Worksheet Deletions Don't Reduce Workbook File Size

Dave has a workbook that was growing in size over time, and he was trying to shrink it. One worksheet contained several PivotTables that were no longer needed, so he deleted that worksheet. The file size did not reduce as expected. To investigate, Dave began deleting all the worksheets in the workbook except one that contained minimal data. The file size stayed huge, so Dave wonders why these major deletions did not reduce the file size.

There are a few things that could be causing this issue, but before jumping into them I want to make sure that the bottom-line assumption in this question is stated fully up front: Dave has deleted all worksheets in the workbook except for a single worksheet that contains only minimal data.

Given this, here are some things to check out:

Are there any hidden worksheets in the workbook? If so, make them visible and delete them, too.

Are there unwanted objects in the remaining worksheet? Press F5, click Special, choose Objects, click OK, then press Delete.

Are there any unused named ranges that you've defined? Display the Name Manager and delete any that you no longer need.

Are there any links to external workbooks or objects? Display the Data tab of the ribbon, click Edit Links, and delete any you no longer need.

Are there phantom cells in the worksheet? Press Ctrl+End to find what Excel thinks is the last-used cell. Delete any unneeded rows above that cell and unneeded columns to its left.

Now, save your workbook using Save As, and make sure it is being saved as an XLSX file. This will get rid of any macros and, more importantly, any user forms that may be defined in the workbook.

That should take care of just about everything, but if not, then it is possible that the workbook is maintaining caches for PivotTables and slicers for some reason. The caches should have been automatically deleted when you deleted the objects and then saved the workbook, but "should have" doesn't always translate to "was." You can use a short macro to make short work of the caches:

Sub ClearCaches()
    Dim pc As PivotCache
    Dim sc As SlicerCache

    For Each pc In ActiveWorkbook.PivotCaches
        pc.MissingItemsLimit = xlMissingItemsNone
        pc.Refresh
    Next
    For Each sc In ActiveWorkbook.SlicerCaches
        sc.ClearManualFilter
    Next
    Application.CalculateFull
End Sub

Remember, as well, that in the latest versions of Excel you can enlist the help of the Document Inspector to get rid of any hidden information. Follow these steps:

1. Display the File tab of the ribbon and then click Info at the left side of the screen.
2. Click the Check for Issues button just to the left of the Inspect Workbook option. Excel displays a few options.
3. Choose Inspect Document. Excel displays the Document Inspector.
4. Since you are trying to make your workbook as small as possible, make sure all the options are selected.
5. Click the Inspect button. Excel examines the workbook and provides a report of what it finds.
6. At the right side of anything found by the Document Inspector is a Remove All button. Click the buttons for those items you want to remove.
7. Click the Close button to dismiss the Document Inspector.

There is one final thing to keep in mind—every time you delete something and then save the workbook, make sure you use Save As and provide a filename different than the current filename. This is because the operating system (not Excel) is not always good about releasing disk space previously allocated to a file when the file decreases in size. Saving under a new name handles this problem easily.

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