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.
Hi fellow 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.
Birthday 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.
If you want to send me an email...
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.
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.
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.
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.
Step1: 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;
Step 2: Add a button to your toolbar and assign the macro:
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.
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.
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.
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.
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.
On Error Resume Next
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
On Error Goto 0
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.
Show Leading Zeros (XL2003/XL2007)
A question I am asked often is, "How can I enter numbers in Excel with leading zeros?"
If you just need to enter an occasional number with leading zeros you can simply type an apostrophe before the number (e.g. '00123).
Option 2 - The Custom Number Format Method
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
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.
Option 3 - The Text Format Method
A third way to enter numbers with leading zeros is to format the range of cells as Text.
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.
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.
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
There's absolutely no risk to you.
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.
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