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.

Comments

  1. ethoros says:

    You may want to add duplicate pivotcaches to that list.

  2. Gordon says:

    Ranges that Excel defines when you use filters are stored in the workbook and can also cause problems in large sheets in my experience.

    Jan Karel Pieterse’s superb Name Manager (http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp) can help identify and remove these ranges, sometimes reducing a file by megabytes.

  3. Jon von der Heyden says:

    A couple of other offenders:

    Pictures, although rarely used, can have adverse effect on size. I tend to compress my pics either 96 or 200 dpi.

    Pivot cache, see here: http://www.contextures.com/xlPivot04.html

  4. sam says:

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

  5. Ross McLean says:

    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?

    • Omar says:

      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”.

  6. Charlie Hall says:

    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

  7. Mark Rothfuss says:

    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.

  8. Gordon says:

    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.

  9. [...] check back in the original post for explanations of these if they don’t make [...]

  10. MacPher says:

    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.

  11. Susie says:

    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

Submit a Comment