Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: June 16, 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)About 6 months ago I crossed paths with another Excel 'addict' on the Internet. He reminded me of myself because he is someone who is passionate about Excel and enjoys sharing his knowledge with others. His name is Purna Duggirala and he is the CEO (Chief Excel Officer) of Chandoo.org. In a short time Purna (most people still think his name is Chandoo) has become a force to be reckoned with in the online world of Excel (especially charting).

A few months ago, Purna made a life-changing decision and quit his 'real' job and turned his online passion into a full-time business. (I really admire him for that. I'm even a little jealous) And from what I have seen since, that was a very smart decision.

In those few months Purna has started an online-video Excel training program called Excel School. Already he has had one group of students go through the program (registration for the current session closes on June 21) and, if the comments are any indication, it has been very well received. I was given access to the program for my review.

I have viewed several of the Excel School videos and have found that Purna covers the topics in detail and his lessons very clear and easy to understand. Excel School includes more than 20 hours of videos
in a structured program covering 11 topics over a period of 12 weeks. Each topic contains several videos and sample workbooks that you can download and practice on.

Is Excel School for you?

I don't know...but if you are someone who is willing to invest a little money for the much greater return of increased productivity (and all the benefits that come from it),
I think it's worth checking out.

Online training videos for Microsoft ExcelClick here if you want to find out more details about exactly what Excel School offers and if it's something that you could benefit from. And PLEASE, feel free to let me know what you think about his program.

(DISCLAIMER: if you register for Excel School through the links above, I will receive a small commission. If you've known me for a while, you know I wouldn't recommend anything I didn't fully support. If you don't know me well, don't listen to my recommendation - just check it out and decide for yourself).

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) Identify Your Input Cells (XL2000-XL2007)
    2) Create Custom Default Worksheet And Workbook Templates  (XL2003/XL2007)
    3) How To Drag and Drop Files To Open Them

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

    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:

    "Our greatest glory is not in never failing, but in rising up every time we fail." -- Ralph Waldo Emerson

    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

    Identify Your Input Cells (XL2000-XL2007)

    If you create workbooks for other users (even the ones for yourself), to make it clear which cells in your worksheet are input cells (as opposed to formulas and static text) adopt a standard font (or fill) color so they are easily identifiable.

    Eighteen years ago, I began using blue as the standard font color for input cells and almost everyone in our company has adopted this. Now, it's easy to identify input cells in most company worksheets.

    Note that if you use a fill color for your cells rather than a font color, this may result in undesirable results with your printed reports.

    Adopt a specific color to easily identify your worksheet input cells

    Microsoft Excel Tip #2

    Create Custom Default Worksheet And Workbook Templates  (XL2003/XL2007)

    Are you constantly having to change the settings for your worksheets, such as margins, font size, page headers, etc... every time you create a new workbook or insert an new worksheet? Most Excel users assume that's just the way it is. Well, I'm here to tell you those days are over.

    I'm going to show you how to create templates containing your own personalized settings that will be applied to all new workbooks and worksheets.

    When you open a new workbook, Excel uses a workbook template with default settings such as three worksheets, column widths of 8.43, no headers and footers, default print margins, etc... When you insert a sheet into a workbook, Excel uses a worksheet template with similar default settings. In Excel 2007 the workbook template name is BOOK.XLTX and the worksheet template name is SHEET.XLTX. In Excel 2003 and earlier versions, the workbook
    template name is  BOOK.XLT and the worksheet template name is SHEET.XLT.

    Here's how to create your own personalized templates so that every new workbook and worksheet will have these settings.

    1) First, do a search on your computer for the XLSTART folder. This is where you will store your custom workbook and worksheet templates.

    The location of the XLSTART folder may be either...
    C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART
    or C:\Program Files\Microsoft Office\Office##\XLSTART

    Make a note of this location.

    2) Open a blank workbook.

    3) Delete all sheet tabs except for one;

    4) If you want to change the default formatting for worksheet cells, such as the font and number formats, in Excel 2007, click the Cell Styles command in the Styles group on the Home tab. Then right click the Normal style and choose Modify. To change the default worksheet cells formatting in Excel 2003, from the Format menu select Style and click the Modify button.

    5) Next select your preferred print settings in PageSetup. In Excel 2007, click the Page Layout tab. In Excel 2003 and earlier, click File, Page Setup. Make all the changes you want your default worksheets to have (i.e. margins, headers/footers, etc...);

    6) Click cell A1 to make it the active cell for each new sheet;

    7) Now we need to save the template to the XLSTART folder. In Excel 2003, from the File menu select Save As. In the Save as Type dropdown on the bottom of the dialog box, select Template (*.xlt) and browse to the XLSTART folder you located in Step 1 above. Change the suggested name to SHEET.XLT. This will be the default sheet used when you Insert a worksheet into a workbook. In Excel 2007, click the Office Button, Save As, Excel Workbook. In the Save as Type dropdown on the bottom of the dialog box, select Excel Template (*.xltx) and browse to the XLSTART folder you located in Step 1 above. Change the suggested name to SHEET.XLTX. This will be the default sheet used when you Insert a worksheet into a workbook.

    8) If you prefer your new workbooks to have more than one sheet, copy the current sheet by holding down the CTRL key and dragging the sheet tab to the right. Rename the new sheet tabs to Sheet 2, Sheet 3, etc...;

    9) Now we will save this same personalized workbook as our default workbook template just by saving it with a different file name. To do this, simply repeat Step 7 but this time save the workbook with a filename of BOOK.XLTX if you are using Excel 2007 and BOOK.XLT if you are using Excel 2003 or earlier

    Personally, I prefer my default workbook to have only one sheet rather than the default three sheets Excel offers, as I find in most cases I never use the extra two sheets. If I do need additional sheets in my workbook, it's easy to insert them using the Insert Worksheet button I have added to my toolbar. (Alternatively you can use Shift+F11 to insert a new sheet.)

    To add the Insert Worksheet button to your
    Excel 2003 or earlier toolbars:

    1) From the View menu select Toolbars, Customize;

    2) On the Commands tab, select Insert from the Categories box;

    3) On the right side in the Commands box you will see a Worksheet button;

    4) Using the left mouse button, drag this Worksheet button and drop it next to the New workbook button on your toolbar;

    5) Close the Customize dialog. 

    Add the Insert Worksheet button to your toolbars

    If you are using Excel 2007, you can quickly insert a new sheet by clicking the Insert Worksheet command (to the right of the sheet tabs). However, if you have many sheet tabs in your workbook, the Insert Worksheet command may not be visible. So I recommend that you add the Insert Worksheet command to your QAT. (Alternatively you can use Shift+F11 to insert a new sheet.)

    To add the Insert Worksheet Command to your Excel 2007 QAT:

    1) Right-click the QAT and choose Customize Quick Access Toolbar;

    2) In the 'Choose commands from' dropdown, select All Commands;

    3) Scroll down and select the Insert Worksheet command and click the Add>> button;

    4) If you don't already have the New Workbook command on your QAT you can add that one also. Note that the command name in the list is New, not New Workbook.

    Add an Insert Worksheet button to your Quick Access Toolbar

    Now that you have done all of that, whenever you open a new workbook or insert a new worksheet it will always contain your preferred settings.

    If you chose to have only one sheet in each new workbook, you can easily insert additional sheets whenever needed by clicking on the Insert Worksheet button.

    Remember that if you ever get a new computer or work on multiple computers, you can copy these two files (BOOK.XLTX & SHEET.XLTX) so you won't have to recreate them.

    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

    How To Drag and Drop Files To Open Them

    Do you know that most Windows programs allow you to open files by dragging and dropping them into the program's window. This little trick comes in handy when you want to open a particular file in a program other than the default program. For example, I use Windows XP Picture and Fax Viewer (the default) to view my digital photos, but when I want to edit them I use the Irfanview (http://www.irfanview.com) program.

    I first open the program (less than full screen) and then drag files (from Explorer, the Desktop, or wherever) and drop them into the program's window. If the program's window is hidden behind another window, simply drag the file to the program's button on the Taskbar, hold it there for a second or two until the program window pops on top of the other windows, then drop the file in this window.

    Open files by dragging them to an open window

    You also can use this tip to choose which program you want to open a text file in (including Excel) or open a HTML file in a text editor rather than your browser. There are many uses for this trick as you'll soon discover.

    This is one of those 'you've-got-to-do-it-to-appreciate-it' tips.

    I hope you find it as useful as I have.

    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