|   Ever notice that your workbook is increasing in size at an alarming rate for no apparent reason? There are several causes of workbook bloat, and some slimming solutions . Have you ever eaten so much that you can't function properly? Workbook bloat in Excel is much the same thing. Workbook bloat is a term for a workbook that has had so much done to it that it has swollen to such a size it can no longer function correctly. We checked out the size of a typical workbook containing a fairly large amount of data. With data only, the workbook file size was 1.37 MB. Then we added a pivot table referencing four entire columns for its data source and noted that the file size increased dramatically to 2.4 MB. Add some formatting and your typical workbook size has blown out to almost double by performing a few actions. One of the more common causes of file bloat, particularly in earlier versions of Excel, is the application of formats to entire columns or rows rather than to just the data range in use. Another mistake is referencing entire columns as the data source for charts and pivot tables rather than just the cells with actual data in them. To fix these problems, you will need to eliminate all the superfluous formatting and restrict your data source to only the useful range of cells .  | Before doing such refactoring, always make a copy of your workbook for safekeeping. | |
Eliminating Superfluous Formatting The first step in eliminating superfluous formatting is to figure out where your worksheet's data endsthe bottom righthand corner of your data, if you will. Don't rely on Edit  Go To... Special Last Cell, as this might take you to the last cell containing formatting, not actual data. Having manually located the cell you know to be your last cell containing legitimate data, highlight the row immediately following it. While pressing the Ctrl and Shift keys, press the down arrow on your keyboard to highlight all rows beneath that row and select Edit Clear All to clear them. Now apply the same logic to unwanted formatting lurking in your columns. Locate the cell in the last column containing data and click the column header of the column immediately to the right. Press Ctrl-Shift and the right arrow on your keyboard to highlight all other columns to the right and then select Edit Clear All. | | Don't be tempted to actually delete these rows or columns rather than clearing them, as doing so often causes the dreaded #REF! error in any cells of any formulas that might reference them. | |
Save your workbook and take gleeful note of the change in its file size by selecting File Properties... General. If you have macros, now you need to address the modules that the macro code resides in. This is a fairly quick, painless, and straightforward process that entails exporting all modules and UserForms to your hard drive and then deleting the existing modules and UserForms, pressing Save, and importing the modules you exported. To do this, go into the Visual Basic Editor and, from within the Project Explorer, right-click each module and select Remove Module1 (or whatever the name of the module happens to be). When you are asked whether you want to export your module before removing it, say Yes, taking note of the path . Do this for each module in turn , as well as for any UserForms you might have. Don't forget the private modules of your workbook and worksheets if they house code as well. Once you have done all this, save the workbook. Then, select File Import File and import each module and UserForm back into your workbook. Following this process will create a text file of each module and that, in turn, removes all extra baggage that the modules might be holding. The Web contains some free utilities that will automate this task to some degree, but we have heard cases of these utilities making a mess of code or even increasing file sizes. If you do use one of these, always save a backup copy first, as the developers will take no responsibility for any loss of data. Honing Data Sources If, after performing the previous steps, you still believe your file size is unrealistically large, another possible suspect is referencing unused cells in PivotTables and PivotCharts. This is true particularly of PivotTables, as people frequently reference all 65,536 rows in order to avoid manually updating ranges as new data is added. If this is your modus operandi, use dynamic named ranges [Hack #42] for your data sources instead. Cleaning Corrupted Workbooks If you still believe your workbook is too large, it is possible that your workbook or component sheets are corrupt. Unfortunately, determining a point of corruption requires a manual process of elimination . | | Again, we strongly advise you to save a copy of your workbook before proceeding. | |
To be sure you're not missing anything, unhide any hidden sheets by selecting Format Sheet Unhide. If this menu option is grayed out, you have no hidden worksheets to worry about. With all your sheets visible, start from the sheet on the far left and move one-by-one to the right. For each in turn, delete it, save your workbook, and note its file size by selecting File Properties General. If the file size drops dramatically considering the amount of data on that sheet, you've probably found your corruption. To replace a corrupt sheet in your workbook, create a new worksheet, manually select the data in the corrupt sheet, and cut (do not copy) and paste it into the new sheet. Delete the corrupt sheet from your workbook, save, and repeat.  | By cutting rather than copying, Excel automatically will follow the data to the new sheet, keeping references intact. | |
| 