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.
Hi fellow Excel Addict,
Thanks 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.
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.
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.
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.
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!
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.
Restrict User's Access To A Specific Worksheet Range (XL2000-XL2007)
1) From the
and click Control
you can use
only one range in the ScrollArea field.
** Excel 2007: Review, Protect Sheet. Excel 2000-2003: Tools, Protection, Protect Sheet.
If you're not 100% satisfied, simply request a full refund from ClickBank.com
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.
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