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
Anita Craig2 days ago
Excellent Service. Exciting opportunities, easy access and engaging sessions.
Catherine O'Connell2 days ago
Technology worked well and the training was well organised.
Trusted Customer2 days ago
Trusted Customer3 days ago
Excellent opportunity to pick up some top tips and hear challenges and solutions from colleagues
Trusted Customer3 days ago
All good :)
Mark Barsby3 days ago
The course content was just right and amended to suit cohort requirements. The instructor was extremely experienced and delivered the subject excellently.
Monica Inkpen4 days ago
People centre approached to training. A style that helps training go beyond the classroom.
Julie Hill4 days ago
The two half-days were really informative and Mike managed to get good participation levels from everyone in the group despite the Zoom medium. There was a lot to take in...
Trusted Customer4 days ago
The trainer spoke very clearly and was patient. She kept the group moving together and explained if unable to follow. I liked the idea of keeping conversations neatly u...
Beth Farminer4 days ago
Every course with Happy is a really great experience with a lot of takeaways, good conversations, the ability to learn from other audience members and real life applicati...
Rupert Bolger4 days ago
A very well run course
Ashan Abeywardena4 days ago
this is my second training at happy. i enjoyed the previous one very much, but this one seemed a bit too vague and less useful