Hi fellow Excel Addict,
It's a beautiful spring day here in Newfoundland. Not quite as warm as many of us here would like (when the wind is blowing from the north) but I'm sure there are many places around the world who would love to have some of our cool spring weather. You'll have to come to Newfoundland.
In today's 'Excel in Minutes' tutorial, I offer a solution to a problem that I'm pretty sure every Excel user has experiences at some point. Over the years I have been asked about this a hundred or more times.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Filter Your Data for Unrelated Items'. You can read all about that here.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Are Your Worksheets Bigger Than You Realize?
In the past two weeks I've received three emails from subscribers with the same problem. When they press CTRL+End (i.e. the keyboard shortcut that should take them to the last-used cell in their worksheet), they end up at a cell that is way past the last cell where their data is located.
In the image below, the last-used cell is just a few rows and columns past the actual spreadsheet data but in reality it can be hundreds or even thousands of rows or columns away.
The most common cause of this is when formatting has been applied to cells outside of the data area of you worksheet.
Many Excel users select entire rows or columns when they want to apply formatting to the rows or column in their worksheets. If you do this, you are formatting not only the cells that contain your data, you are also applying the same formatting to tens of thousands, maybe even millions, of cells. Excel treats all of these formatted cells as part of your worksheet and therefore it may actually be much larger than you realize.
In addition to the CTRL+End keyboard shortcut not working, all of this extraneous formatting can dramatically increase the file size of your workbook — often to the point of slowing down the processes of calculating, opening, and saving.
If you have worksheets where the last-used cell is 'out of whack', here's a solution that most likely will fix it.
Warning: Make sure you have a saved copy of your workbook before doing this.
1) Scroll 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) Starting from that one cell, you are going to select all of the cells below and to the right of your worksheet data. 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.
At this point, you have all of the cells below and to the right of your data selected;
3) Right click this highlighted area and select Delete, Entire Row, OK; **
4) Right-click again and Delete, Entire Column, OK; **
This will clear all data and formatting that may have inadvertently gotten added to the cells below and to the right of your actual data cells.
5) The last-used cell won't be reset until you have saved changes to your workbook, so Save your workbook now;
6) Now press CTRL+End to test that your last-used cell is working properly.
** If you receive an 'Out of memory' error when deleting large numbers of rows or columns, try deleting smaller ranges;
Another issue that can significantly increase your workbook's file size is when objects such as text boxes, shapes, pictures, etc... are accidentally duplicated across many cells. For example, if you copy a row that contains and object (maybe in a column off screen) and paste this row down a column to hundreds of rows, that object may also get copied and you end up with hundreds of duplicate objects.
Over time, it is possible to end up with thousands of duplicate objects all over your workbook. This can very easily increase its file size.
You wouldn't want to have to find and delete thousands of objects, so here is a quick way to find and delete all of the objects from your worksheet - even the 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 your worksheet, 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 (the Shift doesn't work) as you click each object name and then press the Delete key;
5) Save the changes to your workbook.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630