How to Audit an Excel file

Spreadsheets are everywhere in life and motorsports – and they can quickly balloon in size. But when their size becomes an issue, how do you root out what is taking up all the file space?

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 to audit an excel file but all is not lost.

Here is a simple but effective approach I use all the time. Hopefully, it gives you another tool in your armoury and saves you a ton of time – like it has for me.

If you enjoy this article, be sure to subscribe to my weekly email, “Ahead of the Curve,” to make sure you never miss the latest.

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.

When the file you’re using is a mess, you are short on time and it is easy to then get sucked into an endless cycle of:

  • Have inspiration to shrink file size,
  • 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?

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?

If you had a solid method to audit the Excel file, you’d know exactly what was going on and be able to get it sorted quickly.

How To Audit an Excel file – Unzip It !

Well did you know that an Excel file is 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 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:

What you see when you audit an Excel file with unzip.

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 (well if you’re like me!) and there is lots going on here.

For the purposes of this article though, can you now see for certain how you will see what is taking up all the space and where?

Where should you start?

Start With The Worksheets

What I tend to then do first is to expand the “worksheets” folder:

Screenshot 2019 01 31 at 12.23.41

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.

Sheet4 is Big. Now What?

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.

What you might not realise is that repeated formulas take up a huge amount of space.

If it must stay a formula, I look at ways of moving the repeat elements of the formula into a central cell that you can reference.

I have also found that splitting down complex calculations into their component parts in different cells can significantly reduce storage requirements.

Another approach is to use code, such as VBA.

Some of the most experienced Excel users I’ve met are terrified of VBA.

Whilst VBA is a dinosaur language, what it can enable you to do in Excel is transformational.

If VBA is new for you and something you’d like to know about check out this short course –> Excel VBA for Motorsports [link]

Excel Audit In Sum: 120MB to 4MB

Once you’ve made a change in the Excel file go through the process again.

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 you’ve now a new Excel super-power added to your skillset.

If you try it, be sure to sign up to “Ahead of the Curve,” and let me know how you got on.

Whenever you’re ready, there are 4 ways I can help you:

  1. ​Master Your Tyres course [link]​ – Tyres are complicated and fussy. They are also the most important tuning device on your car. Learn professional driving and setup techniques so you can confidently get more grip on track.
  2. ​Data for Drivers course [link]​ – Using a data logger but not sure how to interpret the squiggly lines? Worried you’re missing something? Learn (or refresh!) the key fundamentals, as well as how to more effectively apply what you’ve learned.
  3. ​Driver Coaching [link] ​– No time, experience, or interest in staring at the squiggly lines? Don’t let your data go to waste. Get me to interpret yours (or your teams) data for you.
  4. Promote your business to the best motorsports readers in the world by ​advertising on Ahead of the Curve [link]

 

Whenever you're ready, there are 4 ways I can help you:

  1. Get 1-1 Help From Samir – Sometimes you just need an expert race engineer to get you back on track. Get your questions answered, the best approaches made clear and your goals achieved, with my support.
  2. Master Your Tyres course [link] - Go beyond tyre pressures and guesswork. Uncover the science, tools and techniques to optimise your tyre setup, and your driving style, so you can confidently get more grip, better handling and faster lap times, on any track, in any conditions.
  3. Motorsports Data Analysis course [link] - Ever wondered what insights you might be missing? Turn the data you're already collecting into accurate, personalised guidance to help you drive faster.
  4. Get your business in front of the best motorsports eyeballs in the world by sponsoring Ahead of the Curve [link]