Learn Excel from Mr. Excel

Have you ever been bugged that when you choose File or Edit from the menu and you only get a partial list of all the options that are available? There is a way to get a full list of all the options – every time – and without waiting for the Microsoft delay to kick in. You need to turn off adaptive menus. Here is how to do it:

• Go to the Tools menu item.
• Select Customize.
• In the Customize dialog, choose the Options tab.

Put a check in the box for Always show full menus.

Because Word and Excel share some common code, when you change this setting in one product, it will change the setting in both products.

Have you ever thought about adding a comment to the end of a formula so that you can remember what it does? While you could do this with a cell comment, the cell comment is not visible in the formula bar and the cell comment red triangle indicator invites the spreadsheet user to look at your comments.

For example, you might have to calculate the area of a room. Area is Length times width.

While the words Area=Length times width are easy to understand, the resulting Excel formula of =A2*B2 is not as intuitive. There is a clever way to add a comment to the end of the formula. It is an old, undocumented function called the N() function. Add the N() function to the end of your formula. Remember to put the comment inside quotes inside the parentheses. Example: _=A2*B2+N(Area is Length times Width)_. When you select the cell, the comment and the rest of the formula show up in the formula bar.

Would you like to have every new workbook or worksheet that you create set up with your favorite formats and customizations? It is possible to do. Follow these steps.

• Create a new blank folder on your hard drive.

• From Excel, choose Tools – Options – General. In Excel 2000 and earlier, look for the textbox for Alternate Startup Location. In Excel 2002 and newer, this textbox is called As startup, open all files in. Enter the path to your new folder in this textbox.

• Open a new blank workbook. Customize this workbook to include your favorite settings. You can change the Page Setup settings such as margins, header, footer, and perhaps Fit to 1 page wide by (blank) pages tall. Change cell formatting such as Vertical Alignment to Top or to turn off Wrap on all cells. You can change the zoom settings. Change the number of sheet tabs.

• Use File – Save As. Change the Save Files as Type to an Excel template. Navigate to your new folder and save the file as book.xlt.

The above steps will control the look of every new workbook that you create. You might also want to use the same settings for new worksheets inserted with Insert – Worksheet. Use the file from above, but follow these additional steps:

• Reduce the number of worksheets to just one worksheet.
• Use File – Save As. Change the Save Files as Type to Template. Navigate to the alternate startup folder and save the file as sheet.xlt.

Both of the names, Sheet.xlt and Book.xlt are system names. Excel will examine your startup folder and the alternate startup folder to see if these files are available when you ask for a new workbook.