AddThis Social Bookmark Button

Print

Useful Tips for Excel 2000

by Jinjer Simon
06/13/2001

On the surface, Microsoft Excel gives the appearance of a straightforward, user-friendly program. But it doesn't take long to realize it is much more complex than it appears. Here are ten useful tips for reducing the complexity of Excel.

  1. Instead of moving down a column when I press Enter, I want to move across a row.

    Excel comes with a feature called "AutoEntry" that allows you to quickly add values to a worksheet by simply typing a value and pressing Enter to move to the next cell. By default, the AutoEntry option moves down a column to the cell below the previously edited cell. If you want to change the direction of the AutoEntry you can select Tools -> Options -$gt; Edit. Make sure the Move selection after Enter checkbox is selected and select the desired direction from the list box. There are four options available: Down, Right, Up, and Left.

  2. I want all the workbooks I create to have the same basic settings: default font, number of worksheets, specific headers and footers, etc.

    By default, Excel always creates a new workbook that contains three blank worksheets. These default settings are built into Excel, but they can be overwritten by simply creating a template file named book.xlt and placing it in the XLStart folder, or one of the folders specified below. If this template file exists, whenever you create a new workbook it is used as the default settings for the workbook. Whatever settings are in the template file will be placed in the new workbook you create. Excel looks for template files in the following locations:

    • C:\Windows\Profiles\user_name\Application Data\Microsoft\Templates

    • The XLStart folder, typically located in C:\Windows\Profiles\user_name\Application Data\Microsoft\Excel\XLStart

    • The location you specified at the Tools -> Options -> General tab.

  3. I want the same workbooks to open each time I run Excel.

    If you have some standard workbooks to open each time you run Excel, you can do so by saving your workspace in the XLStart folder. To do so, open the desired workbooks and select File -> Save Workspace to save them in the XLStart folder. Anything that Excel finds in this location will open each time you run Excel.

  4. I need to add a name, such as Smith & Jones, to the header of the worksheet, but it does not work correctly.

    Related Reading

    Excel 2000 in a Nutshell
    By Jinjer Simon

    When working with headers and footers, Excel uses ampersands (&) to indicate codes. For example, &([Page} indicates that you want to insert a page number in that location. If you want an ampersand to appear in the actual text you need to use double ampersands (&&).

  5. I want to make sure no one can add an extra row or column to the worksheet.

    The actual size of an Excel worksheet is fixed. Every worksheet has exactly 256 columns and 65,536 rows. Whenever you insert a row into the worksheet Excel just takes a row from the end of the worksheet and places it in the specified location. The same thing happens with a column insertion. If you do not want rows and columns inserted, you simply need to place a value in the last row and column of the worksheet (IV65536). When this cell contains a value Excel thinks the worksheet is full and no one can insert rows or columns into the worksheet.

  6. Excel keeps stopping on the same terms when I spell-check the worksheet.

    If you have checked the spelling in a worksheet, you have probably noticed terms that are not in the dictionary. If you have specific words and terms that you use frequently, you may want to create a custom dictionary. You can add the words to your custom dictionary each time Excel finds one, or you can manually add the words to the custom dictionary using a text-editing program, such as Notepad. Excel comes with a custom dictionary called custom.dic. It is located here:

    Root\OS\Profiles\User_name\Application Data\Microsoft\Proof

    If you manually create or modify the custom dictionary make sure each entry is separated by a hard page break. This is done by pressing the Enter key between each entry.

  7. I have a series of macros that I want to distribute to other users, but I don't want them to be able to modify the macros.

    If you have a worksheet that contains macros you want to make available to others, you may want to consider creating an Add-in. When you save a workbook as an Add-in, only the macros are saved, but they are hidden, so the code cannot be viewed or modified. To create an Add-in select File -> Save As and then select the Microsoft Excel Add-In (.xla) option. After an Add-in has been created, it can be added to a worksheet by selecting Tools -> Add-in.

  8. I am not getting the intended results when I enter dates into my worksheet.

    As you are probably aware by now, the year 2000 created a lot of confusion when it came to dealing with dates. As with everything else, the best method for dealing with dates is to specify a four-digit year. However, if you don't want to do that, Excel actually has a very straightforward method for dealing with dates:

    • All two-digit years between 00 and 29 are interpreted as the years 2000 through 2029.

    • All two-digit years between 30 and 99 are interpreted as the years 1930 through 1999.

  9. I am unable to undo all of my changes.

    The Edit -> Undo command can be a lifesaver when you need to reverse changes you've made to a worksheet. The command is available to undo changes made during the current session. There is one issue to keep in mind with this command. Each time Excel performs an AutoSave the Undo list is reset. Therefore, if you are frequently performing an AutoSave you may have some difficulty if you need to undo edits. I would recommend setting the AutoSave option at five to ten minute intervals. If you need to undo changes that were made prior to an AutoSave the only option is to close the workbook without saving. This will revert back to the original version, but all changes made during the session will be lost.

  10. I want to create macros that can be used by all workbooks.

    If you have macros that you want to be able to use with all your workbooks, you should store them in the Personal Macro Workbook. This workbook, named personal.xls, is created when you store the first macro in it. Any macros stored in the file are available to all Excel workbooks. Excel automatically opens this file at startup. If you add any new macros to it, you will be asked to save the workbook before closing Excel. Don't worry that you can't see it, Excel opens it up in a hidden window so that it does not get in your way.

Jinjer Simon has been actively involved in the computer industry as a Web site developer, end-user trainer, and developer of online documentation for the past 15 years. She has written numerous books, including Visual Basic Scripting SuperBible for Waite Group Press and Windows CE for Dummies for IDG Press.

O'Reilly & Associates published (August 2000) Excel 2000 in a Nutshell.