I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution.
Excel files are usually pretty stable but sometimes they aren’t. Sometimes the file size increases for no apparent reason.
One cause of file size increase is something called the used range. The used range starts in A1 and goes the intersection of the last used column and the last used row.
The term “used” can mean
- a cell that has an entry
- a cell that contains a cell comment
- a cell that has been formatted
Used can also mean a cell once contained an entry; a comment or a format.
To find the end of the “used range” press Ctrl + End. The cell most likely will be empty. The intersection of the last used row and last used column may not be used itself. It depends on the structure of the entries above and to the left.
Now that you have found the last used cell, use Ctrl + up arrow to see where the last column is compared to the structure above. If the last used column is way off to the right of your sheet structure then you may need to reset the used range. As a guide 20 extra columns aren’t much in the scheme of things.
Press Ctrl + End again and this time use Ctrl + left arrow. Compare this last used row with the structure above and see where it fits in. If it is many rows below the current structure you again may need to reset the used range. As a guide a thousand rows isn’t much in the scheme of things.
Resetting the Used Range
Warning – I am assuming you know the structure of the file you are working with. If it is an inherited file you should spend time reviewing it to learn the structure. This may include unhiding all rows/columns.
It is still a good idea to make a separate copy of the file before you do this. F12 is the shortcut for Save As.
Find the bottom of the current structure you are using. Go a thousand rows below that to be on the safe side.
Press Shift + Space Bar to select the whole row. Then press Ctrl + Shift + down arrow to select all the rows below. Hold the Ctrl key down and press the minus sign on the numeric keypad. Otherwise use the key on the left of the = sign.
A warning will probably appear saying you can’t use undo (that’s why I suggest making a copy). Continue without the undo.
Now go to the far right of the sheet structure, go twenty more columns to the right and press Ctrl + Space Bar to select the whole column. Press Ctrl + Shift + Right arrow to select all the columns to the right. Hold the Ctrl key down and press the minus sign on the numeric keypad. Otherwise use the key on the left of the = sign.
Nearly done, you now need to save the file. Feel free to use Save As (F12 function key) and use a version number. Saving the file is what actually resets the used range. Now try Ctrl + End again and see where the new used range is.
By the way, a thousand extra blank rows and twenty extra blank columns won’t make much difference to the file size.
You may need to repeat this process on multiple sheets.
I hope I have convinced you that in Excel, Ctrl + End can be your friend.
Other causes of file size increase include
- multiple images in the file
- external links
- external data sources