Bigger is not always better. This is certainly true for Excel files. Large workbooks take more time to open and calculate, occupy more storage space, consume more real-time resources, and are more prone to crashing and corruption. Like trying to lose weight, there are easy and harder ways to reduce, but you can have a smaller Excel workbook in just minutes by applying one or more of these techniques in this article. I report the results of the techniques I tested myself.

Except where noted, my test file is 50 full columns of nonempty cells for a total of 5,242,880 cells.

Remove What You Don’t Need

Delete Unused Sheets

It almost goes without saying to delete sheets you don’t need. You might have unused empty sheets. You might have leftover sheets from the development phase of a project. Be sure to check for hidden sheets also. From the Home tab, choose Cells, Format, Hide & Unhide, Unhide Sheet.

Clear Unused Cells

Excel “remembers” every cell on a sheet that was ever non-empty. Press Ctrl-End to locate the bottom-right corner of the “used” region of the worksheet. Select this cell and all cells above it and to the left that are empty. To clear these unused cells, choose the Home tab, Edit, Clear, All.

TIP:  Professor Excel sells an Excel Add-in to reduce the size of a workbook, plus almost 90 more time-saving tools.

Remove Unnecessary Formulas

Removing formulas cuts at the heart of Excel, but formulas do bloat the workbook. Whenever you have built formulas for a data set and that data is static in forever static, then convert the formulas to values. Select the formula cells. Choose Copy (Ctrl-C). With the formulas still selected, click the Paste command’s down arrow on the Home tab and choose Paste Values. Test results:  Converting over 5 million formulas to values decreased the size of the workbook by 56%. Wow!

TIP:  You can prevent many formulas in a workbook by writing VBA procedures that assign values to cells. In one compelling case study, the VBA If statement is faster than the IF function.

Remove Unnecessary Formatting

The living spaces of a home are usually more attractive than storage spaces. Likewise, cell formatting and conditional formatting add meaning to a worksheet. Formatting also increases the file size. When it comes to file size, plain formatting is more attractive. Test results:  Formatting increased the file size by 4%.

There are several ways to clear formatting without deleting formatted cells. To use a Ribbon command from the Home tab, choose Editing, Clear, Clear Formats.

All versions of Excel starting with 2016 and Excel Office Professional Plus 2013 come bundled with Microsoft’s Inquire Add-In. The Inquire tab has a Clean Excel Formatting command. To install the Add-in, choose File tab, Options, Add-ins. Choose COM Add-ins from the dropdown list, then click Go. Make a backup copy of the workbook first, because you cannot undo the “Clean Excess Cell Formatting” action.

Make Smaller Choices

Save as XLSB File Format

XLSB is Binary file format. XLSB file format was introduced with Excel 2007. Binary has only two values, zeroes and ones. Binary is a computer’s native language. The major advantage of XLSB format is efficiency. Workbooks in binary format are more efficient, because most of an XLSB file is already in binary code. An XLSB file is smaller than the corresponding XLSX file. Large XLSB files open faster than do XLSX files.To save the workbook as XLSB, choose File, Save As. For File Type, choose XLSB. Test results:  Saving an XLSX workbook with over 5 million formulas as XSLB format decreased the file size by a whopping 37%. Wow!

XLSB file format does have some disadvantages. Power Query does not recognize XLSB file format. Some third-party applications only recognize XLS, XLSX, and XLSM as acceptable file formats. XLSB file format does not differentiate between macro and macro-free workbooks. You can install a third-party Excel Add-In like spreadsheet1.com’s Macro Mover to check whether an XLSB workbook has macros before you open it. Versions of Excel prior to 2007 do not recognize XLSB file format.

Compress Images

Inserting an image to an Excel workbook increases the file size by the stored size of the image. This is true, even if you crop and resize the image in Excel. That’s because Excel stores the original image. Test results:  The file size of a 9 KB Excel workbook with one empty sheet increased to 170 KB when I Inserted a 159 KB image.

Compress the image before you insert it instead. You can do this with image editing software like Adobe Photoshop and free online tools like pixlr.com and TinyPng.com. Less effort and also less effective is Excel’s built-in Compress Images command. Select the image. From the Picture Format tab, choose Compress Picture (Adjust Group). Choose Email from the Compress Picture dialog.

Avoid Entire Rows and Columns

When practical, avoid formulas that reference an entire row or column. For example, use  =SUMIF(F2:F100, $H$1, G2:G100) instead of =SUMIF(F:F, $H$1, G:G).

