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
The course is well presenting and ease to flower and to ease to understand .
Jenny Ermoyenous2 days ago
The training was easy to follow and clear. If was adapted to meet the slightly different needs of each attendee. Friendly and good natured trainer - always a bonus - Than...
Jonathan Heath5 days ago
I have been on a few Happy courses, in a training room at work and online too. Very knowledgeable trainers and patient too!
Bernard Williams-Barrow7 days ago
The tutor explain everything to you and make you feel comfortable
Trusted Customer7 days ago
5 starts for John - super helpful and patient.
Trusted Customer9 days ago
Right level, including and relaxed feeling.
Luisa Hasdell9 days ago
The course was well structured and the tutor was very patient and knowledgeable and helped us all. My headset decided to die about 10 minutes from the end but he wasn't p...
Trusted Customer9 days ago
Trainer was excellent. Involved the whole group. Speed and tempo of training was just right. Were able to bring our own experiences and plans for the future.
Amanda Adegoke10 days ago
Really informative and relevant contact and the delivery was excellent.
Sam Giles12 days ago
Instructor was patient and tailored everything to people of all levels.
Trusted Customer12 days ago
Online classes are not a good idea when you have to use one screen to look at three things at the same time. Listen to the tutor, look at what they do on one screen and a...
Trusted Customer12 days ago
Training was great, easy to follow.