Send Email. Connect With Customers. Grow Your Business.

Click Here to View it Online

THIS WEEK'S 'EXCEL IN MINUTES' TIP

Is Your Workbook Bloated And Slow To Open?

For new Excel worksheets, the 'used range' starts out as just cell A1. But, over time, as you work with your worksheet, the 'used range' expands to include every cell you make any data or formatting changes to.

Even after you delete data from those cells, the 'empty' cells remain as part of that worksheet's 'used range'.

Every now and then I get an email from someone with a problem workbook that has ballooned into multi-megabyte proportions for no apparent reason.

These megabyte-size workbooks are frequently slow to open, take forever to recalculate whenever a value is changed, and are often troublesome to send by email.

In the vast majority of cases, these problems are the result of either:

1. data, formatting or objects being inadvertently copied to cells that are outside of the actual worksheet data or
   
2. cells whose data has been deleted but the cell formatting remains behind.

f you do have a workbook whose file size appears to be out of proportion with the amount of data it contains, chances are you have one or both of the issues I mentioned above.

Note: Even if your workbook doesn't have a huge file size or is obviously slow to open and recalculate, most workbooks that you have been using for a while could possibly benefit from resetting it's worksheet's 'used ranges'.

So, "How do I fix it?", you ask.

Backup your workbook

Before you try my suggestions below, please be sure to make a backup copy of your workbook.

Also, check and make a note of the workbook's file size.

Check your worksheet's Used Range

1) To locate the bottom right corner of your worksheet's 'used range', press CTRL+End. If this brings you to a cell that is at or near the bottom right of your data, this sheet is OK and there's no need to reset the 'used range'. If the cell is far beyond the last column and or row of the data in your worksheet, continue with the next steps;



Reset your worksheet's Used Range

2) Scroll to the cell that is in the last row and last column of your data, then select the cell that is one column to the right and one row down;

here are no formulas or data above and to the right or below and to the left of this cell;

3) With this cell selected, press CTRL + SHIFT + End. This will highlight the cells from the 'active cell' to the bottom-right cell of your used range (i.e. the cell you found in Step 1);



4) Right click the highlighted area and select Delete, Entire Row, OK;

5) Right-click

9) If you want, close and check the file size of your workbook. Then reopen to see if the load time has improved.

One added benefit to resetting the last used cell in your worksheets is that your scroll bars begin to work correctly again. Now scrolling half way down the scroll bar will bring you half way down your worksheet data.

Find and Delete Phantom Objects

If the above steps still don't fix your 'used range', another possible reason for a workbook's bloated file size is objects such as text boxes, shapes, pictures, etc... being accidentally duplicated across many cells.

For example, if you copy a row that just happens to be beneath a picture (maybe in a column off screen) and paste this row down a column to hundreds of rows, that picture may also get copied and you end up with hundreds of duplicate pictures.

It is very easy to quickly and dramatically increase your workbooks size by inadvertently duplicating objects hundreds or even thousands of times.

Here's how you can quickly delete all objects from your worksheet - even ones you cannot see.

1) Press the F5 key on your keyboard;

2) In the Go To dialog click the Special... button;

3) Select the Objects option and click OK (all objects on the sheet will be selected);



4) Press the Delete key to delete all of the objects from the worksheet.

Choose which objects to delete

1) If you don't want to delete all objects from the worksheet, go to the Page Layout tab and click on Selection Pane in the Arrange group.

A Selection pane will be displayed on the right side of your worksheet listing all objects.



2) Hold down the CTRL key (sorry, the Shift doesn't work here) as you click each object name. If there's an object listed that you can't locate in your worksheet, select it on the Selection Pane and press the F2 key to bring focus to it.

3)  To delete the selected object(s), press the Delete key;

4) Save these changes to your workbook;
 
5) Press CTRL+End to verify that the 'used range' has been reset;

6) If you want, close and check the file size of your workbook. Then reopen to see if the load time has improved.