Whitelist this newsletter

The Excel Addict - Help with Excel 2007

Publication Date: January 20, 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 AddictThanks for taking time from your busy schedule to join me for another edition of my newsletter. Every week I get 4-5 people who decide to cancel their subscription to my newsletter. Believe it or not, the #1 reason for canceling is, "I don't have time to read your tips".

Ever since I started using Excel over 18 years ago, I was always puzzled by the majority of my co-workers who were 'too busy' to learn the tricks and shortcuts I offered to show them.

Often they were 'too busy' because they were spending hours re-entering data into their worksheets that they already had somewhere in another worksheet or workbook.  I say, if you would only learn Excel's most useful function, VLOOKUP, it would save you from all that wasted time. Last month I helped a new co-worker of mine save almost two days work per month by showing him how to use the VLOOKUP function in one of his reports.

If you would like to learn more about VLOOKUP and many other of Excel's powerful, time-saving LOOKUP functions, check out John Franco's e-book 'The Excel LOOKUP Toolbox'. He will show you tricks you can use to magically pull data from your tables into your reports using a variety of Excel's LOOKUP functions. This e-book is getting some great reviews.

Excel Addicts Supporting Haiti
Because of the unfortunate tragedy that is taking place in Haiti, I have taking liberties as publisher of this newsletter to personally help out the best way I know how and also to allow you to participate with me in my Haiti relief campaign. A few people have been offended by this, because it is outside the scope of this newsletter, but the vast majority have been overwhelmingly positive. The emails have been amazing.

I always try to keep my newsletter focused on Excel and I promise to not make this newsletter my personal soapbox. But this is an extraordinary tragedy and if what I'm doing upsets a few people, so be it. This is an opportunity that I cannot let slip by.

If you want to see the latest results of our campaign or if you want to participate by purchasing a copy of my e-book (
100% of the proceeds goes to the Red Cross Haiti Relief fund), click here.

Money will go a long way to help the people of Haiti but they also need our prayers.

Keep on Excelling,
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) How To Calculate Elapsed Time Greater Than 24 Hours  (XL2000-XL2007)
2) Restrict User's Access To A Specific Worksheet Range (XL2000-XL2007)
3) Easily Move Paragraphs In Microsoft Word

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) 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)

You can still find last week's newsletter here.

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


"Too often we underestimate the power of a touch, a smile, a kind word, a listening ear, an honest compliment, or the smallest act of caring, all of which have the potential to turn a life around." -- Leo Buscaglia

Microsoft Excel Tip #1
How To Calculate Elapsed Time Greater Than 24 Hours  (XL2000-XL2007)

Excel is used every day for thousands of varied tasks. One of these tasks is managing time records. Every new Excel user invariably runs into two problems the first time they try to calculate 
elapsed times.

With Excel, calculating elapsed time is simply a matter of subtracting the start time (cell) from the end time (cell). For example, with a 9 am start time and a 11 pm end time, you subtract 9 am from 11 pm (e.g. =B3-A3 ) to get 14 hours elapsed time.

Are you having problems calculating times in Excel?Sounds simple, right? Well the first problem you will probably notice when you do this is that the result of you formula shows 2:00 PM. Not what you were expecting, right? The reason for this is that, since the cells adjacent to the cell with the formula contains time values, Excel assumed that the formula result is also a time value and automatically applied a time format.

You can easily correct this by eliminating the AM/PM
portion of the number format code. Press CTRL+1 to open the Format Cells dialog. On the Number tab, click Custom in the Category list. The current format of the cell is shown. In the Type field delete AM/PM from the end of the number format (e.g. 2:00 PM changes to 14:00 ) and click OK. Problem solved!

Are you having problems calculating times in Excel?Not exactly. The second issue you will probably come across when calculating elapsed time is that, if the result of your calculation exceeds 24 hours, Excel ignores the hours that exceed 24 and gives you a wrong answer. To avoid this, in the Format Cells dialog box (CTRL+1), type square brackets around the hour portion of the format code. e.g. [h]:mm.

These are the most common problems Excel users face when calculating elapsed times and now you have the solutions to avoid them.

Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Restrict User's Access To A Specific Worksheet Range (XL2000-XL2007)

Most worksheets use only a small section of the available area (Excel 2007: 16,384 columns x 1,048,576 rows. Excel 2003: 256 columns by 65,536 rows). Sometimes you may want to restrict the area in which a user can click in or scroll to. This can be accomplished with Excel's Protect Sheet options**, but if you want to restrict a user's access to a fixed area (e.g. B5:H50) of the worksheet without having to protect the worksheet, here is a simple solution.

Excel 2007:

Restrict the area of a spreadsheet a user can access1) If you don't have the Developer tab on your Ribbon, click the Home button, click the Excel Options button and select ' Show Developer tab in the Ribbon' and click OK;
2) In the Controls group in the Developer tab on the Ribbon, click the Properties option;
3) In the Scroll Area field, enter the range to which you want to restrict the users movement (e.g. B5:H50);
4) Click the X on the top right corner of the Properties dialog to close it.

Excel 2003:

1) From the View menu select Toolbars and click Control Toolbox;
2) On the Control Toolbox toolbar click the Properties button (2nd button from left);
3) In the Scroll Area field, enter the range to which you want to restrict the users movement (i.e. B5:H50);
4) Click the X on the top right corner of the Properties dialog to close it.

Note that you can use only one range in the ScrollArea field.

To remove the Scroll Area restriction on this worksheet, simply clear the ScrollArea field on the Worksheet Properties dialog.

CAUTION: This is not a fail-safe solution for protecting cells outside the Scroll Area. Using this method restricts a user from clicking, using the arrow keys or using the scroll bars to access cells outside this range, however, the user can access and edit areas outside this Scroll Area by entering a cell or range address in the Name box.

** Excel 2007: Review, Protect Sheet. Excel 2000-2003: Tools, Protection, Protect Sheet.

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

Easily Move Paragraphs In Microsoft Word
I don't use Microsoft Word a whole lot, but when I do I have a few tricks up my sleeve to make it easier to work with.
In the past, whenever I needed to rearrange paragraphs in my documents, I would highlight each paragraph and drag it, using the mouse, to its new location. The tricky part for me was getting it to go exactly where I wanted. If I wasn't paying attention to where the insertion bar was pointing, I would sometimes insert the paragraph in the middle of a another paragraph. That could get messy.
Dragging a paragraph from one page to another using the mouse is no fun either.
Then I learned a simple little trick that allowed me to easily and safely move paragraphs.
When you want to move a paragraph, click anywhere in the paragraph, hold down the SHIFT and ALT keys and use the up or down arrow keys to move it. Your paragraph magically jumps to precisely where you direct it to.
Keyboard shortcut to easily move paragraphs in Microsoft Word

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