Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: July 7, 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, in Tip #1, I will share with you something I discovered the first week I started using Excel in 1991. It involves a macro - a very simple macro. But it provides a daily convenience I have been glad to have for the past 19 years.

I am interested in what you think about it. You may think it's a little lame but I'd like for you to use it for a while before you pass judgment.

Also, Saturday, July 10 is a big milestone for me -- it's my 50th Birthday. Wow! Saying that just doesn't feel right. I feel younger now than when I was in my 30s.

101 Excel Tips and secretsBirthday Sale: In the next couple of days I will be offering a limited-time half-price 'Birthday' special on my '101 Secrets' ebook. Truth is, I only just thought of doing this tonight, so I wasn't able to get it ready in time for this newsletter. If you haven't already purchased my ebook, watch out in the next few days for an email with a link to this half-price offer.

Keep on Excelling,
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) Goto A1 (Excel 2000 -2007)
    2) Show Leading Zeros (XL2003/XL2007)
    3) Copy Files To Your Flash Drive In A Flash

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

    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:

    "There is a fountain of youth: it is your mind, your talents, the creativity you bring to your life and the lives of people you love. When you learn to tap this source, you will truly have defeated age" 
     -- Sophia Loren


    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
     
    Goto A1 (Excel 2000 -2007) 

    I'm going to share with you the very first and simplest macro I ever created in Excel. That was 19 years ago but it is still one of my favorite customizations I have ever made to my Excel interface and is something that I use maybe hundreds of times a day.

    This is so simple that, when you see it, you may think 'That seems pretty lame!". Well, hear me out, give it a try for a while, and THEN let me know if you like it or not.

    Back in 1991, when I got Excel for the first time I found that I was constantly going back to the top of my sheet. Most often this meant scrolling up to row 1 and then scrolling left to column A. Then I discovered the CTRL+Home keyboard shortcut which was much easier. However, often meant that I had to take my hand off the mouse.

    This was in the first week I began using Excel and I had already read about macros and the macro recorder, so I decided to see if I could record a macro that would instantly take me back to the top left corner of the sheet with one click. I started up the macro recorder, clicked cell A1 and stopped the macro recorder.

    When I tested the macro, it worked perfectly. 

    I opened the VBE (Visual Basic Editor) to see the code and found that it was only one simple line of code. This was my very first macro.

    Sub GotoA1()
        Range("A1").Select
    End Sub

    I added a button to my toolbar and assigned the macro to it. I called this my GotoA1 button.

    Back then, I created a custom image for the toolbar button (In Excel 2007, you cannot create bottom images). I placed the button on the far left end of the bottom toolbar. I found this convenient because I would just point to the top left and, if cell A1 wasn't visible, I would click my GotoA1 button - it just seemed natural.

    I shared this macro and button with everyone I work with and they are all hooked. Every once in a while I hear, "Help! My GotoA1 button is missing!" if they get a new computer or somehow deleted their custom toolbar.

    To see if this is something that you would find helpful, here's what you need to do.

    Click to start recording a macro in Excel 2007Step1: Record the macro

    1) First make sure cell A1 is visible on the active sheet;

    2) If you are using Excel 2007, click the Record Macro button on the left side of your Excel Status Bar (at the bottom of the window just above the Start button if your window is maximized). For Excel 2000-2003 users, from the Tools menu select Macro, Record New Macro;

    3) In the Record Macro dialog, type GotoA1 (no spaces) in the 'Macro Name' field;

    4) Select Personal Macro Workbook from the 'Store macros in' field and click OK; By storing the macro in your Personal Macro Workbook, it will be available at all times as the PMW is always open and hidden in the background;

    5) Click cell A1 and click the Stop Recording button;

    Click button to stop recording a macro in Excel 2007 - 2003


    Step 2: Add a button to your toolbar and assign the macro:

    Excel 2007:

    1) Right click the Ribbon and select Customize Quick Access Toolbar;

    2) In the 'Choose commands from' field, click the dropdown arrow and select Macros. You should see your GotoA1 macro listed as PERSONAL.XLSB!GotoA1;

    3) Select the GotoA1 macro from the left side and click the Add>> button to place the macro on your Quick Access Toolbar;

    4) 
    Select the GotoA1 macro on the right side and click the Up arrow to move the macro to the top;

    5) Click the Modify button at the bottom of the dialog. A Modify button dialog showing 169 button icons appears. Select the Home icon (11 down, 9 across) or you can choose any other icon if you prefer. Click OK.

    Select a new icon for your Excel 2007 Quick Access Toolbar buttons

    I have to admit, the small size for the QAT buttons in Excel 2007 makes clicking the GotoA1 button a little trickier than previous versions.

    Excel 2003:

    1) From the Tools menu select Customize;

    2) Scroll down the Categories list and select Macros;

    3) From the Command list (on the right), drag the Custom Button and drop it on the left end of your bottom toolbar;

    4) Right-click on the button and select Assign Macro;

    5) Select your GotoA1 macro and click OK;

    6) Right-click the button again and select Edit Button Image (a Button Editor appears). Use the colors to draw an image for your button. Here's the one I use for my GotoA1 button.


    Click button to stop recording a macro in Excel 2007 - 2003

    Step 3: We're almost finished.

    Now that you have your macro assigned to a button on your toolbar, try it out by scrolling down and right a few pages and clicking the GotoA1 button on your toolbar.

    Also, let's take a look at the macro code again. Press ALT+F8, select your GotoA1 macro and click Step Into. then, from the Run menu, select Reset.

    Excel macro code example


    Over the years I have made a couple of modifications to my original one-line code to handle certain situations where the single-line macro fails to work properly. Please select and copy the code below and paste/replace the code in your Visual Basic Editor window.

    Sub GotoA1()
        On Error Resume Next
        Range("A1").Select
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
        On Error Goto 0
     
    End Sub

    Finally, click the Save button or File, Save
     and close the Visual Basic Editor window.

    Use this for a few days or weeks and then let me know if you like it or not.

    Do you like to Goto A1 ?


    Pull answers from your lists with these 45 LOOKUP formulas

    Microsoft Excel Tip #2
     
    Show Leading Zeros (XL2003/XL2007)

    A question I am asked often is, "How can I enter numbers in Excel with leading zeros?"

    Sometimes you may need to enter numbers that begin with zeros into a worksheet, such as fixed-length part numbers or serial numbers. By default, Excel drops the leading zeros.

    Here are a couple of options:

    Option 1 - The Apostrophe Method:

    If you just need to enter an occasional number with leading zeros you can simply type an apostrophe before the number (e.g. '00123).

    Show leading zeros in Excel using the Apostrophe method

    Option 2 - The Custom Number Format Method

    Another way is to create a custom number format to display your numbers with leading zeros. In this scenario, your numbers are displayed as fixed-length numbers with leading zeros (e.g. 00123), however, the underlying value in the cell is still the number without the zeros (e.g. 123).

    1) Select the cells containing the numbers you want to show leading zeros;

    2) in Excel 2007, on the Home tab, click on the Number Format dropdown in the Number group and choose More Number Formats at the bottom of the list. For Excel 2000-2003, from the Format menu select Cells and click the Number tab;

    3) In the Category area, select Custom;

    4) In the Type field enter zeros for the number of digits you want your numbers displayed.

    Display leading zeros in Microsoft Excel by using the Custom Number Format method

    Option 3 - The Text Format Method

    A third way to enter numbers with leading zeros is to format the range of cells as Text.

    To do this...

    1) Select the cells to be formatted;

    2) In Excel 2007, on the Home tab, click on the Number Format dropdown in the Number group and select Text from the list. In Excel 2000-2003, from the Format menu select Cells, click the Number tab, and select Text from the Category list. 

    Display leading zeros in Microsoft Excel by using the 'Text format' method







    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

    Copy Files To Your Flash Drive In A Flash

    Flash drives are so convenient. Small...easy to use...lots of memory...fast.

    Remember back in the days when we used diskettes? Oh no! Don't tell me you're still using those things!!

    As convenient as you may think your flash drive is, I'm going to share a secret with you that will make it way more convenient.

    The fastest and easiest way to copy files to your flash drive is to right-click on the file(s) in Windows Explorer and, from the popup menu, select Send To, you'll see a shortcut to all drives, including your memory stick. Select your flash drive and you're done.

    If, instead of 'copying' your files, you want to 'move' them (i.e. cut and paste), you simply hold down the SHIFT key and the files will be copied to your flash drive and removed from the original folder all in one quick step.

    Quickly copy or move files to your flash drive
     

    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