Spreadsheets are everywhere in life and motorsports but they can quickly balloon in size. If you knew how to audit an excel file you could quickly get under the hood and find out precisely what’s going on. Sadly, there are no built-in tools or methods within Excel to do this but all is not lost. Here is a simple but effective approach I use all the time. Hopefully, it saves you a ton of time, like it has for me.
Large Excel files are a pain
They can:
- get impossible to share
- crash (more often)
- cause the screen to freeze (more regularly)
- be glacially slow to use
- get corrupted and leave you with nothing…
Worst, if the Excel file is really important, you can easily become extremely anxious about it.
You’ve got the Excel skills to try and address the issue but the file is a mess, you are short on time and you don’t know where to start.
It is easy to then get sucked into an endless cycle of:
- Have inspiration
- Make a change
- Save (or Save as!)
- Check file size
- Fail to make a big enough difference
- Repeat
What if you knew, no matter how big or complex the sheet, precisely what was taking up space?
With a solid method to audit the Excel file, you’d know exactly what was going on and be able to get it sorted quickly.
Clearly large images and 1000s of rows of data do take up space. But knowing that might not be enough to help you.
What else is going on?
How To Audit an Excel file:
Well did you know that an Excel file is actually a collection of several other files?
In fact, the .xls file is only a “wrapper” for keeping all these files together.
What that means is that you can actually UNZIP an Excel file.
I couldn’t quite believe this when I first did it!
To unzip an Excel file, there are many options. On Mac I use Unarchiver and Windows I use 7-zip.
When you’ve got your zip software setup, you simply right-click on the file in a folder window and … unzip it!
Your Excel file becomes a folder…
When you do unzip the Excel file, it will give you a new folder directory of the same name.
To then audit the Excel file, simply explore that directory for what is taking up all the space.
You’ll see all the component parts of your Excel file, something like in the image below:
If you look at that image you can see the Excel file I wanted to audit was called “Contact List.xlsm”
I simply unzipped it and expanded the resulting “Contact List” folder.
This is fascinating in itself and there is lots going on here.
For the purposed of this post though you can now see for certain what is taking up all the space and where.
Tackle The Worksheets
What I tend to then do first is to expand the “worksheets” folder:
In the image you can see the Worksheets that this Excel file holds.
There are 7 worksheet tabs in this Excel file.
Admittedly this is actually quite a small file, but, never the less you can clearly see that “sheet4.xml” is by far the biggest user of space.
Typically I would then dive back into the actual Excel file, explore sheet4 and then come up with targeted ways to approach reducing the size.
Once you’ve made a change you can unzip and audit the new version of your excel file to see what difference it has made.
In one project, I needed to audit an Excel file that was 120MB!
I used this method and discovered that there were thousands of repeated formulas that were actually taking up most of the space – and on a hidden tab, I hadn’t seen before!
In the end, the solution I chose was to re-engineer those formulas into some VBA code.
The result was an Excel file size that came down to 4MB!
That’s it. Really it is that simple and (if you didn’t know before) you’ve now a new Excel super-power added to your skillset.
