When formatting your Excel spreadsheet (changing colours, backgrounds, gridlines, alignment, etc) how you do it can have a massive impact on its file size. What’s more you may even be unaware of the impact (being honest, how many of us actually check file sizes on a regular basis?). Fortunately, there’s a way to help reduce the impact of what we may have done…
An accidental find
The other day, I was working on an Excel spreadsheet for a client & when I was about to email it to him, I noticed that the file size had reduced from 6Mb down to 1Mb. All that I’d changed since the day before was a simple macro & that change wasn’t enough to explain the difference. Slightly worried, in case I’d done something “daft”, I emailed it to myself on another computer, opened it up & checked that it still worked; intrigued I dug deeper.
First of all, I noticed that some other files from this client could also be miraculously “shrunk” by Excel… but others that I’d created in other folders didn’t seem to be impacted in the same way. I checked the folder in case I’d inadvertently turned it into a zipped folder, but no, that wasn’t it.
Excel updates give a clue
The clue came in spotting that the night before I opened the file which then shrank in size, there had been an update to Excel (I use Office 365 & I’m on the Monthly Insider programme so I get some updates earlier than many users & it would appear that the team at Microsoft have been working on an automatic process whereby Excel now looks for unnecessary formatting & removes it from your spreadsheet as you save, thus reducing the file size automatically. Via a couple of great contacts – Anne Walsh of Galway Training (aka The Excel Lady https://theexcellady.com/ ) and Microsoft MVP Mynda Treacy of http://www.myonlinetraininghub.com/ – we were able to confirm this with the team that created Excel.
So… that’s great… but what if you’ve not got the most absolutely up to date version of Excel… or it doesn’t automatically reduce the file size – another Excel Spreadsheet that I had where I keep details of the membership of an Association that I’m membership secretary of had “gained some weight” & the auto save hadn’t changed the file size…
An Excel 2013 & later solution
Introduced into Excel 2013 the Inquire Tab allows you to quickly remove Excess formatting.
Before we look at how to activate this, it’s only available for the following versions Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Office for business and only in the Office Professional Plus and Office 365 Professional Plus editions
To activate the Inquire Tab, from the Developer Tab, select COM Add-Ins & when the dialog box opens, tick the box next to “Inquire” & click OK
This activates the Inquire Tab
There are a number of analysis options, but the one that we’re interested in is Clean Excess Cell Formatting
Selecting this allows us to choose between All Sheets or simply the Active sheet & once you’ve clicked OK, the following dialog box appears…
Clicking YES saves the changes & you could see an appreciable size reduction in your spreadsheet. Obviously, this will depend on whether it’s a large sheet because you have lots of formulas or whether you’ve taken the “easy way” & rather than simply formatting a block of cells, you selected the whole column… but it’s certainly worth a try if your file is getting very large.
An Excel 2010 & earlier solution
As I mentioned, the Inquire Tab is only available in Excel 2013 & later versions (& only certain of those versions, too), so can we achieve the same result a different way? The answer is yes… but it is a little long winded…
- Save your existing file as [Filename]reduced.xlsx (this will be the one that we initially work on & if something goes wrong, you’ll be able to get back to your original file!
- In the [Filename]reduced.xlsx spreadsheet, select all sheets & then all cells on those sheets
- Once you’ve selected all of the cells, from the HOME Tab select the drop-down arrow next to Clear & choose Clear Formats (it’s at the right-hand end of the toolbar)
- Save your spreadsheet & file size should (hopefully!) have appreciably reduced in size
- Then (whilst the “reduced” version is open, also open the original file & copy the formatted area ONLY then, in the “Reduced” version paste in the Formats (drop down arrow at foot of PASTE icon & select Formats
- On saving, your new “Reduced” spreadsheet should look the same as it did before, but massively reduced size
Prevention is better than a cure
Of course, it’s far easier to get it right from the start, rather than having to resolve afterwards, so the Big Tip is only format those cells where you need to have them looking different to the default; if you’re leaving a cell blank, then don’t bother formatting it. I know it might sound daft, but changing a format on a cell from, for example, default to a number format with two decimal places might only take up this much additional memory…
If you’ve done that down a full column in one of the later versions of Excel, you’ve repeated…
…over a million times… no wonder that your file has grown