The Mystery of the Humongous Excel File
My colleague Rachael sent me an interesting email today. She had tried to send one of our usually svelte Excel exercise files to a trainer, only to find that the file had grown from a perfectly reasonable 108KB to a frankly shocking 30MB!
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
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.
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?
- 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.
- 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.
- Then use CTRL + Minus key to remove the rows. The rows will immediately be replaced with nice new empty ones.
- 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!
Why not sign up to our newsletter?
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
Darren is one of Happy's Senior Trainers, able to train almost every IT course on our course programme. He worked for Happy for 12 years and has been an Associate Trainer since 2018.
Testimonials from happy Customers
Trusted Customer8 days ago
The course well thought out, relevant , easy to follow and the tutor was very experienced.
Emma Clegg9 days ago
Very helpful and detailed
Trusted Customer9 days ago
Very useful course. Great tuition from instructor. Good pace of learning.
Elena Gatti9 days ago
Kevin was very attentive to how I learnt
Trusted Customer13 days ago
Service was very good, food was nice
Catherine Thomas15 days ago
Environment was relaxed and friendly. Course content was self explanatory and not too compacted in the time available.
Wendy Wilderspin16 days ago
Good service from applying to completing training event
Trusted Customer16 days ago
The trainer was very knowledgeable and made the course enjoyable. I also liked the relaxed atmosphere at Happy
Elena Gatti16 days ago
It was very useful.
Trusted Customer17 days ago
A lot of information to support previous knowledge.
Adebayo Ademefun23 days ago
i enjoyed the training and the trainer is a joy to work with. She is an excellent trainer
Jane Whittaker25 days ago
I've had 3 courses with Happy - Word, Powerpoint and Excel, they've all been excellent. The tutors are knowledgeable, friendly and helpful. I recommend Happy all the time...