by Allen Wyatt
(last updated July 29, 2017)
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:
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:
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:
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.
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6792) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Having trouble saving a workbook? It could have to do with the age, size, and complexity of that workbook. This tip ...Discover More
Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based ...Discover More
Have you ever been working with data in Excel and experienced a "freeze" where the program stops responding? This can be ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.