Shrinking Workbook Size

by Allen Wyatt
(last updated July 29, 2017)

13

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),

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 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/Reduce-the-file-size-of-a-picture-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(), 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 cacluation 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, and 2016.

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

Reference to a Range of Endnotes

When multiple endnote references are used at a given point in your document, you may wonder if there is a way to compress ...

Discover More

Copying a File

Making copies of files is a snap in Drive. Here are a couple of ways you can perform this common task.

Discover More

Specifying a Data Validation Error Message

Data validation is a great tool for limiting what can be input into a cell. Excel allows you to specify what should appear on ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Duplicate Workbooks Opening

If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the number ...

Discover More

Viewing Workbook Statistics

Excel keeps track of a range of stats about each workbook you use. If you want to take a look at those stats, it's easy; just ...

Discover More

Printing Workbook Properties

Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. Here's ...

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}] 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 nine more than 8?

2017-08-19 06:57:47

Joel Courtheyn

Allen, very good posting giving rise to interesting discussion.
Ruthie and David : very interesting comments !


2017-08-03 18:57:19

David

hi all - here is some very useful VBA code to remove bloat in Excel workbooks. http://www.vbaexpress.com/kb/getarticle.php?kb_id=83
I have used this tool with great success, many thanks to Jacob Hilderbrand .
David


2017-08-03 05:33:36

Ruthie A. Ward

When deleting blank rows and columns, the "save· the workbook , restart Excel , reload the workbook , and check· each worksheet again" steps aren't really necessary. Press Control+End to find the last cell with data or formatting, delete as needed.
Reset the active range on your worksheet and then press Control+End again to ensure that they're really "gone". Use the macro below:
Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub


2017-07-31 19:57:03

David A. Gray, MBA

This is an excellent and comprehensive overview of factors affecting document size and performance.

With respect to performance, one thing that I would add is that I frequently build up formulas, only to discover that I need a portion of the same formula elsewhere. Rather than repeating the segment, I'll segregate it into a new cell, and have the other cells that need the value that results from processing that fragment reference the cell that contains the fragment. This has the effect of letting the recalculation engine reuse the fragment, instead of recomputing it every time it's needed. In a large worksheet, the performance improvement can be substantial, especially when the fragment contains aggregate functions, such as sums or averages, or lookups.

When you have one or more rows or columns of intermediate formulas, if you group them in adjacent rows or columns, you can hide them for presentation and day-to-day use.

One other thing to remember about these hidden rows is that they need labels, which serve two purposes. First, they keep the label row contiguous, which helps ensure that Excel selects the correct range for sorting. Second, they give some hint about the nature of the formula.


2017-07-30 08:10:19

CJ

Checking the unhide option to determine if there are hidden worksheets is not 100% reliable. If all hidden worksheets are "very hidden" then the unhide option will not be selectable. One way to check for "very hidden" worksheets is to open the VBA IDE and check the visible property of each worksheet.


2017-07-30 06:50:48

Nenad Stevanović

Easiest way to realize witch worksheet consumes most space and needs most work is to rename extension from etc. .xslx to .zip. Then just unzip it see witch worksheet form 1 to infinite (1 is first on left in workbook) is biggest.


2017-07-29 15:45:49

Erik

Allen, this is the most comprehensive article you've written for this site since I can remember. I especially like how you give links for related articles with more detail. Thank you for your awesome work!


2017-07-29 15:39:09

Aldo Santolla

I use a lot of custom macros in some of my larger workbooks. To help with speed on these worksheets, I use triggers in my code to disable Events and Auto Calculations. This prevents other functions from working while my custom macro is doing it's thing. Then afterwards when all is done, I re-activate Events and Auto Calculations, and force a full sheet recalculation. Saves a lot of processing time!


2017-07-29 15:00:40

Evan Lynn

I have an Excel program .xlsm running on Faith Hope and Charity at about 82 mb and it crashed several times both on using and saving. I changed over to .xlsb and dropped it down to about 12 mb.


2017-07-29 11:40:47

Brian Lair

Thanks, Alan! Great, well-written post with lots of good ideas, some of which I hadn't thought of before. For instance, I sometimes use NOW() in tables to have a calculated column showing elapsed time since a date in another column. Thanks to your note about volatile functions, I'll consider putting the NOW() in a single cell outside the table & then referencing that cell in the table formulas.

Regarding disk space being cheap, that is true -- however large files can still be an issue in a work environment where workbooks are shared. For example, people often share huge workbooks using email or server file shares, and this can cause problems over time in corporate environments that impose user quotas restricting the size of inboxes, email folders, and file shares. In addition, a large Excel file may load quickly enough from the author's PC, but if it is shared via a server or in a collaboration system on the network, loading may be annoyingly long for the other users.


2017-07-29 06:33:45

Willy Vanhaelen

Also check if there are user defined functions using the Application.Volatile method. Use this only if it is really necessary.


2017-07-29 06:18:37

David George

Interesting post. However, with hard drive space dirt cheap, I can hardly see anyone being concerned about the physical size of a workbook.

My biggest, most frequently used workbook is 5.2 MB--hardly a concern. Perhaps others have much bigger workbooks.

Several points you've raised and suggested limiting are what for me anyway are the most useful aspects: formatting variations, formulas, and hyperlinks. I use all of these extensively. The only glitch I run into with another fairly big workbook is the limit on formatting variations: Is there a specific limit? Is there a way of increasing the limit?


2017-07-29 05:00:34

Barry

You've got a lot of great ideas here, Allen. Good job.


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.