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.
|
Would
you believe me if I told
you that
you can DOUBLE your productivity in Excel? How about TRIPLE it ? or QUADRUPLE it ? or MORE ? If I showed you a tip where you could do a task in Excel in five seconds that would typically take you 20 minutes to an hour or more, what would you call that? Productivity on steroids? ...and that's just ONE tip! "Give me 10 or 15 minutes each week and I will show you how learning a few of my time-saving 'Spreadsheet Tips From An Excel Addict' on a regular basis will seem almost effortless BUT will quickly help you BOOST your productivity in Excel beyond your imagination" - Francis Hayes, The Excel Addict Get my FREE
Weekly Newsletter for
FREE
"Spreadsheet Tips From An Excel Addict" Now being read by more than 37,000+ Excel Addicts all over the world |
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.