Shrinking Workbook Size

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.