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!
- 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.
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
Ruby Alden-Gibson2 days ago
Really enjoyed the course. It was informative and I learnt loads!
Trusted Customer3 days ago
Very good, the process was easy and communication good. Mostly spoke to Laura so it would be good if all her emails had come from her name email address.
Trusted Customer3 days ago
Fully satisfied with the service and location of the training.
Trusted Customer6 days ago
Trent Grassian7 days ago
An incredible training, which was so engaging and informative. Really got a lot out of it and would highly recommend itt o others!
Joanne Spear7 days ago
thanks, the course was simple and easy to follow and i found the presenters approach very friendly.
Wilma Wheatley7 days ago
The tutor was great in including everyone. answered all the questions i had before attending
Trusted Customer7 days ago
Happy's training courses enhanced my knowledge and gave me new skills which I can use in my job to make things more efficient.
Jodie Moore8 days ago
Great course with a great tutor. Very interactive with good learning experiences.
Trusted Customer8 days ago
Great course, enjoyable and useful.
Jackie Costin8 days ago
Roumiana Grigorova8 days ago
I am very happy, taking this training. I would say I am one step more confident. This was an interesting and very useful experience. Our lecture was a great, I have to sa...