Filesize: embedded data validation lists all stored independently?

[ker_01]

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

 

[JLatham]

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]

I appreciate your insight into the way the data validation lists use memory -
I haven't seen or read anything about this before, so I was completely in the
dark on whether it would have an impact.

For this particular worksheet, I had tried both clearing and deleteing extra
rows and columns, to no effect (Excel 2003). I then cleared even the used
space (about 30 rows, and less than 30 columns), and the file size was not
reduced. Removing the worksheet did reduce filesize significantly.

What finally worked for me was copy/pasting the used area into a new sheet,
after which I only had to reformat column widths and row heights- then I
deleted the original worksheet. I now have a fully functional workbook that
takes up about half the space.

I wish there was a way to determine the real root cause (was it related to
creating the workbook in 2007 with lots of formatting, then bringing it to
2003, was it a file corruption that was somehow specific to that worksheet,
etc...) but for now I'll just be glad that the filesize is significantly
smaller.

Thanks!
Keith