Excel file size out of control? Learn how to reduce Excel spreadsheet file size

Posted on:  07/13/2021

Sometimes working in Excel, moving data around, creating worksheets and removing, copy pasting, all that work can lead to huge file sizes, without realizing. I've had a situation like this recently, with an Excel spreadsheet that didn't have a lot of data, but the file size on disk was more than 15 times larger than it should have been.

Reducing the file size of your Excel worksheets is easy, all you have to do is identify what is causing the problem. Sometimes you work on an Excel file with one or more Worksheets and when you save, you notice the saving is slower, takes longer to move around or open the right-click menus, etc. All these are symptoms that the file has a size problem - in-memory or on disk. 

Let's have a look at how you can check the size of your Excel workbook, how to inspect the worksheets to figure out which worksheet is too large or is causing the size issue, then look at a few cool keyboard shortcuts to help you move around and diagnose the problem and make your excel spreadsheet smaller. Finally, with a few keystrokes, I show you how you can clear the problem, reduce your excel file size and delete unnecessary or blank rows and columns in your Excel workbook so that it is optimized.

How to check the file size of your Excel spreadsheet?

With your spreadsheet open, go to File > Info, and on the right-side you should see the size on disk of your workbook:

To further investigate the problem and see some data about your entire workbook, you can go to the Workbook statistics in the Review tab in Excel, and look at the workbook statistics. This should show you what's going on in your current worksheet as well as how many cells with data are in your workbook, how many worksheets you have and other useful info:

Start cleaning up your Excel file

The first thing I would do is check which worksheets I don't need and delete them. Whether they are blank or you think they are blank, if you don't need them, remove them. 

Then save your document again and once the Excel file has saved, check the size again in File > Info, as described above. Maybe the file size went down. If not, keep reading...

Useful keyboard shortcuts to find problem areas in Excel

If removing unnecessary worksheets doesn't help, here are a few keyboard shortcuts to help you move around your worksheet faster and see what the problem is:

CTRL + HOME	This shortcuts takes you to the first cell in your worksheet. Or the top-left cell in case you have some frozen panes at the top, like headers. This shows where your data range STARTS in your current Excel worksheet.
CTRL + END	This should take you to the bottom of the worksheet. This helps to see if your worksheet ends where the data ends or if you have invisible data or cells that Excel thinks have data in them.

In my example problem file, the CTRL + END shortcut took me all the way to more than 1 million rows (I don't have that much data!):

This is what makes the file so large: Excel is picking up a lot of empty cells and thinks there's data in them, which then it tries to save into the XLSX file on disk, and because it needs to keep references to all those hundreds or thousands of cells, the file size gets overblown.

So we've identified the problem, but how can we optimize the Excel file?

I have two more shortcut combinations for you, and this time, these will show you where the data in your Excel spreadsheet ACTUALLY ends:

CTRL + DOWN ARROW	Go to the last row that has data in it.
CTRL + RIGHT ARROW	Go to the last column with data in it.

So these keyboard shortcuts help identify the extent of your data and not what Excel thinks is data.

To remove all empty cells, follow these steps:

1. Go to the column next to the last one with data in it, in my example it's column M, and select that column.
2. Press CTRL + SHIFT + RIGHT ARROW key to select all the columns after this one, up to the end of the empty columns
3. Then press right-click and select Delete.

This should have removed all the 'data' in those empty columns.

Now repeat the same procedure for the rows with no data:

1. Use the shortcut CTRL + DOWN ARROW to find the last row with data in it
2. Select the row immediately AFTER that row
3. Press CTRL + SHIFT + DOWN ARROW to select all the empty cells up to the end of the sheet
4. Right-click and select Delete on these cells to remove them (this might take a while, for me on a powerful computer it took a while because I had more than 1 million rows!)

This should take care of it.

Check if the file has shrunk

After the procedures above, save your file again. This time the file size should have gone way down, if all the empty cells have been removed.

If the file size doesn't reduce as much as you think it should, make sure you don't have other worksheets in the same situation and repeat the steps above on those ones too.

If this was useful, let me know and check out my YouTube channel for more Excel tutorials!

Categories

• Blog » Microsoft » Microsoft Excel » Basic