Software

How to Recover from Excel Workbook Bloat

The average Excel workbook is about 14 kb. Occasionally, you end up with a file that's much larger than it needs to be. Bloat can turn into a serious problem for busy workbooks. As the file grows, performance slows down. In fact, occasionally a workbook grows so large that users have trouble just opening the file, let alone entering and deleting data. You'll want to take care of bloat before it reaches that stage.

Formats are probably the biggest culprit for the average user. For the most part, you just have to live with that. You can help avoid bloat by deleting formats when you delete data (admittedly, that's difficult to do). Also, use column and row formats on large areas instead of individually formatted cells. If you format 500 cells, Excel saves 500 formats. If you format a column, Excel saves just one format.

Another hog is unused cells. By unused, I mean empty cells that fall between areas of data. Excel's used range property (UsedRange) includes every cell that's ever been used. That means, if you use a cell way out in la la land for something, Excel consumes resources to maintain not just the la al land cell, but also all the empty cells in between.

To test a sheet, press Ctrl+End. Excel will select the cell that marks the end of your data. It might surprise you to find out just how far away from your spreadsheet that cell lies.

Fortunately, you can reclaim the desert that sucks up your resources unnecessarily. Simply delete the data and formatting in that far-off cell. If you're actually using the contents, consider moving it to another sheet.

Before you delete or move anything, make a backup of the workbook, just incase. Also, if you didn't create the workbook, discuss what you're about to do with its creator. Doing so might avoid the unintended consequences of not knowing the workbook's full scope.

When you're ready, press Ctrl+End to find the anchor of the sheet's used range. Then, delete any data and formatting you find there. If the cell is formatted, the quickest solution is to delete the entire row (or column). To do so, click the row (or column) header to select the entire row (or column) and then choose Delete from the Edit menu.

Then, save the workbook and press Ctrl+End again. The used range should be smaller. You might have to do this a few times if you have forgotten cells and formats scattered throughout the sheet.

This process is easy and significantly reduces the size of your workbook. I recommend that you update the used range property on a regular basis if you have a busy worksheet in which you enter and delete a lot of data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks