Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: June 30, 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)Happy Canada Day (July 1st) to all my fellow Canadians! 

My American neighbours are also celebrating a birthday on Sunday, July 4th.

With all the partying going on, I hope everyone has a safe and happy weekend.

Francis Hayes (The Excel Addict)

If you need to change your subscriber information, please look for this link at the very bottom of this newsletter.
Spreadsheet Tips From An 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) Add Color To Your Sheet Tabs (XL2002-2007)
    2) Quickly Find All External Links In Your Worksheet (Excel 2003-2007)
    3) Need A Quick Launch Calculator?

    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) Quickly Identify Your Conditionally Formatted Cells (XL2003/XL2007)
    2) Show Degree Symbols In Excel (XL2000-XL2007)
    3) Save Multiple Outlook Messages As A Single Text File

    You can still find last week's newsletter here.

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

    Quote of the Week:

    "You're off to Great Places! Today is your day! Your mountain is waiting, So... get on your way!" -- Dr. Seuss (Oh, the Places You'll Go!) 

    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

    Add Color To Your Sheet Tabs (XL2002-2007) 

    When you are working with workbooks containing a lot of sheet tabs, it may be useful to distinguish related sheets by color coding the sheet tabs (for versions of Excel prior to XL2002, the option to color sheet tabs isn't available).

    1) Select the sheet tabs you want to apply a color to. If you want to select multiple tabs, hold down the CTRL key and click each tab;

    2) Right-click on one of the highlighted tabs and select Tab Color from the pop-up menu;

    3) Choose a color.

    I am a little disappointed with Excel 2007's 'washed out' colors. as they often make the colored sheet tabs difficult to read.

    Color code sheet tabs for Excel 2003-2007

    CAUTION: Make sure you ungroup multiple tabs before making any other changes as this could cause you to really mess things up. (Right-click the any sheet tab and select Ungroup Sheets)

    Pull answers from your lists with these 45 LOOKUP formulas

    Microsoft Excel Tip #2
    Quickly Find All External Links In Your Worksheet (Excel 2003-2007)

    Surprisingly, Excel doesn't have a built-in way to find links to external workbooks. Fortunately, there is another way to do this ... and it's pretty fast and easy.

    Since all links to closed workbooks enclose the source workbook's name in brackets [] (e.g. =[Inventory.xls]CYR!H25 ) you simply need to do a search for all cells that contain brackets.

    Here's how to find external links on a single worksheet:

    A trick to quickly and easily find all external links in your workbook1.Close all other workbooks;

    2.Press CTRL+F to bring up the Find dialog;

    3.Click the Options>> button;

    4.In the Find What field, type a single opening bracket [;

    5.In the Look In field, select Formulas;

    7.Click the Find All button;

    8.You will see at the bottom of the dialog that all of the 'found' cells are listed. Only one is highlighted. Press CTRL+A to highlight the entire list. You should also notice that all of the 'found' cells on the sheet have been selected;

    9) Click the Close button;

    10) With all of the 'found' cells still selected, add a background color (e.g. yellow) to them. Now you can click away from the selected cells and still easily identify all of the links.

    Quickly and easily find all links and color them for easy identification

    Find All Links In The Entire Workbook

    Note that in the Within field on the Find dialog there is an option to search the entire Workbook. If you choose this option, you will find that when you press CTRL+A to select all of the found items, only the items on the first sheet are highlighted/selected. To highlight/select items on another sheet, click one of the items listed for that sheet and then press CTRL+A.

    Also note: This method may not find all links in your workbook since links can be hidden in names and worksheet objects such as text boxes and charts.

    101 Excel Tips revealed in this book
    Microsoft Excel 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 ClickBank.com

    There's absolutely no risk to you.

    Non-Excel Tip

    Need A Quick Launch Calculator?

    If you need to do some quick calculations while you're working on your computer but don't have a calculator close by, you can make one readily available by adding a shortcut to the Windows Calculator to your Quick Launch Toolbar. It's so easy!

    The Quick Launch Toolbar

    If the Quick Launch Toolbar (small icons to the right of the Start button) isn't visible, here's how to install it.

    1) Right click a blank area of your Taskbar and click to remove the check next to Lock the Taskbar;

    2) Right click a blank area of your Taskbar again, point to Toolbars and click Quick Launch..

    You should now see the Quick Launch Toolbar to the right of your Taskbar. If you see >> immediately to the right of the Quick Launch Toolbar, drag the small gray bar to the right until all icons are visible. You can now re-Lock the Taskbar.

    Add Windows Calculator to your Quick Launch Toolbar

    To add the calculator to your Quick Launch Toolbar...

    Add Windows Calculator to your Quick Launch Toolbar1) Click the Start button;

    2) Click All Programs and point to Accessories. You should see Calculator in the list of programs;

    3) Using your right mouse button, drag the Calculator icon and drop it on your Quick Access Toolbar. Click Copy Here to the message that pops up.

    Now you have one-click access to a calculator no matter which program you are working with.

    Calculator Tips

  • To convert the calculator to scientific, click View, Scientific.
  • No need to point and click on those small calculator buttons. You can use your keyboard to enter numbers and operators into the calculator.

  • 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