Large Excel File? A Step-By-Step Guide to Shrink Excel’s File Size

I see this situation often as am Excel consultant: a client takes over responsibility for a workbook from someone who’s left the company and is thrust into their new role with little support. Months and sometimes years go by working with the spreadsheet and there’s never enough time to step back to optimize it. Adding, deleting and editing data always seems to take priority. 

As data is added over time, it’s natural for the size to grow. But like the gunk that builds up in the drain, unnecessary data accumulates with day-to-daycopy and pastes, insert copied cell commands and formatting changes applied to entire rows and columns. This slows spreadsheets and often results in unmanageably large file sizes which aren’t a problem until your email says you’ve exceeded attachment size limit. 

Big is relative, but when I start seeing sheets in the double-digits, then it’s usually a sign that the workbook is not set up to work at it’s best (and fastest). Unoptimized file sizes of 10-25MB are common, with some  unoptimized workbooks growing to 300MB! A common question I get is whether they need to get more RAM or install the 64-bit version of Office. In 95% of cases, this is not the solution, and if it is, it’s only a temporary one because the root of the problem is an Excel file that’s been allowed to run wild for too long.

So what can you do to fix a large and slow Excel file? I’ll walk you through the steps I take to diagnose and fix unwieldy workbooks so you know exactly what to do. .

I’ll be working with an actual client workbook. Any identifying and confidential data has been removed or replaced.

Removing Unused Worksheets and Data

First, take a quick look at how many sheets and what’s in them. 

For workbooks with multiple sheets where it’s not obvious which sheet is the size hog, make a copy of the workbook and delete the sheet you suspect is the culprit. Then save the fileand write down the new file size.

Repeat this process by deleting different sheets and comparing the file size of the saved workbook. You can call this a “brute force” method of isolating a problem sheet. 

It’s usually is one or two sheets causing the problem, so once you’ve found it, here are your next steps to diagnose the problem:

Identify the last used cell and compare it to the last cell of data. 

Excel 365 has a new feature called Workbook Statistics. You can pull it up from the Review tab > Proofing group > Workbook Statistics, or Press CTRL-SHIFT-G in Windows:

Over time, multiple inserts, copy and pastes, and formatting changes leave innocent-looking “blank” cells behind.  However, those cells actually have formatting data saved which adds to the file size.

By checking the used range and deleting seemingly unused cells that look blank, you can reduce the number of used cells by X thousands.

Another tip is tolook for hidden sheets. For example, I’ll ask my client if there is any unnecessary data and unused sheets that can simply be deleted. Often these sheets are new to them. Of course check that they’re not linked to from other sheets or used in macros.

Reduce the Number of Formulas

This one’s a little bit tricky. I’m a big advocate of helper columns to break out formulas to make formulas more readable. This helps with formula auditing as well. However, every new helper column is more data, which increases the file size.

A useful strategy is to check for repeated formulas and use a helper column instead (Yes, I just said remove helper columns and now I’m recommending using them).  In some cases, if a formula is repeated in several columns, make one column containing the logic and link to that.

You should also check for Volatile Formulas (lookups, INDIRECT, OFFSET ). Ask yourself: are formulas necessary? Can the values be pasted instead of using formulas?

Compress Images 

Just like with websites, a spreadsheet image usually doesn’t need to be the 5MB 3300×2550 pixel image that came straight out of your camera.

Unless there’s a need to print in  high resolution, optimize and resize images before inserting them to the spreadsheet. Windows users can use tools like Irfanview, PowerToys or the built-in Windows Photos.  Mac users can use ImageOptim, Affinity Photo, or the built-in Preview.

Excel doesn’t treat copy and pasted images the same as inserted images.

Try this experiment with this image:

The file size when inserted normally is KB:

But when copy and pasted the file size grows to KB it converts the image to a bitmap (remove BMP files and Microsoft Paint? They’re the anti-JPEG). 

Save as an XLSB Binary File

After all of the above optimizations are done, sometimes the client is still concerned about the file size or slow opening and saving times..That is when I suggest using the XLSB file format.

XLSB is’s like minifying a JavaScript file and can be done as the last step. The workbook runs the same, except extra meta information is not saved with the file.

However, there are drawbacks.

The major disadvantageis that should the XLSB file ever become corrupted (Recover Saved File,anyone?) the file often can not be recovered. This is because XLSB doesn’t save any metadata – data about the data – which helps Excel rebuild a corrupt file.

I recommend using this method if the client has a good back system, i.e. Dropbox or even a regular process to make file copies.

Advantages

  • Speeds up loading time by 4X
  • Speeds up Saving Time 2X
  • Reduces File Size 50%
  • Formulas over the 8192 character limit will save properly

Disadvantages

  • Incompatible with other software like OpenOffice, Excel earlier than 2007
  • Security issues: depending on the users settings, the file maybe treated like an XLSM macro-enabled file though no macros exist XLSB replaces both XLSX and XLSM.

Excel is a significant tool for many businesses. Workbooks help us track critical data which is why we need them to run quickly and efficiently. It can be frustrating and detrimental when our file is too big and runs slowly.

If you find yourself with a workbook that needs optimizing, follow these steps to repair any issues and shrink your file size. Your Excel will run smoothly in no time.

Sign up for our Newsletter

Get the most out of your data by using… Dictum pellentesque dignissim tellus arcu diam aliquam ultrices. Feugiat vitae suspendisse odio nibh.

Related Posts