Office 97 tips

• A shortcut to the Format Cells dialog box
• Hiding the distribution list In an E-mail message
• Making formatted totals appear mathematically correct
• A VBA procedure for prompting users to select an Excel range
• Sending E-mail to a contact
• Insert name references in Excel formulas
• Using the Spike to move multiple text selections
• Replace Clipit
• Auto Quick Info feature
• Drop caps
• Changing the default number of worksheets in Excel

Formatting worksheets is something you do often. To apply formatting to a range, you normally issue the Format/Cells... command to display the Format Cells dialog box. A quicker way to display the Format Cells dialog box is to simply press [Ctrl]1. Once you use this shortcut a few times, you won't ever go back to the menu.

Suppose you want to send an E-mail message to several people, but you don't want the recipients to see who else is in the distribution list. Simply enter the E-mail addresses in the Bcc field instead of the To or Cc fields at the top of the E-mail form. If you don't see the Bcc field, you can display it by issuing the View/Bcc command.

Formats that display rounded values can make totals look mathematically incorrect. For instance, assign the Comma Style format with zero decimal places to the worksheet range B1:B6. Enter in cells B1:B5 the values 1.11, 2.22, 3.33, 4.44, and 5.55, respectively--which will appear as 1, 2, 3, 4, and 6. Then enter in cell B6 the function =SUM(B1:B5). The formatted result of the function will appear as 17, which doesn't seem correct if you compare it to the total of the displayed values 1, 2, 3, 4, and 6, the sum of which is 16. This behavior occurs because the SUM() function sums the values actually in the cells, rather than the formatted values it displays.

You might include a statement in your printed report mentioning that totals show minor errors due to rounding. Or if you'd rather make the totals appear to be mathematically correct, you can use the array formula {=SUM(ROUND(B1:B5,0))}, which returns the total of the formatted values--in this case, 16. (Remember, to enter an array formula, you don't type the
curly braces at the beginning and end of the formula; and you enter it by pressing the [Ctrl][Shift] keys.)

When developing Excel VBA applications, you might need to prompt the user for a range. To do this, you can use the following VBA procedure:

Sub SelectRange()
    Dim UserRange As Range
    Set UserRange = Application.InputBox( _
        prompt:="Please input or select a range", Type:=8)
    UserRange.Font.Bold = True
End Sub

It displays a dialog box that contains a text box in which the user can type the address of a range. The user can also highlight that range in the worksheet, and the text box will automatically display the address of the highlighted range. When the user clicks OK, the procedure assigns the range object to the variable UserRange. At that point, you can use that variable to modify the range the user selected. For instance, the last statement in the procedure assigns bold formatting to the selected range.

A quick way to send E-mail to a contact is to drag the contact's name from the Contacts list onto the Inbox icon in the Outlook bar. Outlook will open a new mail message form with the To field already containing the contact's E-mail address. You can use this technique to send a message to multiple recipients as well. Just select the desired contact names--you can select multiple non-adjacent names by holding down the [Ctrl] key as you click on the names--and drag the selection onto the Inbox icon.

If you use many range names in your Excel spreadsheets, it can become difficult remembering exactly how each name is spelled. To avoid brain strain when you need to enter a name reference in a function or formula, you can use the Paste Name dialog box, which displays a list of all the names in the active workbook and inserts the selected name in your function or formula.

For instance, suppose you want to sum all the values in the range named JanQ4. Begin by typing =SUM(, then press [F3] to display the Paste Name dialog box. Choose JanQ4 from the list of names, click OK, and Excel will insert that name in the function. To complete the SUM() function, type a closing parenthesis and press [Enter].

Have you ever needed to relocate several scattered portions of text in a document? If so, you may have been thwarted by the single-item storage capacity of the Clipboard. When you click the Cut button to place the first selection on the Clipboard, then cut a second selection of text, Word will jettison the first selection. Of course, you can move the text one piece at a time, but that's not terribly efficient. Here's a more reasonable alternative: Use the Spike to move all the text at the same time.

To do this, select each portion of text you want to move and press [Ctrl][F3] to place it on the Spike. Then, position the insertion point marker in the new spot and press [Shift][Ctrl][F3]. Word will clear the contents of the Spike and insert your text selections in the order in which you spiked them.

Tired of Clipit, the paperclip guy (but still value having the Office Assistant)? Send him on a long vacation and replace him with any other number of optional characters. From the Main Menu, click Help and then Options. Click the Gallery tab. Using the Next button, page through your options until you find a new assistant.

Use Access 97's new Auto Quick Info to help you remember the proper syntax and functions arguments. Access 97 will automatically display the function with all its arguments. Simply type the function name followed by a space or open parenthesis.

Use the Drop Cap option in Word to create large capital letters for the first letter in a paragraph (Think back to children's fairy tales where almost every one began with "O" on "Once upon a time" dropping ornately into the text below). In business, you can make a powerful statement with a bold capital letter capturing the readers attention. From the Format menu, click on Drop Cap. You will have the option of choosing any font available and also if the capital letter is to be hanging into the text or in the
margin.

You can change the default number of worksheets in a workbook in Excel. >From the Tools menu select Options and then click the General tab. Use the sheets in New Workbook text box to specify the new default number of sheets. The number of sheets can range from 1 to 255.