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.
|
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
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
|
Save your workbook and take
gleeful note of the change in its file size by selecting File
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
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.
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.
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.
|
To be sure you're
not missing anything, unhide any hidden sheets by selecting Format
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.
|