any ideas why this Microsoft Excel file is way larger (in terms of kb) than it should be?

Factual Questions
1

I have an issue at work (Excel 2010), that I want to try and simplify here for the sake of explanation (so the numbers might not be completely accurate, but the spirit of the question remains the same).

In file ‘ABC’, I have two cells that are used. In cell A1, the user enters a zip-code. In cell B1, there is a VLOOKUP formula that references file ‘XYZ’.

File ‘XYZ’ contains every zip-code in the US, and the corresponding city, and it is 3MB in size. It is also on a shared-network-folder that all principal players have access to.

Example: in file ‘ABC’, if I enter ‘24014’, I expect to see ‘Roanoke, VA’. I want to email this file to my supervisor, to verify that it’s correct.

However, the rub is that file ‘ABC’ is now 800kb in size (and it only has two cells in use! :confused:)

We want to leave file ‘XYZ’ (i.e. the lookup table) on a shared-network-folder, so that if it gets updated, then the new version becomes active in real time (i.e. we don’t have all 10 employees needing to update “their version” of the lookup table).

Is there some “effect” of having one Excel file (ABC) linking to another, much larger one (XYZ) that causes ABC to be a much larger file than you’d expect? Is there something else I can look for in file ‘ABC’, to cure the problem of it being roughly 790kb larger than it should be?

I’ve tried verifying that there are no ‘hidden’ range-names in the file. I’ve deleted all “empty” rows and columns below, and to the right, of cells A1 and B1. We’re tired of emailing around ‘ABC’ files that are 800kb in size, when one would think they should be < 10kb.

Any other ideas?

2

What’s happening is that Excel is copying the values of the linked file (that means all the zip codes and location names) into your current one, presumably to speed up behind-the-scenes operations. You can see this happen if you save the lookup spreadsheet as an XML Spreadsheet… all the stupid values are copied over.

You can disable this behavior by following these instructions:

(Basically, go to File -> Options -> Advanced -> “When calculating this workbook” (near the bottom) -> Uncheck “Save external link values”)

I tried this with a zip code database and two-cell spreadsheet and it got the filesize back to 12kB.

3

PS - If you guys are doing this on a regular basis, consider switching to Google Docs. Collaboration there is so much easier than emailing back and forth.

4

great thank you! I will try this tomorrow.