Is Your Workbook Suddenly Ballooning In Size?
by Francis Hayes (The Excel Addict)
Every once in a while I hear from someone with a problem workbook that has ballooned to multi-megabytes in size for no apparent reason.
Often this is caused by data or formatting or objects inadvertently being copied to rows and columns that are far from your actual worksheet data. With the newer versions of Excel having over a million rows and more than 16,000 columns, there are a lot of places for things to be hidden.
Before you try my suggestion below, make sure you have a backup copy of your workbook.
1) For each sheet in the problem workbook, go to the cell that is one row below and one column to the right of the last data cell. There should be no formulas or data above and to the right or below and to the left of the cells;
2) Hold down both the CTRL and SHIFT keys and press the down arrow (more than once if necessary) to go to the bottom of the worksheet. With the CTRL and SHIFT keys still held down, press the right arrow (more than once if necessary) to get to the far right edge of the worksheet;
3) Right click the highlighted area and select Delete, Entire Row, OK;
4) Right-click again and Delete, Entire Column, OK;
This will remove any junk and formatting that may have inadvertently gotten added to the cells below and to the right of your data cells.
5) Repeat this for each sheet in your workbook;
6) Save, close and re-open your workbook.
Another possible reason for a ballooning 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 text box (maybe in a column off screen) and paste this row down a column to hundreds of rows, that text box may also get copied and you end up with hundreds of duplicate text boxes.
It is very easy to 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 Goto 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 objects from the worksheet (If you don't want to delete all objects from the sheet, on the Page Layout tab, click on Selection Pane in the Arrange group. A Selection and Visibility pane will be displayed on the right side of your worksheet listing all objects. Hold down the CTRL key (sorry, Shift doesn't work) as you click each object name and then press the Delete;
5) Save, close and re-open your workbook.
|Copyright © 2016
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.