It isn’t obvious how to audit an Excel file.
If you’re working with an Excel file and it is starting to balloon in size, you may want to get under the hood and find out what’s going on.
Large Excel files are a pain.
- 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 skills to try and address the issue but don’t know where to start.
What is taking up the space?
Clearly large images and 1000’s of rows of data do take up space. But knowing that might not be enough to help you.
What else is going on?
You want to sort it out but there are no built in tools or methods within Excel to help.
It is easy to then get stuck in an endless cycle of: Change. Save. Check. Repeat.
What if you had a simple and quick way to look under the hood?
With a solid method to audit the Excel file, you’d know exactly what was going on.
What if you knew, precisely what was taking up the space?
If you had complete certainty on each part of the file then it would be breeze to then work out what you could do about it, knowing that you’re tackling the right area(s).
No matter how big or complex the sheet how would if feel to know for certain exactly what was causing the Excel file to be so big?
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.
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.
First 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!
It also ended up with more visuals, 10x better data quality and was significantly faster and easier to use – the new automated tool reduced the time to create our reports from 4 hours of screen smashing hassle to 4 minutes of automated bliss!
Hopefully, this simple Excel audit method will have the same impact for you.