The Ultimate Guide to Reducing File Size in Excel
We’ve all encountered them before: unnecessarily large files in Microsoft Excel. They’re difficult to email to colleagues or customers, take too long to open, and tend to take a long time to update or change formulas every time you change a value.
All of these issues mean you’re sitting in front of some kind of loading bar, wasting your time when you could be getting back to completing your work.
So how do these files get so big and unworkable? If you’re sharing these files with colleagues, classmates or friends, you’ll probably never find out.
Fortunately, there are a wide range of ways to fix this within Excel itself.
In this post, I’ve gathered all the best tips to help you manage these large and bulky files. I’ve also added some of my own tips from my personal experience. To make it easier for you to work through, I’ve grouped them into three main areas:
- directly reducing file size,
- saving calculation time, and
- optimising formulas.
- Directly reduce file size
- Save calculation time
- Optimise your Excel formulas
- Bonus option: changing the file extension
- And now you’re all set!
Directly reduce file size
In this section are the easy and straightforward options to cut down on file size. If you don’t want to alter the contents of the workbook, try these options first.
1. Remove unnecessary blank space in your sheets
This is definitely the most common source of large Excel file sizes, and fortunately one of the easiest to fix.
Excel has a “used range” for every sheet in your workbook, and the larger this is, the bigger the file size becomes. For new files, the used range is only cell A1, but as you work through a file, it will increase to represent the rightmost column and furthest row that you’ve edited or formatted.
Especially in older files, even if cells are blank and have no formatting, Excel may be treating them within the used range, leading to a larger file size for no reason.
By pressing Ctrl + End on any sheet in your Excel file, you can see what the “last used cell” is. If that shortcut takes you many rows (or columns) past the end of your data, it means that all of those cells are increasing the file size for no reason whatsoever.
Here’s an example. I’ve got data that goes until column F and row 11, so I should expect that the used range goes until cell F11 (even if nothing is in that specific cell). However, when I press Ctrl + End:
(Note: to get this result, I simply entered a value in cell K20 then deleted it.)
How to Fix the “Used Range” in Excel
To shrink the used range to the cells you’re using, simply delete the extra blank rows or columns:
- Select the first blank row (or column). You can easily do this via shortcuts by pressing Shift + Space for the current row, or Ctrl + Space for the current column.
- Press Ctrl + Shift + Arrow Down (or Arrow Right) to go to the very bottom or very right of the Excel sheet.
- Right-click and select Delete. Do NOT simply press the Delete button – it won’t have the same effect, all it will do is clear the contents of the cells!
In older versions of Excel, you’ll need to save the file after deleting the unnecessary rows and columns. If you press Ctrl + End again, the last used cell should be right before the cells you just deleted.
If you have received an Excel file from someone else, they may have hidden some additional sheets as part of the file, but hidden them for some reason.
You can reduce the file size of your Excel workbook by unhiding all sheets, and checking if you still require that data or not.
3. Save your files in binary format (.xlsb)
I’ve hidden the best way to reduce file size down at position #3.
If you have an Excel file with lots of raw data and lots of formulas, go and save it in the Excel binary format (.xlsb).
Any Macros and VBA will still be retained in this format, so you don’t have to worry about loss of functionality.
Most files will get a reduction in file size of roughly 50% by saving it in the .xlsb format, but this will vary depending on what kind of data is in the file.
4. Remove formatting on raw data
If your raw data contains formatting, then it’s going to increase the file size in Excel.
Of course, your job or assignment may require the data to be presented in a specific way – but if it will never be viewed directly and only needs to be used for calculation purposes, then remove as much formatting as possible.
I can think of a few ex-bosses of mine who would hate if I removed any formatting from files that we gave to our clients, but if it’s an internal working file, then avoid formatting it just to make it look pretty for you. Be careful of using the Clear Formats options to do this – you’ll also strip out any date or currency formats from data which can be confusing (eg: Jan 1st 2016 will change to ‘42370’). Formatting such as borders and highlighting are good examples to remove.
5. Double check Conditional Formatting ranges
Conditional Formatting is a fantastic way to visually compare any changes in numbers in data ranges, but it can come at a hefty price – a bloated Excel file!
One mistake some Excel users make is to apply formatting to an entire sheet, or an entire group of rows or columns.
Go to Conditional Formatting > Manage Rules and check the cell range of each of the rules. If the applied range is larger than what you need, then consider limiting it to only the required data range.
Want more formatting shortcuts? Check out this Excel Shortcut Roundup to become lightning-fast at formatting in Excel.
Save calculation time
If you’re dealing with a large file in Excel, then you’ll eventually end up with a file that with lots of formulas that recalculate as you make changes.
For most workbooks, the file size and number of calculations will be so small that you’ll never notice that Excel recalculates any time you update a value that it depends on. But, when you have hundreds of thousands of formulas the time will start to add up, which means you can often be sitting around twiddling your thumbs for minutes at a time.
1. Disable automatic formula calculation
If formula calculation is slowing down your work, then go ahead and disable it.
A large number of Excel users don’t even know this option is available – you can find it in the Formulas tab in the Excel ribbon, towards the end:
While formula calculation is set to Manual, you can force a recalculation of the entire workbook by pressing the F9 key. If you only want to recalculate one sheet, then Shift + F9 will calculate the currently selected sheet.
Now, you might notice some strange behaviour when Manual is selected. If you edit a cell containing a formula and press Enter, that formula will calculate as usual. If you then copy that formula to other cells, the previous result will be displayed (temporarily!) until you hit the F9 key. Don’t worry about it, just keep working as normal and once you press the F9 key (or change the Calculation option back to Automatic) the sheet will update completely.
And if you want a single formula to be updated, you can edit the cell (F2), hit Enter, and that formula will calculate.
One final note on this point: always make sure to change calculation back to Automatic when you’re finished working on a file, or when you’re about to send the file to someone else.
2. Use a Watch Window to always check on specific cells
Also in the Formulas menu, you can click on the Watch Window option to open up a small dialog box which appears in front of your Excel workbook.
The Watch Window contains a list of cells that you specifically want to keep an eye on. For example, you might be working on a detailed budget which has workings across multiple sheets. All of those sheets feed values into a high-level budget summary sheet, and you want to know the final profit or loss while working on other sheets.
In the Watch Window, click on the Add Watch button and select the cell you want to keep a close eye on. The value will always be visible, regardless of which sheet is active.
If you want to populate the Name field, follow these steps:
- Select the Name Manager in the Formula menu.
- Select New, and type whatever reference you like in the Name field.
- Make sure the “Refers to:” box is pointed to the cell you’re using in the Watch Window.
- Click OK and close the Name Manager.
Optimise your Excel formulas
Apart from shrinking file size directly, you can use the below tips to optimise your Excel formulas so they run faster.
1. Avoid using volatile formulas
Seven functions in Excel are known as volatile – ie, they calculate every time any cell is updated in the workbook.
These seven functions are RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT() and INFO(). Microsoft lists them here on their help page.
Minimising the use of these functions in your workbook will cut down on unnecessary calculation time.
2. Use PivotTables or Excel Tables
It may seem slightly counter-intuitive, but using PivotTables instead of a series of formulas is a very effective way to show your results. PivotTables are designed by Microsoft to operate efficiently in Excel, so use them if it makes sense!
Similarly, putting your data in a Table is also very efficient. Any formulas (calculated fields) within the Table contribute less to file size compared to a series of formulas on a normal data range.
Want to learn more about Excel Tables? Read the 9 (+1) Benefits of Excel Tables here.
3. Avoid referencing entire rows or columns
I am especially guilty of this – using a SUMIF() or VLOOKUP() function that asks Excel to look up an entire column, even if my data is only a few hundred rows. So instead of having:
=SUMIF(A:A, $C4, B:B)
You should have:
=SUMIF(A1:A100, $C4, B1:B100)
This way, Excel will only search the data range you specify – not all 1,048,576 rows.
4. Avoid repeated calculations
For this tip, you want to reduce the number of cell references that your formulas have. It’s not something that most Excel users will think about, but it’s worthwhile if you’re planning on some serious number crunching.
Let’s use a really basic example:
Say you have a formula which contains “=$D$5+$E$5”, and you copy that formula to one hundred cells. In this case, the total number of cell references is two hundred, despite the number of unique cells only being two.
But if F5 has a formula which is =D5+E5, and you update those one hundred cells to refer to cell F5, then you have halved the number of cell references. Excel only has to calculate D5+E5 one time, instead of one hundred times.
So if you have a large number of formulas that perform the same calculation, consider breaking them up in your sheet to speed up the calculation.
5. Sort your data when using formulas that look up values
When you browse through an alphabetised filing cabinet, it’s fairly quick to find what you want (provided the items in the cabinet are in order)!
The same idea applies to Microsoft Excel.
If you’re using VLOOKUP(), INDEX(), MATCH(), SUMIF(), or any other Excel function which looks for a specific value, your sheet will calculate much quicker if the data you’re searching in is already sorted.
Learn more about Excel formulas here.
Bonus option: changing the file extension
Apart from updating the file contents directly in Excel, you can try this other method to reduce file size.
Because of the way .xls/.xlsx files are stored, you can treat them as .zip files and rename them without any issues.
Now I’m not a huge fan of this process, but it does appear to work!
Here’s the process:
- Navigate to your file in Windows Explorer, and change the filename from .xls/.xlsx to .zip.
- Unzip the file, just like you would with any other .zip file. You should get a few folders and an XML file:
- Zip up the files that were just extracted into a new .zip file.
- Change the filename back to the original .xls/.xlsx file extension.
And now you’re all set!
So there you have it. You now have plenty of options to ensure you’re working on smaller, more manageable Excel files.
And even better, your files should spend less time to open, and formulas should take less time to calculate!
Have any of the above tips helped you out, or do you still have issues cutting down on file size? Let us know in the comments!