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.
Hi fellow Excel Addict,
People 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.
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.
Remove 'ALT+ENTER' Line Breaks Without VBA (XL2000-XL2007)
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.
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.
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.
Here is a
trick to help you avoid this situation.
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);
Step 2: Add 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.
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.
If you're not 100% satisfied, simply request a full refund from ClickBank.com
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.
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