FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
June 2, 2016 

Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis hayes (The 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)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


If you missed my last newsletter, you can click here to view it online. 
 
TheExcelAddict.com Quote of the Day

"He who would do some great thing in this short life, must apply himself to the work with such a concentration of his forces as to the idle spectators, who live only to amuse themselves, looks like insanity."
-- John Foster
--

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

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.

Incorrect Last Used Cell in Microsoft Excel 2007 2010 2013 2016 365
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.

Did you know that there are more than 17 billion cells in an Excel worksheet?More Than 17 Billion Worksheet Cells in Microsoft Excel 2007 2010 2013 2016 365

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.

YOU CAN DOWNLOAD THIS PRACTICE FILE IF YOU WANT TO FOLLOW ALONG WITH THIS TIP

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;

Actual Last Used Cell in Microsoft Excel 2007 2010 2013 2016 365
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.

Clear Unused Worksheet Cells in Microsoft Excel 2007 2010 2013 2016 365
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;


Duplicated objects
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.

Duplicate Objects in Microsoft Excel 2007 2010 2013 2016 365
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.


June 6, 2016 Girish T. -- Thank you Francis! You won’t believe, 2 of my excel files which were over 27 MB just reduced to under 200 KB!!! The files had 8 to 9 sheets. They had huge number of formulae and tables. I thought it is obvious to have such a size because of these. The files used to take more than a minute to open. Now after the “hidden” formattings were removed, the files are quick to open as the size dramatically reduced. 


It's about time to learn macros in Microsoft Excel 2007 2010 2013 2016 365



Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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