Software optimize

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.

4 comments
alex.krenvalk
alex.krenvalk

Try - xlsx repair, this tool can scans the specified Microsoft Excel worksheet and gets all available information from it, recovered from the damaged folder, a single mouse click is enough to export the data to Microsoft Excel and continue working with it, supports almost all existing versions of XLS and XLSX formats: Microsoft Excel 97, 2000, XP, 2002, 2003 and 2007 (xlsx repair).

dogknees
dogknees

The first thing I try is to simply delete all rows and columns outside the used range. The rows and columns re-appear immediately(they aren't really deleted, just cleared) but the formats are removed. Select the cell in column A of the first row below the data. Then hold down the shift key, hit End, Down, End, Right, let the shift key up, then hit Ctrl - (minus sign on the keypad). This clears all the rows below the data. Then select the cell in row 1 of the first column to the right of the data. Hold down Shift and hit End, Right, End, Down, and release Shift. Then again hit Ctrl -. This clears all unused columns. Doing it this way removes any column or row formats outside the used area as well as the individual cells. This takes much less time to do than it takes to write! Try it. Regards

CharlieSpencer
CharlieSpencer

That worked nicely. Apparently I had a bunch of formatting buried somewhere but couldn't find all the occurances. I may get motivated and record this keystroke sequence. I don't use Excel much beyond an inventory spreadsheet, so I picked up some keyboard shortcuts too. Thanks.

ssharkins
ssharkins

This really is the simplest and quickest way -- but you have to be careful you don't accidentially delete something you meant to keep. I think the days of multiple sheets make this less likely though. Most of us put those lone formulas, etc. on a sheet of its own instead, which makes the multiple-row/column delete less dangerous.