Microsoft® Excel 97 Quick Reference
After you have created a new workbook and entered data into it, you will eventually want to edit cell data or modify the structure of the workbook itself. You can edit the contents of a cell by retyping data into the cell or by editing the existing cell contents. To save time, you can copy and paste existing cell data rather than having to reenter the data. Excel also enables you to easily find and replace worksheet data, spell check your worksheet, and protect your data from unauthorized changes.
You can modify the structure of the workbook by adjusting column widths and row heights, and inserting or deleting cells, rows, columns, or worksheets. This section covers these features and more of the most-used means of editing your workbooks and worksheets.
You can improve the appearance of your worksheet by adjusting the width of columns to fit the data contained in those columns. Adjusting column widths can also help you fit more data on-screen or in a printout. If a column is not wide enough to display a number, date, or time, Excel displays #### characters in the cell. Excel also enables you to hide confidential data within a column. (See also "Alignment: Shrinking Text to Fit in a Cell" in the "Formatting" section.)
To size multiple columns, select the columns, and then drag the right boundary of one of the selected columns. To size all columns in the worksheet, click the Select All button (the gray rectangle just left of the column letters), drag the right boundary of any column to the desired width, and then release the mouse button. (See "Selecting: Rows and Columns.")
TIP: To automatically fit the column to its widest entry, double-click the right boundary of the column letter.
The height of a row automatically adjusts to the largest font size applied to a cell in a row unless you manually change the height of a row. Excel enables you to manually adjust the height of a row when you need additional space between rows of data. If you adjust the height of a row so that it is too small to display the font, the tops of the characters are cut off at the boundary of the cell(s) above.
To size multiple rows, select the rows, then drag the bottom boundary of one of the selected rows. To size all rows in the worksheet, click the Select All button (the gray rectangle just above the row numbers), drag the bottom boundary of any row to the desired height, and then release the mouse button. (See "Selecting: Rows and Columns.")
TIP: To automatically make the row height fit the contents of the row, double-click the bottom boundary of the row number.
In Excel, you can copy cell data within a worksheet, between worksheets, between workbooks, or even between Excel and another application. (See also "Copying: Data Between Applications" and "Moving: Cell Data.")
TIP: To quickly copy data to another location in the same worksheet, highlight the cell or range you want to copy. Press and hold down the Ctrl key, and drag the border around the selected range to the desired location. Release the mouse button and the Ctrl key.
(See also "Pasting: Multiple Copies" in this section and "Copying Formats with the Format Painter" in the "Formatting" section.)
You can use the Clipboard to easily copy and paste data between Windows applications. The Clipboard is a temporary storage area for cut or copied items. When you cut or copy text or objects in one of the programs, Windows places that item on the Clipboard. You can then paste that item to the same worksheet, a different worksheet, or a different program. You can continue to paste the same item over and over again until you cut or copy another item.
(See also "Pasting: Formats, Values, or Transposed Data" in this section and "Linking: Data Between Applications" in the "Linking and Embedding" section.)
You can copy all the contents and formatting of an existing worksheet to a new worksheet--either in the same workbook or to another workbook. This feature is useful if you frequently create similar worksheets, or if you need to start a new monthly or quarterly worksheet using the same format as an existing worksheet. (See also "Moving: Worksheets.")
TIP: To quickly create a copy of a worksheet in the same workbook, select the sheet tab for the worksheet you want to copy. Then press and hold down the Ctrl key and drag a copy of the active sheet tab forward or backward, to where you want the new worksheet to appear.
Although deleting data is a simple task, there are a few concepts with which you should become familiar. You can delete the data contained in worksheet cells, or you can delete the actual cells themselves. Deleting cell contents is sometimes referred to as clearing cell contents. When you clear cell contents, you have the option of clearing just the data in the cell, the cell formatting, cell comments, or all of these.
NOTE: To delete cell contents (as described in this section), use the Edit, Clear command or the Delete key. If you want to delete actual cells or entire rows or columns (including all data contained in them), however, you would use the Edit, Delete command. (See "Inserting and Deleting: Cells and Ranges.")
Choose Edit, Undo Clear to return the data range to its original state.
TIP: To quickly clear just the contents from a range, select the range and then press Delete.
If you no longer need a particular worksheet in a workbook, you can delete the entire worksheet. Excel prompts you for confirmation before deleting the worksheet. Exercise caution when using this command--if you delete a worksheet by accident, you cannot use Undo to restore the worksheet. (See also "Workbooks: Deleting a Workbook" in the "File Management" section.)
When you need to edit a cell entry, you can either edit the text in the cell itself or in the formula bar. For worksheets built like data-entry forms, the users often expect to type and edit directly in a cell.
In addition to editing data directly in the cell, you also can edit data from within the formula bar. If you frequently use the formula bar, you may prefer to edit data in the formula bar.
TIP: To replace part of a cell entry, click and drag the I-beam pointer to highlight the characters you want to replace. Then, type the new data and press Enter.
When your worksheets get large and are filled with data, you may have trouble finding specific information in them. The Find command enables you to jump to any piece of information, such as labels, formulas, values, or comments in your worksheet. (See also "Replacing Worksheet Data.")
NOTE: The Find command performs the search in the current worksheet only. To search in other worksheets in a workbook, you must select them individually, and then choose Edit, Find.
When you create a group of similar worksheets in a workbook (such as in a sales consolidation), you can group the worksheets together before you enter data or format the worksheets. This can save you a great deal of editing time because you can apply the same operations to the entire group of worksheets at once. (See "Selecting: Worksheets" in the "Getting Started" section before you complete this task.)
(See also "Formatting: Multiple Worksheets" in the "Formatting" section.)
When you generate a worksheet for multiple users, you may not want to print all the information that you enter. You can hide columns temporarily so that they do not print or appear on-screen. In addition to hiding columns, you also can temporarily hide entire rows of data in the worksheet if you don't want them to print or appear on-screen. Hiding columns or rows doesn't affect the results of formulas that refer to hidden cells.
TIP: If you often hide and redisplay the same columns or rows in your worksheets, you should create a custom view using the View, Custom Views command. This command enables you to assign a name to the current worksheet view. You can then use the View, Custom Views command to select the name of the view you want to display.
(See also "Hiding: Displaying Hidden Columns and Rows.")
If one or more column letters or row numbers appear to be missing in the worksheet frame, this is because the columns or rows have been hidden from view. You can easily redisplay hidden columns or rows if you need to see or edit data in them. (See "Hiding: Columns and Rows" before you complete this task.)
You can insert new blank cells or delete existing cells anywhere in the worksheet. When you insert cells, the existing cells move in the direction you specify to make room for the new cells.
When you delete cells from a worksheet, you delete the contents of the cells and the actual cells themselves. The remaining cells shift to fill the space left by the deleted cells. When you insert or delete cells or ranges, formulas that reference affected cells automatically update. (See also "Deleting: Cell Contents.")
As you edit your worksheet, you may need to insert or delete entire columns in the worksheet. Perhaps you forgot to add a category, and you now want to insert it between existing columns of data. Or you might want to delete a column containing outdated information. (See also "Inserting and Deleting: Rows.")
TIP: If you want to insert or delete multiple columns, select the same number of columns as you want to insert or delete in Step 1 above.
Just as you can insert or delete columns in the worksheet, you also can insert blank rows or delete existing rows. In a list of information, you can insert a row if you want to add a new record of data, for example, or you can delete a row if you no longer want to include a specific record in the list. (See also "Inserting and Deleting: Columns.")
TIP: If you want to insert or delete multiple rows, select the same number of rows as you want to insert or delete in Step 1 above.
Each new workbook contains three worksheets by default. You can easily insert new worksheets (as many as 255 total) at any time. If you are copying or moving existing worksheets, you do not need to insert a blank worksheet before you copy or move a worksheet. (See also "Copying: Worksheets" and "Moving: Worksheets.")
TIP: You can change the default number of worksheets that appears in a new workbook. Choose Tools, Options; then click the General tab. In the Sheets in New Workbook text box, type the number of worksheets you want new workbooks to contain; then click OK.
The sheet tabs at the bottom of each workbook enable you to quickly move among the worksheets in that workbook. If your workbook contains multiple worksheets and some of the sheet tabs are hidden, you can increase the width of the sheet tab area by dragging the tab split box (the vertical bar just left of the horizontal scroll bar) to the right. If you still cannot see all the sheet tabs, use the tab scroll arrows to the left of the sheet tabs to move through the sheet tabs. With a single click, the tab scroll arrows enable you to move to either the first sheet, previous sheet, next sheet, or last sheet in the workbook.
NOTE: If your workbook doesn't display any sheet tabs, choose Tools, Options; then click the View tab and select the Sheet Tabs check box.
In Excel, you can move cell data within a worksheet, between worksheets, between workbooks, or even between Excel and another application. When you move (or cut) cell data, you also move the cell formatting with the data. Be sure you won't overwrite any existing data when you move the data (make room for the data first, if necessary). (See also "Copying: Cell Data.")
TIP: To quickly move data to another location in the same worksheet, highlight the cell or range you want to move. Drag the border around the selected range to the desired location.
You can move worksheets to a different location within the same workbook, or to another workbook entirely. You do not need to create a blank worksheet to receive the new data--you can move the worksheet directly to the new location. (See also "Copying: Worksheets.")
TIP: To quickly move a worksheet in the same workbook, select the sheet tab for the worksheet you want to move. Then drag the active sheet tab in front of the sheet tab where you want the worksheet to appear.
NOTE: To place a worksheet as the beginning worksheet in a new workbook without first creating the workbook, select (new book) in the To Book list in the Move or Copy dialog box.
Each worksheet in a workbook is automatically assigned a name. In a new workbook, worksheets are named Sheet1, Sheet2, and so on, as displayed on the sheet tabs. You can also assign your own descriptive name of up to 31 characters to each worksheet. You can use spaces in a worksheet name, but you cannot use any of the following characters:
/ \ : ? * [ ] < >
With the Edit, Paste Special command, you can copy and paste part of a cell's attributes, such as the format or value, but not both. This command also enables you to transpose data by switching rows of cells to columns, and columns to rows. In addition, you can use this command to combine the attributes of cells by pasting them together.
TIP: To quickly copy only the cell formats, select the cells with the formats that you want to copy. Click the Format Painter button on the Standard toolbar. Then select the range to copy the formats to.
You can save a great deal of data-entry time with Excel's Copy and Paste commands and other shortcuts. Rather than typing each formula in a worksheet, you can type a few formulas and copy or fill them into other cells. You even can copy the formula and format at the same time. Be sure you won't overwrite any existing data when you paste the copied data (make room for the data first, if necessary).
(See also "Copying: Cell Data" and "Moving: Cell Data.")
You can paste multiple copies of data even if the areas into which you are pasting are not adjacent. Be sure you won't overwrite any existing data when you paste the copied data (make room for the data first, if necessary). (See also "Pasting: Multiple Copies.")
If you need to share worksheets with other users, you can prevent the contents of specific cells from being changed by turning on worksheet protection. You first must unlock cells that you want others to be able to change, and then protect the entire worksheet. (See also "Protecting: Individual Worksheets" and "Protecting: Workbooks.")
NOTE: You can assign a password so that only users with access to the password can make changes to the unlocked cells. In the Protect Sheet dialog box, type the password you want to use in the Password text box; then click OK. In the Confirm Password dialog box, reenter the same password and then click OK.
To turn off worksheet (and therefore cell data) protection, choose Tools, Protection, Unprotect Sheet.
You can protect individual worksheets in a workbook if you want to prevent others from accessing them. Protecting a worksheet does not prevent others from opening the workbook and modifying other unprotected worksheets in the workbook, however. (If you want to protect an entire workbook, see "Protecting: Workbooks.")
To turn off worksheet protection, display the worksheet and choose Tools, Protection, Unprotect Sheet. Then, enter the password and click OK.
CAUTION: Don't forget your password! When you protect a worksheet, write down the password and keep it in a safe location. You won't be able to open the worksheet or access any data in the worksheet without the password.
You can open a password-protected workbook just as you would normally open a workbook. Before the workbook appears on-screen, however, Excel prompts you for the password(s) you assigned to the workbook. If you do not remember the password for opening the workbook, you will not be able to access the workbook. If you forget only the password you set for write-protecting the workbook, you can open the workbook but you can't enter new data, even if you save the workbook under a different name. (See "Protecting: Workbooks" before you complete this task.)
You can prevent other users from accessing or changing the contents of a workbook by assigning password protection to a file. Excel provides three levels of protection: you can assign a workbook as read-only recommended, as write-protected, and as password-protected. The first setting suggests to users that they open a file in read-only mode. The last two options enable you to enforce protection by restricting file modification or access only to those who have the password.
NOTE: The Read-Only Recommended option doesn't override the password settings. If you assign a password to a workbook, only those who have access to the password can open the workbook and view the worksheet data.
To remove protection from a workbook, open the workbook and choose File, Save As; then click Options, remove the passwords from the Save Options dialog box (and clear the Read-Only Recommended check box, if applicable), and click OK. Then click Save to save the workbook with these changes.
CAUTION: Don't forget your password. When you protect a workbook, write down the password and keep it in a safe location. You won't be able to open the workbook or access any data in the workbook without the password.
You can use the Replace command to quickly locate and replace one string of data with another. You can choose to replace all occurrences of a selected string, or you can replace a string one by one. (See also "Finding Worksheet Data.")
NOTE: The Replace command performs the search in the current worksheet only. To search in other worksheets in a workbook, you must select them individually, and then choose Edit, Replace.
You may need a custom dictionary with your worksheets so that you are not frequently prompted to verify the spelling of client names, abbreviations, product codes, industry terms, and so on. When Excel checks spelling, it looks first in the standard dictionary. If Excel doesn't find the word there, it checks the custom dictionary.
Unless you specify otherwise, words you add go into the dictionary named CUSTOM.DIC. This name appears in the Add Words To drop-down list in the Spelling dialog box. You can build your own custom dictionaries and select them from the list. You can have as many custom dictionaries as you like, but only one can operate at a time with the standard dictionary.
At any time when the Spelling dialog box is open, you can change to a different custom dictionary by selecting the dictionary from the Add Words To list.
To add words to your custom dictionary, start the spell check. When you want to add a word to a custom dictionary, select the dictionary from the Add Words To list and choose the Add button.
With Excel's dictionary, you can check the spelling of one word, the entire worksheet, or even a chart. Microsoft Office applications all use the same spelling checker and dictionaries.
You also can check against a custom dictionary that contains abbreviations or words specific to your clients or industry.
The following table lists the options available in the Spelling dialog box and describes how they work:
| Option | Description |
| Ignore | Ignores this word and continues. |
| Ignore All | Ignores this word throughout the document. |
| Add | Adds this word to the current dictionary. |
| Suggest | Suggests some alternatives from the dictionary. This option is available only if the Always Suggest check box is not selected; otherwise, Excel automatically provides suggested alternatives. |
| AutoCorrect | Adds this misspelling and the correction to the list of AutoCorrect entries. When you make this same mistake again, Excel automatically replaces the misspelling with the correct spelling. |
| Undo Last | Undoes the most recent spell check correction. |
| Cancel | Stops the spell check. |
NOTE: If no misspelled words are found, the Spelling dialog box never appears. Instead, a message box appears and tells you that the spell check is complete for the entire worksheet.
©Copyright, Macmillan Computer Publishing. All rights reserved.