8 easy ways to reduce Excel file size

In this guide, we will cover off a series of techniques that can be used to easy compress an excel file that has gotten out of hand.

Excel is a really powerful tool for data analysis, but it is also an absolute pain to work with when the file size starts creeping up. We have all been there – multiple sheets of formulas on formulas that make our computers fans sounds like an aircraft before Excel decides to shut itself down.

If you are anything like me and terrible at saving, seeing the file stop responding is anxiety inducing!

Luckily, there are quite a few ways we can go about ensuring the file sizes stay as small as possible, and avoid going out and buying more RAM for our systems!

These range from eliminating some (hopefully) unnecessary elements of the files, and strategies such as splitting the files up across multiple workbooks which can help in it’s own way.

We will cover the following techniques:

• Check for and remove any hidden sheets that aren’t needed
• Reduce the number of formulas
• Compress image sizes
• Remove pivot cache
• Remove cell and conditional formatting
• Store raw data in separate files where possible
• Save as XLSX
• Save as ZIP

1. Check for and remove unnecessary hidden sheets

First up, let’s look at removing unnecessary data to help reduce Excel file size by simply deleting tabs.

There are two parts to this step, the first of which may seem obvious, but if you are working with lots of data it can be pretty easy to keep filling in different sheets with datasets you don’t actually need in the end.

Step 1 – Go through all of your sheets and completely delete any that are no longer needed. If there are a couple of tables that are useful see if you can move them onto another sheet so you can delete it. This can have a significant impact on the overall file size.

Step 2 – Check for hidden sheets. There may be some hidden data in there that you have forgotten about, or someone else using the document was adding in previously that is now no longer needed. To unhide sheets right click on your current one, and select Unhide.

This will bring up a list of all hidden sheets:

From here simply select the ones you want to unhide and see what they contain! If they of no value then delete them.

2. Reduce the number of formulas

Working with larger datasets that involve formulas can be challenging for two reasons – firstly they significantly impact the calculation time when adding in more data. Secondly (and more importantly given the context of this article), they have an impact on the file size.

Some examples of these are more complex IF formulas, or data merging ones such as VLOOKUPs that are referencing a second set of data elsewhere.

The easiest way of dealing with this without losing any data is to simply copy and paste over those formulas as values. To do so, highlight all of the cells in a row of formulas you no longer need as a formula, right click and select copy, and then right click back in the very top cell you have selected and under paste special select the paste as values option:

It could always be worth keeping the very bottom row of data as formulas in case you ever add more data and need to use those same formulas again as this would let you drag them down, but by removing the bulk of the formulas this should have a significant impact on file size and performance!

3. Compress image sizes

Images can take up a ton of space within a document. If you are working with high quality images in certain formats these can add significant volume to the size of the file itself, even if within your spreadsheet they might not look like they would.

Depending on the picture itself, you may be able to significantly reduce the size and quality of an image without it having too much (if any) of a visual impact. You can use tools such as photoshop which are great as when saving they have a quality slider you can test out to see just how small an image can get before it’s too poor to use.

Another option is looking at various free online resources where you just upload the image. For example TinyPNG.

4. Remove pivot cache

Pivot tables are notorious for taking up a lot of space due to the pivot cache that is created with the table itself.

There are two approaches we can take here to reduce file size depending on the purpose of the pivot.

The first option is similar to what we did with the formulas. If you don’t really need the pivot to have a live connection to a set of raw data, for example in cases where you know it won’t be updated and need to be refreshed, then you can simply copy the pivot table as values!

The second option is to stop Excel saving the source data with the file itself as this takes up a significant amount of the file size after closing it. To do so right click on the pivot table itself and select PivotTable Options.

Now, under the Data section, untick Save source data with file, and then make sure to tick Refresh data when opening the file.

If you leave that second one unticked you will need to manually refresh the pivot table after opening the file given the source data was not saved. It’s just easier to let Excel do that automatically when you open it again later.

5. Remove cell and conditional formatting

Formatting in Excel is super handy, but it does add to the overall file size so it is worth looking through all of the formatting that has been applied and seeing if it is really necessary.

There are two types of formatting to look at removing:

Data / cell formatting – this is simply the formatting that has been manually applied to various sections of the sheets. For example adding borders to data, adding in coloured backgrounds for headers. All of those little changes to make the document for visual can add up.

Conditional formatting – this adds a fair bit to the overall file size not only due to the colours and logos it can add, but the logic that goes on in the background to check the contents of a cell and measure it against the condition that has been set. In a way it is almost like having a lot of smaller formulas!

Removing conditional formatting is easy. You can either remove it from selected cells, or the entire worksheet if you decide you don’t need any.

On the home tab at the top of the screen, select the conditional formatting drop down box, and under clear rules, you will be able to choose which option you would prefer.

6. Store data across multiple files

This option isn’t technically reducing overall size of the total worksheets, but can come in very handy.

Let’s say you have 5 worksheets that are more or less laid out like the below:

• Sheet 1 – Charts and other visualizations
• Sheet 2 – Series of pivot tables
• Sheet 3 – Raw Data set 1
• Sheet 4 – Raw Data set 2, including multiple columns worth of formulas
• Sheet 5 – Additional formulas, notes and so on

In this case, we can split our work into two documents:

• Document 1 – Sheets 1 and 2
• Document 2 – Sheets, 3, 4 and 5

This will help for two reasons. Firstly, each file will now be smaller. Making it easier to open if you just need to work with one half, or send files to other users.

The other reason this helps is, realistically, the other users these files are being made for, especially insightful documents filled with charts and tables, are not going to care at all about all of the raw data and formulas that were used.

Charts and tables are not limited to just referencing data on another sheet – they can reference an entirely separate file. Eliminating the need for huge tabs of hidden sheets of raw data can make life so much easier when it comes time to distributing a final document.

7. Save as XLSX format

There is a very good chance that you are already doing this as in later versions of Excel the default ‘Excel Workbook’ format that appears when choosing to save a file is selected automatically, but this is still worth mentioning as you could be working with an older format, or something entirely different like a CSV.

CSV file formats tend to be a fair bit larger than XLSX, even though they don’t actually allow for any formatting at all. Chances are you are already working with an XLSX file – especially if you have even a single formula or piece of formatting, but if you are taking the above approach of storing raw data sheets in their own documents then saving them as XLSX rather than CSV even if they are pure raw data can make a difference.

8. Save as ZIP

Finally, once the file itself is as small as we can make it, the last technique we have to reduce the overall size is to compress into a ZIP file.

This obviously isn’t very helpful if the file size issues are affecting the file performance itself, but if we need to save a file somewhere, or send via email for example then reducing it even more in a compressed ZIP file will make a significant difference.

To ZIP a file, simply right click on it, and under Send to, select Compressed (zipped) folder.

This will automatically create a zipped copy of the file in the same folder that is ready to be transferred easily. You can unzip the file in just as few steps by right clicking on it, and choosing Extract All.

This sums up our guide of 8 easy ways to reduce Excel file size. Hopefully these tips all help to make processing and working with data in Excel easier, and result in less files that choose to stop responding.

For more handy guides be sure to check out our Excel Tips page