Whitelist this newsletter

The Excel Addict - Help with Excel 2007

Publication Date: January 13, 2010

This newsletter is being sent to 14,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.  

Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes - Get help from a Microsoft Excel AddictPeople always ask me, "Why do you spend so much of your time sharing these tips in your free newsletter?"

We, as you know, I am an EXCEL ADDICT, I love Excel and I love helping people. That's part of the reason. Another big reason I love doing this is that I often receive moving emails like this one.

"Hi Francis! After several blogs, and 'for dummies' e-books and websites of excel tips, I realized that yours for me is the best, just cause you write it so simple and with a sympathy attached that makes me learn and have fun at the same time. it's really nice to read your newsletter, you have that readying sympathy which distinguish you from others and for me you are kind of an example of what I want to do, transcending and serving others. Life should be simple, but many people have a strange feeling of complicating itself and lack the continuous desire of a daily improvement. PS Sorry if my writing is not the best, I've been 1 year since I haven't wrote on English. hehe have a nice day Francis!  -- David Garcia (Mexico)

"Life should be simple, but many people have a strange feeling of complicating itself and lack the continuous desire of a daily improvement".

My sentiments exactly.

Francis J Hayes (The Excel Addict)

Please type 'The Excel Addict' in the Subject field of your emails.
If you reply to this message, please delete the newsletter content below before sending.

View The Excel Addict Francis J Hayes's profile on LinkedIn   Find out what The Excel Addict is doing NOW

This week's tips..

1) Remove 'ALT+ENTER' Line Breaks Without VBA (XL2000-XL2007)
2) Simple Summary Page Formulas (XL2000-XL2007)
3) Turn Your Control Panel Into A Menu (Win XP)

If you're having trouble displaying graphics in this email, I have posted an online HTML version of this week's newsletter here.

Last week's tips were...

1) When Blank Cells Aren't Blank (XL2000-XL2007)
2) Display Dashes Instead Of Zeros (XL2000-XL2007)
3) Edit Word 2007 Documents In Print Preview (Word 2007)

You can access even more tips on my website by going to my members' page.  


"Discipline is the bridge between goals and accomplishment” -- Jim Rohn

Microsoft Excel Tip #1
Remove 'ALT+ENTER' Line Breaks Without VBA (XL2000-XL2007)

Remove multiple=You already know that you can enter a forced line break within a cell by pressing ALT+ENTER, right? If you you want to get rid of the line break you can simply click immediately before the line break and press Delete or click immediately after the line break and press Backspace.

But what if you have a worksheet that has a lot of these line breaks and you need to get rid of them? You may think that this would require a macro. You'd be wrong!

Here's a non-VBA method to quickly get rid of all those line breaks in your worksheet. If you want to remove them from only part of the worksheet, select that range first. Otherwise, select any single cell.

1) Press CTRL+H (i.e. shortcut for Find and Replace);

2) Click in the 'Find what' field, hold down the ALT key and type 0010;. It may not seem like anything happened but you actually entered an invisible line break character;

3) Click in the 'Replace with' field, press the spacebar once and click Find All. You will get a list of all cells containing a line break;

4) At this point you can choose to Replace All.

It may not be apparent but you may have caused some cells to have two blank spaces. You can easily find and get rid of them by repeating the previous 4 steps, using two blank spaces in the 'Find What' field and one blank space in the 'Replace with' field.

FYI, you can also use a formula to remove these line breaks.


Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Simple Summary Page Formulas (XL2000-XL2007)

Do you have a workbook with a summary page that summarizes data from all the other sheets? Here's a simple technique you can use to make your summary page formulas virtually maintenance free.

Cool technique to sum formulas across sheets

There are a couple of prerequisites for this technique to work. First, ensure that your summary sheet is to the left of all other sheets that it summarizes. Second, all of the sheets referred to must have an identical layout. This is necessary because each formula on the Summary sheet will refer to the same cell on all sheets.

=SUM(Jan:Dec!C5) will sum cell C5 on all sheets between Jan and Dec. This is a simple but powerful formula but there is a potential problem with it. If you insert a sheet between the Summary sheet and Jan or after Dec, the new sheet will be outside the reference of the Summary sheet formula.

Here is a trick to help you avoid this situation.

Step 1: Set up your sheets

1) Select the first sheet to the right of the Summary sheet;

2) Insert a new sheet;

3) Double-click the sheet tab and rename it to START;

4) Insert another new sheet;

5) Double-click the sheet tab and rename it to END;

6) Right click the END sheet tab and select Move or Copy;

7) Scroll to the bottom of the Before Sheet section and select (move to end);

Note: The START and END sheets will be left blank.

Cool technique to sum formulas across sheets

Step 2: Add your summary formulas

To easily create your Summary formulas...

1) Select a cell on your summary sheet;

2) Click the SUM tool (or press ALT+=);

3) Click the START sheet tab;

4) Hold down the SHIFT key and click the END sheet tab;

5) Click the cell you want to SUM (i.e the SUM formula will sum the same cell from every sheet);

6) Press ENTER.

You can now copy this formula for all other cells in your Summary worksheet.

Step 3: Hide your START and END sheets

1) Select the START sheet tab;

2) Hold down the CTRL key and select the END sheet tab;

3) In Excel 2007, right click the END sheet tab and select Hide. In Excel 2003, from the Format menu select Sheet, Hide.

Cool technique to sum formulas across sheets

Now whenever you insert a new sheet tab into this workbook it will always be added between the START and END tabs and will be automatically included in your summary sheet formulas.

101 Excel Tips revealed in this book
"I purchased your eBook and so did my boss.  Even though we are both experienced Excel user in the field of engineering and construction surveying, we find that your tips are top-notch and to the point; well above the standard fodder you find in the range of Excel books available on the market. Thank you" -- Richard J., Las Vegas, NV

Click Here to Download Excel Secrets eBook

Try out the secrets in my ebook for two months. If they 
don't save you 10 times the price of the book...

If you're not 100% satisfied...

...simply request a full refund from ClickBank.com

There's absolutely no risk to you.

If you're not 100% satisfied, simply request a full refund from ClickBank.com

Non-Excel Tip

Turn Your Control Panel Into A Menu (Win XP)

If you frequently access the Control Panel, and your settings are like mine (were), you point to Start, click on Control Panel (or Settings) and a new window opens with the Control Panel items.

If you're like me and prefer to have Control Panel displayed as a menu, there is an easy fix.

1) Right-click the Start button and select Properties;

2) In the 'Taskbar and Start Menu Properties' dialog, click the Customize button;

3) Click the Advanced tab;

4) Select the 'Display as a menu' option below Control Panel and click OK.

If you're happy with that, take a look a the other options on the Advanced tab. There may be another option or two that you would like to customize.

Turn your Control Panel into a menu

Pull answers from your lists with these 45 LOOKUP formulas

My goal: To reach One Million Excel Users<

Subscription Information

Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.

If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com

If you would like to share this newsletter with others...

1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed

2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com

3)  Post a link to TheExcelAddict.com in a company newsletter or website
"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