AWeber - Email Marketing Made Easy Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: June 9, 2010

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

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

Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes (The Excel Addict)This week I've got more tips that I hope you'll like. Try them and let me know what you think.

Once again, I want to thank you for sharing my newsletter. There are almost 500 new Excel users signing up for this newsletter every month and I know a lot of them are the result of referrals. I know this because I get dozens of emails telling me how happy they are that someone told them about this newsletter. So, I really do appreciate your help in sharing my newsletter with others.

To your SUCCESS !
Francis Hayes (The Excel Addict)

If you want to send me an email...

  • Please type 'The Excel Addict' in the Subject field of your emails.

  • If you reply to this email, please delete the newsletter content before Sending.

  • Remove background color in Outlook 2007 - Options, Page Color, No Color.

  • 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 Input/Display Minutes That Exceed An Hour (XL2000-XL2007)
    2) Sorting Data Using Absolute Values (XL2000-XL2007)
    3) Limit Your Internet Search To A Specific Website

    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) Plan Your Garden With Excel's Drawing Tools (XL2000-XL2007)
    2) Count Cells That Include Specific Text (XL2000-XL2007)
    3) Access Right-Click Menus Without A Mouse

    You can still find last week's newsletter here.

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


    "Seek always to do some good, somewhere... Even if it's a little thing, so something for those that need help, something for which you get no pay but the privilege of doing it." --Albert Schweitzer

    CAUTION: Make sure you save a copy of your spreadsheet before trying these tips,
    just in
    case you make an error or the procedure doesn't produce the results you want.

    Microsoft Excel Tip #1

    How To Input/Display Minutes That Exceed An Hour (XL2000-XL2007)

    If you need to record elapsed times such as race times, you may want to enter just minutes and seconds, not hours. To enter minutes that exceed an hour, there's an important concept you will need to know. 

    When you enter a time, Excel interprets the number before the first colon as the hours, the number after the first colon as minutes and, if there is a second colon and a number (optional), Excel will treat that as seconds.

    So, with that in mind, if you enter a value 123:45 in a cell, Excel will interpret that as 123 hours and 45 minutes. For entering minutes that exceed an hour, the trick you need to know is to enter times with a leading zero for hours. So, in the previous example, you would enter 0:123:45 and Excel will correctly interpret this as 0 hours, 123 minutes and 45 seconds. 

    Input minutes in an Excel worksheet greater than an hour

    The second part of this exercise is to apply the correct number formatting to your cells to display these times properly. To format the cell to display the time as minutes and seconds and prevent the minutes from rolling over at 60, use the format [m]:ss

    Press CTRL+1 and on the Number tab click Custom and type [m]:ss in the Type field. With this custom number formatting applied to your cells, you can also total the times to display just minutes and seconds.

    Enter minutes in Excel that exceed an hour

    Microsoft Excel Tip #2

    Sorting Data Using Absolute Values (XL2003/XL2007)

    I use this a lot, so I'm guessing that you may also find this tip useful.

    Whenever you sort a list of numbers that includes both positive and negative amounts, assuming that you sort in descending order, the largest positive numbers go to the top and the largest negative numbers go to the bottom. On a short list, this may not be a problem, but on a long list your largest positive and negative numbers will be far apart.

    Sort data in Excel based on absolute value

    You may find that sometimes you need your data sorted but you want to ignore the sign. For example, if you are working with variances, you may want to see all large variances grouped together, whether they are positive or negative.

    You can accomplish this by using the ABS (Absolute) function.

    1) In a blank cell, in the column immediately to the right of your data, enter the formula =ABS(D5);

    2) Replace D5 with the reference to your first amount cell;

    3) Copy that formula down the column to the bottom of your list;

    4) Now sort your data in descending order based on this column. 

    Sort data in Excel based on absolute value

    You will now have your data sorted based on absolute values showing your most significant items (positive or negative) toward the top of your list.

    101 Excel Tips revealed in this book
    Microsoft Excel 2000 - 2003

    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

    There's absolutely no risk to you.

    Non-Excel Tip

    Limit Your Internet Search To A Specific Website

    If you have poked around in the Google's Advanced Search options, you may have seen that there is an option there that allows you to return your search results from a particular website.

    Limit your Internet search to a specific website

    If this is something you do often, you may like to know that there is an even easier way to do this. In the regular Google search box, simply type search_term site:target_site>.

    Example:        oil spill

    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 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 or send a blank email to theexceladdict(AT)aweber(DOT)com

    3)  Post a link to in a company newsletter or website
    "Spreadsheets Tips From An Excel Addict" is a weekly publication of
    Copyright Francis J. Hayes All Rights Reserved.
    8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630