in General

Excel Workbook Size Reduction

As you work and work on a spreadsheet it has a tendency to grow in size. This is caused by adding more data, formulas and charts etc, but as file size grows workbooks can become unstable. I’m not sure what the current thinking is, but workbooks over 100mb always used to worry me. I know some folks will have bigger ones that are stable, but it’s just a rough guide.

Anyway it turns out that it’s not only the data, formulas and charts that effect work book size, but other, possibly less obvious things too.

Luckily there are some steps we can take to help reduce workbook size, making them more stable and reducing the file size on disk – think e-mail.

The effect will differ massively depending on the workbook you start with, but for illustration, this shows what happen to a workbook after applying each method in isolation.

What are the options?

  • Zip the file – and remember if you’re trying to get the file under a certain size, maybe to get it through a corporate firewall, other zip applications are about, which can give smaller file sizes than the built in windows one.
  • Used Range – Clearing up the used range means that empty cell don’t get sotred in the file, Excel is not great at clearing up it’s owe file structure, good news is its easy to fix, see here.
  • Removing any VBA code from the file can help reduce the file size – it’s not always possible, but if it is, maybe you could try it! Also watch out of userform with massive graphic files embed in them – classic!
  • Remove Formatting. Having lots of complex formatting is a key driver for file size, removing unnecessary or overly complex formatting can drastically reduce the size of you files.
  • Rob Bovey’s code clearer, strips out VBA models can re imports them, this can help clean them up – if you been working on a bit VBA project, this can help – big time!

So there you go, a few ideas about how to reduce file size, maybe these will help someone.

Click here to comment...

Comment

12 Comments

  1. Store it as .xlsb… its the most compressed file format available if you have 2007 and above. Lets you store macros as well

  2. Good comments, I didn’t know about the filter issues, or about .xlsb.

    But I have a question if an xlsb can hold code and is the smallest file type, why is there other types – is it that slow loading or something.

    Pictures – yep, excellent tip for Power point too, and pivot pivotcache too, yep good.

    Any more?

    • I went to the .xlsb format because of faster loading across a network. The smaller file sizes (1/8 of .xls is typical) were a big help too. I consider it to be the successor to .xls but with compression built in.

      Why the other formats then? It’s binary, not xml, so it’s not cool (I mean compatible with other applications), and has macros built in so it’s not “safe”.

  3. If you are using vlookup to pull data from another workbook, then Excel 2007 (possibly earlier versions as well) keeps a copy of the range data in the workbook – and so if it is a large external range, your workbook can get quite large without it being obvious why

    –Charlie

  4. PivotTables store a copy of your data as well. If you have multiple PT’s driven off the same set of data, they will each store a separate copy of this data. Your workbooks can get large very quickly this way. To limit this, change all but the first PTs data source to that of the first PT using the data. Only one cache copy will be stored.

  5. I recently found a method that can reduce the file size of an Excel file containing significant amounts of VBA, sometimes by as much as 50%.

    1) Note file size
    2) Open file but do not enable VBA/macros
    3) Make no changes
    4) Save & close file
    5) Check file size again

    Even after using Rob Bovey’s Code Cleaner this can sometimes make a huge difference to the size of the file. Many of the workbooks I manage are read-only and reside on a network share somewhere, and as this can make these files much smaller (1300KB down to 700KB etc.) it improves load times, particularly over VPN connections, resulting in an improved user experience.

    The files work exactly the same after this is carried out and although I’ve done some examination to see what might have changed I’ve not managed to find anythign. Most likely some internal decrufting is responsible, but I’ve really no idea why this works.

  6. If you are using pivots in another version of excel, and saving the same as current version, this may eat up lot of space. I was able to reduce around 100 MB on a file of 104 MB, by discarding the old pivot and working out a new one in the current version ! Try it.

  7. I have data in my workbook in cells that I cannot see, making my workbook tooooooo big (262, 880 KB). Takes forever to open, save and to close. Need Help ASAP