Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 14 Reduce Workbook Bloat

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.