This same rule applies to formatting. It’s tempting to select the entire worksheet with one click and set its font and alignment. That’s 17,179,869,184 formatted cells! Instead, format only non-empty cells. 

TIP:  Set the default font and font size for all new workbooks from File tab, Options, General.

Check Conditional Formatting rules too. From the Home tab, choose Conditional Formatting, Manage Rules. At the top, choose to show formatting rules for “This Worksheet.” Edit ranges that are much larger than necessary.

Use Numbers Instead of Characters

True numbers take less space to store than do characters. It requires one byte to store a single character like “A”. That same byte stores any number between -255 and 255. Whenever you have to assign unique values to a row, use true numbers whenever possible instead of text like an email address. Test results:  My test file with the letter “A” in over 5 million cells is 3.3% larger than a file with the number 1 in the same cells.

Embed Formulas in a Table

The Table object, introduced with Excel 2007, is vastly underutilized despite its great value and ease of use. Formulas in a table that reference cells in the table are more efficient than the same formulas in a normal range. To convert a normal range to a Table object, select any cell in the table. From the Insert tab, choose Table (Ctrl-T is the shortcut). Like a chart, a Table object has its own Table Design tab when it is selected. Test results:  For this test, I created an array of formulas 1000 rows by 25 columns in a new workbook. The file size with a normal range was 232 KB. The file size of the same range converted to a table was 81 KB. That’s a reduction of 65%, almost two thirds!

Table Object showing formulas with structured references

Cache in on Pivot Tables

When you create a pivot table, Excel automatically creates a pivot cache. The pivot cache is a copy of a data source and a hidden object. When you make changes to a pivot table, you are manipulating the pivot cache, not the data source. You don’t need to store both. Either delete the pivot cache and store the source data, or delete the source data and store the pivot cache.

Delete the Cache, Store the Source Data

Select any cell in the pivot table and choose Options from the Analyze tab, or right click the pivot table and choose Pivot Table Options. From the Pivot Table Options dialog box, click the Data tab. Uncheck “Save source data with file” (“source data” here refers to the pivot cache).  Also check “Refresh data when opening the file.” These two options generate the pivot cache only while the workbook is open. Test results:  Creating a pivot table increased the file size by 13%. Deleting the cache from this pivot table increased the file size by only 3.3%

Pivot Table Options to delete the pivot cache

When you decide to create more than one pivot table from the same data source, copy the pivot table instead of creating a new pivot table from scratch. Copying uses one pivot cache for multiple pivot tables.Test results:  Three pivot tables increased the file size by 21%. Copying the first pivot table two times to create three pivot tables increased the file size by 17% instead. Deleting the one cache shared by all three pivot tables increased the file size to merely 7.5% more than with no pivot table.

Delete Source Data, Store the Cache

When the source data is static, you can delete the source data and just store the cache. The default pivot table options are optimum for this technique. If you need to view the source data, double click the Grand Total cell to populate a new worksheet with the source data

ZIP-ITY DOO DAH!

Can you believe it? You can convert an Excel workbook to a ZIP file just by changing the extension from XLSX to ZIP! Before you do, first make a backup copy of the workbook. Then, change the file extension from XLSX to ZIP. This alone converts the workbook to a ZIP file. Doing this gives you two options for reducing the file size. 

NOTE:  Changing the file extension to ZIP does not decrease the file size!

Change and Change Back

Extract the ZIP file. View the ZIP folder. Select its contents of one  XML file and three folders. Zip this content. Change the extension from ZIP to XLSX. Test results:  A 23% reduction in file size was worth the effort of changing the file extension to ZIP, extracting the zip file, zipping its contents, and changing the file extension back to XLSX.

Locate Large Sheets

Extract the ZIP file. View the ZIP folder, then xl folder, then worksheets folder. Sort the sheets by size to identify the largest sheets.

And One More:  Use Volatile Formulas Sparingly

There are eight volatile functions, so named because they are calculated every time any cell is changed in the workbook. The volatile functions are RANDBETWEEN(), NOW(), TODAY(), OFFSET(), INDIRECT(), CELL(), INFO(), and SUMIF(). The arguments of the latter three determine whether the function is volatile or not. Treat these functions like luxury items..Use them sparingly to limit their impact on file size and performance.

You can have slim and trim workbooks. Chances are, you will save time in the long run by investing time to reduce the size of your workbooks.

See also:

• Runtime Error 2023 — How to Fix