I don't actually know the definitive answer, but I can infer some things. In
the bottom line analysis, Excel actually only looks at one cell at a time and
pulls up the necessary information about it 'as required'. That's a
simplistic view, but it allows us to infer that the entire lists are not
stored in each cell, just a reference to their source.
One thing Excel does is to 'remember' the largest row number/farthest right
column ever used, even when you delete entries from them. Often to reduce a
file's size, you have to actually delete unused rows/columns, save the file
and reopen it to see a reduction in file size.
Check out this FAQ over at Contextures for more information about this
situation - and cures for it: http://www.contextures.com/xlfaqApp.html#Unused



"ker_01" wrote:

I have 4 named ranges that are used as sources for data validation lists. On
my worksheet, I have:
120 cells associated with a named range with 2 items,
60 cells associated with a named range that will have (on average) about 50
items
10 cells associated with a named range with about 20 items, and
60 cells associated with a named range containing about 4000 items

Does each data validation cell store a copy of the full list, as well as the
currently selected index value, or does the sheet (or entire workbook) share
one 'copy' of the named range, with each data validation cell only using as
much memory as it takes to store the index value for that cell?

I tried deleting sections of the data validation cells, but it didn't affect
the filesize, so either the file is storing undo information, or only the
index is stored and my lists are taking a lot more memory than I ever would
have expected.

I appreciate any information available on the use of memory by data
validation list cells based on named ranges.

Thanks!
Keith