The Mystery of the Humongous Excel File

There are many reasons that a simple Excel file can “balloon” to such proportions, but here is a simple and very frequent cause.

Firstly, I had a quick scan for the obvious – hidden sheets, too much formatting, abandoned Pivot Tables. Nothing stood out, so I moved on to the number one cause (in my list anyway) of bloated workbooks – those “empty” cells.

What are “empty” cells?

Workbooks have a hard life. They’re often brought into being by people who do not understand them well, who fail to nurture them, who leave them in the care of thoughtless colleagues. Under these conditions workbooks can both fail to thrive and, in the worst cases, become a bit, well, feral.

One of the most innocent ways we can upset our workbooks is by not being clear where our data is. We might think its obvious – if a cell has something in, it’s data. If it’s empty, it’s not. But are we being clear about that?

Excel has very different ideas of data to us. For instance, if you were to open a blank workbook, select column A:A and click the Bold tool, do you have any data? The cells are all still empty, and to the eye nothing in your workbook has changed.

Yet something very significant has changed: the last used cell.

The culprit?

Excel knows it has a propensity for “panic buying” when it comes to memory, both working memory (RAM) and storage (file size) so it has some “self-control” strategies built in. Rather than grab enough resources to watch every cell on every sheet it only worries about the cells from A1 to the last cell that you’ve used and doesn’t worry about the rest of the sheet.

Do you remember formatting column A:A? Our last used cell is now A1048576. Not only is Excel “keeping an eye” on over a million cells, it has also saved a small amount of data about the text (even though there is none) about the cell being bold.

So, what would happen to our last used cell if I were to enter some data in cell J5? Our last used cell is now J1048576. That is now a whopping 104,857,600 cells that Excel is “keeping any eye on”. Remember, all I’ve done is formatted column A:A and entered a single piece of data into cell J5. If I’d also formatted row 1:1 my last used cell would now be XFD1048576 – that’s 17,179,869,184 cells.

Putting things right

Right, let's fix this! If I delete the data in J5 I should be back to cell A1048576 being my last used cell, right? Wrong! Even if you delete the contents of the cell and remove its formatting the last used cell is never forgotten. So what should we do?

1. First of all, find your last used cell using the shortcut CTRL + END. If this cell is significantly beyond your data you’ll need to remove the offending rows and columns.
2. To do this, find the first row beneath your data, select the whole row and use the CTRL + down arrow key to select all of the rows from here to the bottom of the worksheet.
3. Then use CTRL + Minus key to remove the rows. The rows will immediately be replaced with nice new empty ones.
4. Do the same for the columns and you should notice a sizeable reduction in your file size.

Deleting or clearing the contents and formatting will not work.

After doing this on just one sheet in our workbook the size fell from 30MB back to the more usual 108KB.

I have to be honest with you good people and state that it’s not always this easy, but this is a good place to start!

Related Blogs

• Save Time in Excel with Autofill - Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
• How Microsoft Excel Can Increase Your Productivity - Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.
• How to Create a Pivot Table in Excel - Pivot tables are the perfect way to sort and analyse data in Excel. This two minute video takes you through the process of how to do so. 
• Calculations on a Filtered List in Excel - Darren talks through the process of how to create a dropdown filter in your Excel spreadsheets.