Shrinking Workbook Size

Written by Allen Wyatt (last updated August 19, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

Nolan is noticing that some of his workbooks are getting quite large. He would like to shrink the size of the workbooks, but isn't sure which approaches to shrinking them will deliver the best results. He is, therefore, looking for the best suggestions on how to shrink the size of his workbooks.

Before getting into some real "hands on" ideas you can apply in this area, I should mention that it is important to figure out why the large file sizes may be of concern. After thinking about it for a while, there are two general areas of concern that I've come up with. First, people may be concerned with large file sizes because the files take a lot of disk space or they take a while to transfer over a network or across the Internet. Second, they may be concerned because larger files take longer to load and to recalculate.

Let's discuss both of these concerns in turn.

Physical File Size

The actual physical file size of a workbook is affected by two primary factors—the elements that you are saving within the file and the format in which you save that file. Elements in the file are things like worksheets, rows and columns, styles, PivotTables, multimedia (images, sound, and videos), etc.

It almost seems unnecessary to say that a workbook consists of worksheets and those worksheets, in turn, consist of rows and columns. What you may not realize, however, is that worksheets can be hidden and that worksheets can contain superfluous rows and columns.

First, unhide all the worksheets in your workbook. It's easy to check if you have any hidden; just right-click one of the worksheet tabs in the workbook. In the resulting Context menu, if the Unhide option is selectable, it means you have hidden worksheets. Select the option and you can see a dialog box that shows all the hidden worksheets. Unhide them, and then determine if you really need them or not. If you don't, delete them to free up the space.

Now, in each of the remaining worksheets, you'll want to check to see if there are any extraneous rows or columns being saved by Excel. The easiest way is to display the worksheet and press Ctrl+End. This selects whatever Excel thinks is the last row and column in the worksheet. If it doesn't match what you believe is the last row of column, simply delete everything beyond the actual last row and column.

There is one caveat here: When you delete the extraneous rows and columns (make sure you do it on every worksheet), you'll want to save the workbook, restart Excel, reload the workbook, and check each worksheet again. Doing this is a "double check" to make sure you really deleted the extraneous rows and columns rather than just clearing them. (They actually need to be deleted, not just cleared.)

There is one other thing you can check when it comes to unnecessary space, but it applies only if you paste data in your worksheets from a non-Excel source or if you import data generated by a third-party program. In some instances, you may find that your data contains extraneous spaces at the end of text values. For instance, instead of simple text such as "This is my example text," the cell may contain the text followed by multiple spaces. This will take some detective work to figure out if your cells have extra spaces, but toward that end you may find the following formula helpful:

=IF(LEN(A1) <> LEN(TRIM(A1)), "Extra spaces", "")

Relying on the TRIM function to remove spaces at the beginning or end of the cell, the formula displays the text "Extra spaces" if the length of the cell contents aren't the same. Delete the extra spaces from any offending cells.

Styles can also take up space in your workbook, so it is a good idea to delete styles you may not be using. The best ways to go about doing this are covered in a separate ExcelTip:

https://excelribbon.tips.net/T012259

You may also consider reducing the number of formatting variations used in a workbook and the number of times you use data validation. These can both inflate the size of a workbook, so you'll especially want to check that formatting and data validation is not being applied to empty cells.

If your workbook contains PivotTables, understand that this can increase the size of your workbook files quite a bit. There is a way, however, to still use PivotTables but reduce the overall size of your workbook. This technique is best covered in a different tip, which you can find here:

https://excelribbon.tips.net/T008669

When it comes to multimedia elements in a workbook, you'll want to examine each image, sound file, and video you may have in the workbook to determine if you really need them. Multimedia elements can eat up a LOT of space, so you should only use them if absolutely necessary. In addition, when it comes to images, you'll want to check to make sure you aren't cropping the images within Excel. Cropping images hides what is shown from the image, but it doesn't reduce the actual size of the image saved in the workbook. Instead, save the images to disk, edit them with an actual image editor, and replace them into the workbook.

Excel also allows you to compress pictures and reduce the resolution of any pictures you may have in the workbook. Balancing compression, resolution, and quality can be a tricky tightrope to walk. If you want to start walking it, though, you may find this article on one of Microsoft's sites helpful:

https://support.office.com/en-us/article/8db7211c-d958-457c-babd-194109eb9535

Finally, look at the format you are using to save the file. Load the workbook, press F12 to display the Save As dialog box, and save workbook under a new name. This forces Excel to rewrite the file from scratch, which can eliminate any unused space in the file.

Another thing to check is if you can save the file in XLSX format instead of XLSM. The latter file type is only necessary if you have macros in the workbook. If you don't need macros, then use Save As (again, F12) and use the Save As Type drop-down list to specify you want the XLSX format.

If this still doesn't get the file size down to where you want it, use the Save As dialog box and choose to save the file in XLSB format. This is a binary format that can save a ton of space in the file.

Load and Recalculation Times

If your major concern with file size is related to load times and recalculation times, then the problem isn't just what is stored in your workbook. You should still work through all the items covered earlier in this tip, but your workbook may still take a long time to load. This is because workbooks are automatically recalculated when loaded, and if your workbook contains a lot of data or a lot of complex formulas, it is the recalculation—not the loading—that is slowing things down.

To minimize calculation time, you'll want to check out a few things. Understand checking (and correcting) these things won't impact the file size all that much, but it will result in faster load and save times.

  • Get rid of circular references. The iteration required to resolve circular references takes time, and if you have a lot of circular reference formulas, the time required can really add up.
  • Avoid volatile functions. Some of Excel's worksheet functions are considered "volatile," which means they trigger a recalculation every time any cell in your worksheet is changed. These functions are RAND(), RANDBETWEEN(), RANDARRAY(), NOW(), TODAY(), OFFSET(), and INDIRECT(). In addition, the INFO() and CELL() functions may be volatile, depending on the arguments used with the functions.
  • Rework your formulas to be more efficient. This takes time, detective work, and knowledge, but you can often rewrite your formulas to use different techniques that will speed up calculation. (This idea is one that is best to apply over time as you gain the knowledge necessary or as you run across tips and tweeks that can be applied in your situation.)
  • Minimize the use of formulas. If you are using formulas to calculate cells and the values on which those formulas rely are never changed, then you should consider getting rid of the formulas themselves. The easiest way to do this is to select all the cells, press Ctrl+C, and then use Paste Special to paste values back into the cells.
  • Get rid of external links unless you absolutely need them. Fetching information from external sources (particularly sources over a network) can really slow down load times.
  • Use PowerPivot and PowerQuery. If you are using Excel 2010 or a later version and you are using Excel to process large amounts of data, consider reworking your worksheets to rely on the newer PowerPivot or PowerQuery tools. These have proven to be not only very powerful, but also very fast in processing large amounts of data.

If your calculation times are still quite long, you may want to consider turning off automatic recalculation and only manually recalculate when you are ready to expend the time necessary to do so. You can turn off automatic calculation using the techniques discussed in this tip:

https://excelribbon.tips.net/T009999

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6792) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting a Break with a Macro

Inserting a break in your document is easy. You may think that inserting one using a macro is more complex, but it isn't. ...

Discover More

Understanding AutoComplete

Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...

Discover More

Self-Deleting Macros

Macros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Workbook not Saving

Having trouble saving a workbook? It could have to do with the age, size, and complexity of that workbook. This tip ...

Discover More

Seeing a Worksheet Thumbnail in Windows

When you save a workbook, you have the opportunity to save a thumbnail image that can be displayed within Windows. Here's ...

Discover More

Opening a Workbook as Read-Only

When you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2023-08-21 05:44:29

DaveS

With formatting, worth remembering that conditional formatting can really slow calculation down if applied too liberally.

And for xlsm files, worth remembering that there can be 'very hidden' worksheets which won't show up in the list when right-clicking on a tab; you need to open the vba project to see if there are any. Because such worksheets are usually there to store information or handle background tasks out of sight, chances are they won't have fancy formatting but they could include other things mentioned in the article such as volatile functions or complex formulas.


2023-08-19 11:13:14

J. Woolley

For related discussion, see https://excelribbon.tips.net/T008030_Finding_the_Size_of_a_Workbook.html


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.