TOC BACK FORWARD HOME

Microsoft® Excel 97 Quick Reference

- 4 -
Data Analysis

One of the fundamental uses of a spreadsheet program is to analyze data. Excel provides multiple tools for making analysis easier for novice Excel users, as well as more complex tools for engineers, scientists, and financial analysts.

You can use Excel to create databases or lists of information. Features such as the Data Form enable you to quickly insert, delete, and search database records. You also can easily sort and filter data in a database. Using the outlining or pivot table features, you can manipulate lists to summarize data by using different views . You can use Goal Seek and Solver to find answers to financial problems that require an optimum solution.

Excel's AutoFill, AutoComplete, and Pick From List features enable you to enter data more quickly. You can even use the Template Wizard to create your own professional-looking data entry forms that make it easier for others to enter data. These forms can include controls such as check boxes, scroll bars, and option buttons similar to those used in Excel dialog boxes.

AutoFill: Entering Custom Data Series

Excel enables you to create and insert a custom list or data series in a worksheet. Suppose that you frequently enter the same company department names in different worksheets. You can define these names as a list, enter the first name, and then have Excel's AutoFill feature fill in the remaining names. (See also "Entering: Data Series" in the section "Getting Started.")

Steps

  1. With a worksheet open, choose Tools, Options; then click the Custom Lists tab.

  2. In the List Entries box, type the entries you want to include in the custom list. Press Enter after each entry.

  3. Click Add. The list you entered now appears in the Custom Lists box.

  4. If you want to create additional custom lists, click NEW LIST in the Custom Lists box, and then repeat Steps 2 and 3 above.

  5. Click OK to add the list and close the dialog box.

TIP: If you want to create a custom list based on existing text in a worksheet, use the Import List From Cells text box to enter the range containing the list. Then click the Import button to import the list into the dialog box.

AutoFilter: Filtering a Database

The AutoFilter feature enables you to filter, or work with a subset of data in a list, without moving or sorting the list. Each field name at the top of the list becomes a drop-down list, from which you can choose which data you want to view. This feature allows you to easily perform editing and formatting commands on the resulting subset of data. (See "Databases: Creating a Database" before you complete this task.)

Available Filter Options

Options that are available for displaying data are listed in the following table:

AutoFilter Options

Option Description
(All) Displays all records in this field (the default option).
(Top 10) Enables you to filter a specified amount of numeric data from the top or bottom of a list.
(Custom) Displays the Custom AutoFilter dialog box, enabling you to create AND or OR criteria.
(Exact value) Displays only records with the exact value you specify in this field.
(Blanks) Displays all records with blanks in this field.
(NonBlanks) Displays all records with nonblanks (records that contain data) in this field.

Steps

  1. Select any cell in the list; then choose Data, Filter, AutoFilter.

  2. Click the drop-down list in the column that contains the data you want to filter.

  3. Select the criteria you want to display for that field. Select from the options shown in the table above. As soon as you make an AutoFilter selection from a drop-down list, the worksheet hides rows that do not meet your criteria. You immediately see the results of your filter.

  4. To discontinue AutoFilter for this list, choose Data, Filter, AutoFilter. The drop-down lists disappear from the field labels.

NOTE: Data stored to the left or right of the list may be hidden when you filter the list. If other data shares the worksheet with the list, store it in rows above or below the list area.

Comments: Adding to a Cell

When you share a worksheet with other users, or if the data you use in a formula changes on a regular basis, you can attach a comment to a cell. Comments are descriptive notes commonly used to explain the data in a particular cell. These comments appear in pop-up text boxes when you move the pointer over the cell containing the comment.

Each comment automatically includes the author's name. Examples of items you may choose to include in a comment are: the date a worksheet (or cell) was last modified, worksheet assumptions, data-entry limits, the significance of a particular value, or the origin of a formula.

Steps

  1. Select the cell you want to contain the comment, and choose Insert, Comment. You see a pop-up text box with your name.

  2. Type the text for the comment; if you need to move to a new line, press Enter. Click in the worksheet area when you are finished.

  3. To see the comment, point to the cell with the red indicator in the top right corner.

TIP: If you frequently use the Auditing toolbar, you may prefer to use the New Comment button on the Auditing toolbar to add comments to the worksheet. To display the Auditing toolbar, choose Tools, Auditing, Show Auditing Toolbar.

NOTE: To turn the comment indicator on or off, choose Tools, Options; then click the View tab. In the Comments area, select None to turn off the indicators, Comment Indicator Only to show just the indicator, or Comment & Indicator to show both the indicator and the comment.

(See also "Comments: Selecting, Finding, Editing, and Printing.")

Comments: Selecting, Finding, Editing, and Printing

After you've attached a comment to a cell in the worksheet, you can easily select worksheet comments, find specific text in comments, edit a comment, or print comments. (See "Comments: Adding to a Cell" before you complete this task.)

Steps

  1. To select all the cells in a worksheet that contain comments, choose Edit, Go To; then click Special. Click the Comments option and click OK. You can then move between the selected cells by pressing Tab or Shift+Tab.

  2. To search for specific text in worksheet comments, choose Edit, Find, and type the text in the Find What text box. In the Look In list, select Comments; then click Find Next. Click Close to close the dialog box.

  3. To edit a comment, select the cell and choose Insert, Edit Comment. Edit the comment as you normally edit text in Excel. Click outside the text box when you are finished.

  4. If you type more text than will fit within the text box, you can resize it. To do so, drag any of the selection handles.

  5. To print the comments in a worksheet, choose File, Page Setup; then click the Sheet tab. Select Comments, and then choose whether you want to print the comments at the end of the sheet or as displayed in the sheet. Click Print to begin printing.

TIP: To delete a comment, right-click the cell that contains the red indicator. Then choose Delete Comment from the shortcut menu.

Databases: Creating a Database

One of the most common uses of Excel is to help manage a database. You can use Excel database features to work with a simple list of information, such as a To Do list, or a larger, more complex database that contains several columns of data. In Excel, the terms database and list are often used interchangeably. This book uses the term database to refer to both simple lists and more complex databases.

A database includes information that contains similar sets of data, such as a client database. In an Excel database, the information in one row (such as all the information for one client) is known as a record. In each row, individual items are stored in fields--each field is a column in the database. An example of a field in a client database would be the client's name; another would be the address of the client. When information is organized in a database format, you can easily sort, filter, and summarize data.

Steps

  1. Type the field names in a single row of a worksheet. These field names (such as Name, Address, Phone, and so on) will appear as column headers in your database.

  2. Select all the field names and apply a style to them, such as boldface, italic, or a larger type font. This will make the field names stand out from the records of your database.

  3. Type the records of your database (such as information on each client) directly below the field names, one record per row. Do not leave a blank row after the field names.

NOTE: Your Excel database is actually just a special type of Excel worksheet. You can still use all the familiar worksheet commands to edit and format your database.

(See also "Databases: Inserting Records.")

Databases: Deleting Records

When you need to delete records from a database, you can delete them directly from the worksheet, or you can use a data form to locate and delete specific records. (See "Databases: Creating a Database" before you complete this task.)

Steps

  1. To delete a record using the data form, select any cell in the database; then choose Data, Form.

  2. Click Find Next or Find Prev until the record you want to delete is displayed in the text boxes, and then click Delete.

  3. When the warning box appears, click OK to delete the record. (Keep in mind that you cannot undo a record deleted with the data form.) Click Close to return to the worksheet.

TIP: To delete a record directly from the worksheet, right-click the row number containing the record you want to delete. Then select Delete from the shortcut menu to delete the row containing the record. If you use this method, be sure that you won't lose any data stored in the same row as the record you want to delete. If you want to undo the deletion, immediately choose Edit, Undo.

Databases: Finding Records

When you need to locate specific records in a database, you can use a data form to help you find them. You just enter the search criteria, and Excel finds the records corresponding to that criteria. (See "Databases: Creating a Database" before you complete this task.)

Steps

  1. Select any cell in the database; then choose Data, Form.

  2. Click the Criteria button; then type the criteria you want to search on in one or more text boxes. Excel performs the search only on the subset of data that matches the criteria you specify.

  3. Click Find Next until you find the record you want; then click Close.

TIP: If your database is small, you may prefer to use the data form to browse through the records in the database. Choose Data, Form; then click Find Next or Find Prev to find the desired record.

Databases: Inserting Records

A data form provides a convenient way to insert records in a database, as well as to search for and delete existing records. You also can insert records directly in the database by inserting rows in the worksheet. (See "Databases: Creating a Database" before you complete this task.)

Steps

  1. Select any cell in the database; then choose Data, Form.

  2. Click the New button; then type data for the new record in each of the text boxes.

  3. Repeat Step 2 for each new record you want to add to the database.

  4. Click Close when you are finished. The records you added are appended to the bottom of the database. You can sort the new records at any time if you want them to appear in a different order.

TIP: To erase the current entry in the data form as you are inserting new records, click the Restore button.
TIP: To insert a record directly in the worksheet, right-click the row number where you want the new row (record) to appear. Then select Insert from the shortcut menu to insert a blank row in the database.

(See also "Databases: Sorting a Database.")

Databases: Returning to the Original Sort Order

If you want to sort a database but later return it to the original order, you need to add a record index to the database. A record index can assign a number to each record according to the record's position or its date and time of entry. You may want to use a record index, for example, if you need to keep track of the order in which records are entered in a database. You can insert a column or cells to make room for an index. (See "Databases: Creating a Database" and "Databases: Sorting a Database" in this section and "Inserting and Deleting: Columns" in the section "Editing Workbooks" before you complete this task.)

Steps

  1. To index database records so that you can later return them to their original order, first insert a column for the index anywhere in the database. Many databases include an index field as the first column in a database. (If you named the database range, you need to redefine the name to include the new cells.)

  2. Type a number, such as 1, in the top cell of the column (beside the first database record). Type 2 in the second cell.

  3. Select the cells containing 1 and 2 and drag the fill handle down the length of the database. When you release the mouse button, a series of numbers fill in next to each row. These are the index numbers.

NOTE: When you sort, always make sure that you include the column containing the index numbers. When you want to return the database records to their original order, select the column of index numbers in the Sort By list and select Ascending.

Databases: Sorting a Database

Excel enables you to organize the data in a database to suit your needs. Although sorting works best on databases, you can actually sort any data in your worksheet. You can sort data either alphabetically or numerically, in ascending or descending order. (See "Databases: Creating a Database" before you complete this task.)

Steps

  1. Select any cell in the database; then choose Data, Sort.

  2. In the Sort By box, select the field you want to sort on, then select either Ascending or Descending sort order.

  3. If you want to sort on additional fields, select the fields in the Then By text boxes, and then select the sort order for each field. Click OK to perform the sort.

TIP: To quickly sort the database on a single field, select any cell in that field. Then click either the Sort Ascending or Sort Descending button on the Standard toolbar.

NOTE: Excel sorts date fields using the serial number created by dates and times entered in cells. Sorting works correctly only on dates and times entered with a date and time format that Excel recognizes. If you enter dates and times that Excel does not recognize, Excel normally stores them as text and sorts them in text order. (See also "Entering: Dates and Times" in the section "Editing Workbooks.")

(See also "Databases: Returning to the Original Sort Order.")

Forms: Adding Check Boxes

A check box is linked to a cell so that the result of the check box status appears as TRUE or FALSE in the linked cell. Selecting the check box makes the cell TRUE. Deselecting the check box makes the cell FALSE. The check box control is commonly used in a data form and provides an option to the user. (See "Forms: Adding Controls" before you complete this task.)

Steps

  1. Draw a check box in the active worksheet by clicking the Check Box button on the Forms toolbar and then clicking the cell where you want the check box; then right-click the check box and choose Format Control from the shortcut menu.

  2. Click the Control tab, then select the default value of the check box: Unchecked for FALSE result, Checked for TRUE result, or Mixed for #NA result. Choose the 3D Shading check box to add depth to the check box.

  3. Select the Cell Link edit box, and click the cell that you want to hold the results of the check box; then click OK.

Forms: Adding Controls

Excel enables you to place on a worksheet the same type of data-entry controls that you can place in a dialog box run by a macro or Visual Basic procedure. Controls are data-entry objects commonly used in forms, such as scrolling lists or check boxes. When you enter a value in a control or make a selection from a control, the entry appears in a worksheet cell.

Steps

  1. With a worksheet open, display the Forms toolbar by right-clicking in the toolbar area and choosing Forms.

  2. Click the button on the Forms toolbar that represents the control you want to draw. To create a check box control, for example, click the Check Box button. The pointer changes to a crosshair.

  3. Move the crosshair to the top-left corner of where you want the control to appear, and drag down and to the right to where you want the control's opposite corner.

  4. Release the mouse button. The control appears in the worksheet. If you created a button control, the Assign Macro dialog box appears when you release the mouse button. Select the macro you want to assign to the control; then click OK.

  5. To modify a control, hold down Ctrl and then click the control to select it. When a control is selected, handles appear around the object. You can then move, resize, or change the properties of the selected control. Drag an edge of the control to move it. Resize the control by dragging one of the handles.

TIP: To deselect a control, click a cell or object outside the selected control. To delete the control, select it and then press Delete.
TIP: Double-click a selected control to display the Format Object dialog box. To change the format of the control, select the options you want from the available tabs.

Forms: Adding Lists

Both a list box and a combo box restrict users to choosing from a defined list of items. Restricting user selections prevents them from typing a mistake, entering incorrect data, or using old data.

List boxes and combo boxes produce the same result, but the appearance of the list in each type of box differs. A list box shows multiple items in the list, while the list stays the same height. A combo box is only one item high and has a drop-down arrow on the right side. Clicking the drop-down arrow displays the list. Combo boxes are commonly used when not enough room exists for a list box. (See "Forms: Adding Controls" and "Databases: Sorting a Database" before you complete this task.)

Steps

  1. In the worksheet, enter a vertical list of items you want to appear in the list. Enter one item per cell.

  2. If you want the list to appear sorted within the control, select the list and choose Data, Sort to sort the list. Choose the desired sort options and then click OK.

  3. Click the List Box or Combo Box button on the Forms toolbar and draw the control in the worksheet. Make a list box wide enough to see the entries in the list and tall enough to see multiple items. A combo box only needs to be tall enough for one item.

  4. Right-click the list and choose Format Control; then click the Control tab.

  5. Select the Input Range edit box; then drag across the range of cells in the worksheet that contain the data for the list. This list will appear in the list box or the combo box.

  6. Select the Cell Link box and click the cell that will receive the results of the list. If you are using a combo box, enter in the Drop Down Lines box the number of lines displayed when the list appears; then click OK.

NOTE: The cell you specify in the Cell Link box returns a value that represents the number of the selected list item, not the actual list item itself. However, you can use this number in a formula to return the selected item in the list. If a combo box is linked to cell B5 and the input range for the list is in the range C5:C10, for example, the following formula returns the value from range C5:C10 based on the selection from the combo box:
=INDEX(C5:C10,B5)

Forms: Adding Option Buttons

Option buttons are used most frequently when you need to make a single choice from a group of choices. Option buttons are round buttons that come in groups.

If you draw just option buttons on a worksheet, all these buttons will belong to the same group, which means that you can select only one button at a time. You can have multiple groups of buttons, however, by enclosing each group in a group box drawn with the group tool. (See "Forms: Adding Controls" before you complete this task.)

Steps

  1. Draw a group box by clicking the Group Box button on the Forms toolbar and dragging where you want the box in the worksheet. While the box is selected, type a title to replace the default box title.

  2. Click the Option Button tool on the Forms toolbar and draw an option button inside the group box. Type a title while the option button is selected.

  3. Right-click the option button and choose Format Control; then click the Control tab. Select the value for the option button: Unchecked or Checked.

  4. Select the Cell Link edit box, click the worksheet cell that you want to contain the results from the group of option buttons, and then click OK.

  5. Repeat Steps 2 through 4 for each additional option button that you want to include. When you have finished creating option buttons, click a cell outside the group box.

NOTE: When you create additional option buttons, you don't have to enter a cell reference for the Cell Link. Only one linked cell exists for all option buttons in a group.

Forms: Adding Scroll Bars

Scroll bars enable users to enter a number within a wide range while getting a visual impression of where their entry lies within the range. To enter a number, you can click the top or bottom arrow, click the gray part of the scroll bar, or drag the square button in the scroll bar. (See "Forms: Adding Controls" before you complete this task.)

Steps

  1. Draw a scroll bar control in the worksheet by clicking the Scroll Bar button on the Forms toolbar and dragging where you want the scroll bar; then right-click the control and choose Format Control from the shortcut menu.

  2. Click the Control tab. In the Current Value edit box, enter the amount you want the linked cell to have when the worksheet opens.

  3. Enter the lowest value you want the scroll bar to produce in the Minimum Value box. Enter the highest value you want in the Maximum Value box.

  4. Set the amount of change for each click of the control in the Incremental Change box. In the Page Change edit box, enter the amount of change you want when the user clicks the gray part of the scroll bar.

  5. Select the Cell Link edit box and then click the cell in the worksheet you want to receive the scroll bar result; then click OK.

Forms: Adding Spin Boxes

Spin boxes are controls that show two arrow heads--one pointing up and the other pointing down. Each click of an arrow head increases or decreases the amount in the cell linked to the spin box. Holding down the mouse button on a spin box causes the number to change continuously. (See "Forms: Adding Controls" before you complete this task.)

Steps

  1. Draw a spin box in the worksheet by clicking the Spinner button on the Forms toolbar and dragging where you want the spin box; then right-click the spin box and choose Format Control from the shortcut menu.

  2. Click the Control tab. In the Current Value edit box, enter the amount you want the linked cell to have when the worksheet opens.

  3. Enter the lowest value you want the spin box to produce in the Minimum Value box. Enter the highest value you want in the Maximum Value box. Set the amount of change for each click of the control in the Incremental Change box.

  4. Select the Cell Link edit box and then click the cell in the worksheet that you want to receive the spin box result; then click OK.

Forms: Creating a Form

Excel has many features that enable you to create nice-looking and easy-to-use data entry forms. Before you can create a data entry form using the Template Wizard, you need to create a worksheet that will be the basis for your form. Using Excel formatting techniques, you can make worksheets appear more like a paper form. You probably want to start by having the form in the same workbook as the worksheets that do the calculations, which makes it easier to create and maintain links from the controls on the form to the worksheets using the data. (See also "Template Wizard: Creating a Form" and "Forms: Adding Controls.")

Enhancing the Appearance of a Form

In most cases, you probably want the form you create to resemble a paper form rather than a typical worksheet. To do this, you can change several Window Options that appear on the View tab of the Options dialog box. The following table describes the recommended settings for these options when you are creating a form.

Recommended Window Options for Forms

Option Description
Page Breaks Deselect so automatic page breaks do not show.
Formulas Deselect so results show, not formulas.
Gridlines Deselect so gridlines do not show.
Row & Column Headers Deselect so row and column headings are hidden.
Outline Symbols Deselect unless your form is built in an outline.
Zero Values (Optional) Deselect to hide zeros.
Horizontal Scroll Bar Deselect to hide the scroll bar at the bottom.
Vertical Scroll Bar Deselect to hide the scroll bar on the right edge.
Sheet Tabs Deselect to hide the worksheet tabs.

Steps

  1. To create a form and make the worksheet window look like a paper form, choose Tools, Options; then click the View tab.

  2. Select from the options in the Window Options group to affect the appearance of only the active window. For recommendations on which options are most appropriate for forms, see the table above.

  3. Enter the text labels you want to include in your form; enter these labels in cells above or to the left of the cells that will contain the data input.

  4. To enhance the appearance of the form, format the workbook data as desired using the buttons on the Formatting toolbar, for example. You also can add controls to the form, such as check boxes.

  5. Remember to print the form to check that the printed copy looks acceptable.

TIP: Change the background color of a form to light gray to give it a more interesting appearance. Use the Shadow button on the Drawing toolbar to give pictures, charts, or text boxes a more three-dimensional appearance.

Forms: Formatting Controls

After you have drawn a control such as a check box on the worksheet, you can change the appearance of the control. You may want to change the font of the control, for example, so that it conforms with the fonts used in other worksheet data. (See "Forms: Adding Controls" and "Forms: Adding Check Boxes" before you complete this task.)

Steps

  1. Right-click the control that you want to format, and then choose Format Control from the shortcut menu.

  2. In the Format Control dialog box, click a tab, then select formatting options you want to apply to the control. Click OK when you have finished.

NOTE: The Format Control dialog box used to format controls on forms may contain a different number and type of tab, depending on the control that you format.

Goal Seek

NOTE: This feature's task requires understanding of a complex subject. If you are not familiar with the Goal Seek feature, you will probably want to become acquainted with it by reading Special Edition Using Microsoft Excel 97 for a complete tutorial coverage.

Although Goal Seek is similar to the Solver add-in, it is generally faster and easier to use because it doesn't provide as many options as Solver does. Use Goal Seek if you want to produce a specific value in a formula cell by adjusting one input cell that influences a value; otherwise, if you have one or more input cells and have constraints on the solution, or if you want to maximize or minimize a formula cell, use Solver. (See also "Solver: Using Solver.")

You can use Goal Seek when you want to find an input value that generates the answer you want in a formula cell. To find the level of sales required to break even, for example, the formula cell would contain the net present value, and you would want the NPV function to return a zero value (which represents a break-even figure).

Steps

  1. Select a goal cell that contains the formula you want to force to produce a specific value.

  2. Choose Tools, Goal Seek. The Set Cell text box references the cell you selected in Step 1 above.

  3. In the To Value text box, type the target value you want the formula cell to reach.

  4. In the By Changing Cell text box, type the cell reference of the input cell (the cell you want to change); then click OK to start the goal seek process.

Outlines: Creating Manually

Outlining enables you to expand or contract worksheets or reports so that you see more or less detail. In a sales report, for example, you might need to display various levels of detail depending upon who will read the report. With the outline feature, you can hide or display up to eight levels of detail in rows or columns.

Excel can automatically create an outline, or you can manually create the outline. Manual outlining is necessary if the data is organized in a way that Excel doesn't understand. In general, you should arrange the worksheet so that summary rows are located below the detail rows, and summary columns are to the right of the detail columns. You may want to apply boldface or italic to summary rows or columns for emphasis, although this is not required for the outline feature to work. (See also "Outlines: Excel's Automatic Outlining" and "Outlines: Formatting Outlines.")

Steps

  1. Select cells in the rows or columns that you want to outline. Select up to, but not including, the cell that contains the summary formula. If the rows or columns include only the data to outline, you can select the rows or columns to group.

  2. To group items on a level, choose Data, Group and Outline, Group. The Group dialog box appears.

  3. In the Group dialog box, select Rows or Columns, depending upon what you want to group; then click OK.

  4. Repeat Steps 1 through 3 above for each section you want to outline.

NOTE: If you selected an entire row or column in step 1, you don't see the Group dialog box. Excel groups the data by rows if you have rows selected or by columns if you have columns selected.

TIP: If you make a mistake or if you want to undo a grouping, you can use the Ungroup command. Select the section you want to ungroup. Then choose Data, Group and Outline, Ungroup. Select either Rows or Columns; then click OK.

Outlines: Displaying or Hiding Levels

You use the outline symbols to select which levels in an outline you want to display or hide. When you create an outline, the outline symbols appear automatically in a gray area to the left of the row numbers. (See "Outlines: Creating Manually" or "Outlines: Excel's Automatic Outlining" before you complete this task.)

Steps

  1. If outline symbols are not displayed, press Ctrl+8. This key combination works as a toggle, to display or hide the outline symbols.

  2. Display or hide levels of detail in specific rows or columns by following these actions, as desired:

Outlines: Excel's Automatic Outlining

You can have Excel create an outline for you automatically instead of creating the outline manually. Automatic outlining is useful if you haven't created an outline before or your out-line has a consistent layout. (See also "Outlines: Creating Manually.")

Steps

  1. If you want to outline data within a part of the worksheet, select the range you want to outline. If you want to outline the entire worksheet, select a single cell.

  2. Choose Data, Group and Outline, Auto Outline.

If Excel can determine a consistent direction of summarizing, it creates an outline. If Excel doesn't create an outline, it displays a warning message. If this occurs, you need to adjust the layout of your data or create an outline manually. (See "Outlines: Creating Manually.")

Outlines: Formatting Outlines

If you are creating a new outline, you can apply outline styles when Excel creates the outline. You also can apply outline styles to an existing outline. (See "Outlines: Creating Manually" or "Outlines: Excel's Automatic Outlining" before you complete this task.)

Steps

  1. If you are formatting an existing outline, select the cells to which you want to apply the outline styles. Otherwise, begin with Step 2.

  2. Choose Data, Group and Outline, Settings. The Settings dialog box appears.

  3. If you are formatting an existing outline, choose Apply Styles. Otherwise, select Automatic Styles and then click OK.

Outlines: Hiding Symbols

You can choose whether or not you want to display the outline symbols in a worksheet that includes an outline. For example, if your worksheet is outlined, you may choose to hide the outline symbols while showing the worksheet during an on-screen presentation. (See "Outlines: Creating Manually" or "Outlines: Excel's Automatic Outlining" before you complete this task.)

Steps

  1. To hide outline symbols in a worksheet, choose Tools, Options; then click the View tab.

  2. Clear the Outline Symbols check box; then click OK.

TIP: To toggle between hiding and showing outline symbols, add the Show Outline Symbols button to an existing toolbar. Choose Tools, Customize; then click the Commands tab. In the Categories
list, select Data. Scroll through the Commands list until you see the Show Outline Symbols icon. Drag the icon to the desired location in an existing toolbar; then click Close. To remove the icon, choose Tools, Customize; then drag the icon off the toolbar and click Close.

Outlines: Removing an Outline

You can remove either a portion of an existing outline or the entire outline. (See "Outlines: Creating Manually" or "Outlines: Automatic Outlining" before you complete this task.)

Steps

  1. To remove a portion of an outline, select cells in the row or columns at the level you want removed; or, to clear the entire outline, select a single cell in the worksheet.

  2. Choose Data, Group and Outline, Clear Outline.

Pick from List: Building a List of Entries

Using Excel's AutoComplete feature, you can type the first few letters of an existing column entry, and Excel completes the rest of the entry for you. A similar feature, called Pick from List, enables you to select from a list of entries when you enter text in that column. Excel builds the list from entries that you have already made in the column above the active cell. You will save much time and repetitive typing with these two features. (See also "AutoComplete: Entering Duplicate Data.")

Steps

  1. Select the cell in which you want to enter data.

  2. To display a list of existing entries in the current column, right-click the cell; then choose Pick From List on the shortcut menu. A drop-down list appears.

  3. Select an item from the list to complete the entry, or press Esc to close the list without choosing an entry.

TIP: Use Pick From List when you have many entries in a column where the first several characters are identical. In this case, picking from the list of entries is usually faster than typing the several characters required for autocompletion of your entry.

Pivot Tables: Adding a Data Field

In some cases, you may want to examine more than one kind of data in an existing pivot table. To do this, you use the PivotTable Wizard to add a second field to the DATA area. (See "Pivot Tables: Creating a Pivot Table" before you complete this task.)

Steps

  1. To add another data field to an existing pivot table, select a cell in the pivot table.

  2. Click the PivotTable Wizard button on the PivotTable toolbar. (Right-click the toolbar area and choose PivotTable to display this toolbar, if necessary.)

  3. In Step 3 of the PivotTable Wizard, drag the button for the data field you want to add to the DATA area; then click Finish.

Pivot Tables: Adding Rows, Columns, or Pages

If you want to include more detail in an existing pivot table, you can add more fields to the display of a pivot table. To do this, you use the PivotTable Wizard. (See "Pivot Tables: Creating a Pivot Table" before you complete this task.)

Steps

  1. To add a row, column, or page field to an existing pivot table, select a cell in the pivot table.

  2. Click the PivotTable Wizard button on the PivotTable toolbar. (Right-click the toolbar area and choose PivotTable to display this toolbar, if necessary.)

  3. In Step 3 of the PivotTable Wizard, drag the button for the desired field to the ROW, COLUMN, or PAGE area; then click Finish.

TIP: To remove a row, column, or page field from an existing pivot table, drag the field button outside of the pivot table.

Pivot Tables: Creating a Pivot Table

NOTE: This feature's task requires understanding of a complex subject. If you are not familiar with pivot tables, you will probably want to become acquainted with them by reading Special Edition Using Microsoft Excel 97 for a complete tutorial coverage.

A pivot table enables you to summarize, analyze, and manipulate data in lists and tables. When you use the PivotTable Wizard to create a pivot table, you tell Excel which fields in the list you want to arrange in rows and columns. You also can specify a page field that appears to arrange the data in a stack of pages. Pivot tables are called such because you can quickly rearrange the position of pivot table fields to give you a different view of the table.

You can create a pivot table from several sources. The default (and most common) choice is to create a pivot table from an Excel list or database. In addition, you can create the pivot table from data in an external data source, multiple consolidation ranges, or another pivot table.

One useful application of pivot tables is creating summary tables that group large categories of data, with totals displayed for each category. After you create a pivot table, you can also more easily create charts based on data in the summarized table.

Steps

  1. Select any cell in the list you want to summarize; then choose Data, PivotTable Report.

  2. In Step 1 of the PivotTable Wizard, select the Microsoft Excel List or Database option (the source of the data); then click Next.

  3. In Step 2 of the PivotTable Wizard, the range for the list appears in the Range text box. If this range is incorrect, type the correct range. Then, click Next.

  4. In Step 3 of the PivotTable Wizard, you define the column and row layout of the pivot table. The fields are listed as buttons on the right side of the dialog box. Drag into the DATA area the button corresponding to the data field you want to summarize. To arrange items in a field in columns with the labels across the top, drag the button for that field to the COLUMN area. To arrange items in a field in rows with labels along the side, drag the button for that field to the ROW area. Then click Next.

  5. In Step 4 of the PivotTable Wizard, specify where you want the pivot table to appear. (Be sure to choose an area that won't overwrite existing data.) If you want to specify additional options for the pivot table, click the Options button, choose the options you want, and click OK. Then, click Finish.

Pivot Tables: Editing a Pivot Table

Because pivot tables are devices for displaying information, you cannot manually change information in the body of the table. You can, however, change the names of the pivot table fields and items. Excel doesn't allow you to duplicate names. If you enter an existing field or item name inadvertently, Excel rearranges the pivot table, moving the item with that name to the location where you typed the name. (See "Pivot Tables: Creating a Pivot Table" before you complete this task.)

Steps

  1. To edit a pivot table field or item name, select that field or item in the pivot table.

  2. Type the new name and press Enter.

TIP: To change additional options for a specific pivot table field, double-click the field button. Choose the options you want in the PivotTable Field dialog box; then click OK.

Pivot Tables: Formatting a Pivot Table

You can use the AutoFormat feature to apply a set of predefined formatting choices to a pivot table, just as you can for other tables in a worksheet. This will greatly enhance the appearance and readability of a pivot table. (See "Pivot Tables: Creating a Pivot Table" before you complete this task.)

Steps

  1. With the pivot table you want to format active, choose Format, AutoFormat.

  2. In the Table Format list, select the desired table format; then click OK.

(See also "Formatting: Tables with AutoFormats" in the "Formatting" section.)

Pivot Tables: Updating a Pivot Table

A pivot table does not change automatically when you change the data in the source list or table. You can, however, update or refresh the pivot table for the following types of changes to the source data: changes to data in a data field, new or changed items, or insertions or deletions of fields or items. (See "Pivot Tables: Creating a Pivot Table" before you complete this task.)

Steps

  1. To update a pivot table, select any cell in the pivot table.

  2. Click the Refresh Data button on the PivotTable toolbar.

Solver: Changing Solver Options

You can customize Solver options, such as how you want Solver to find answers, how long Solver works, or the precision of the answer it attempts to find. In most cases, however, the default settings should be appropriate for Solver problems. (See "Solver: Using Solver" before you complete this task.)

Steps

  1. Open the workbook that contains the source data for Solver, and then choose Tools, Solver to display the Solver Parameters dialog box.

  2. Click the Options button. The Solver Options dialog box appears.

  3. Change the options, as desired. If you need more information on a particular option, click the question mark button, and then click the option for which you need help. Click OK when you've finished.

Solver: Installing Solver

Even if you installed the Solver option when you installed Excel, you still need to make the Solver add-in available by selecting it in the Add-Ins dialog box before you can begin to use it. After following this procedure, the Solver add-in will be available whenever you need to use the program.

Steps

  1. From any point in Excel, choose Tools, Add-Ins.

  2. In the Add-Ins Available list, select the Solver Add-In check box. If you don't see the Solver Add-In in the list, click Browse and then select the file named SOLVER.XLA (check the \LIBRARY\SOLVER folder); then click OK.

  3. Click OK again to close the Add-Ins dialog box.

NOTE: If you can't locate the SOLVER.XLA file, you need to run the Setup program to install the file.

Solver: Producing Reports

Solver can generate reports summarizing the solution results. These reports enable you to easily compare different solutions to the same problem.

Solver can generate an Answer Report, a Sensitivity Report, and a Limits Report. The Answer Report summarizes the original and final values of the variables, target cell, and the constraints. The Sensitivity Report tells you how sensitive the solution is, based on small changes made to the target cell formula. The Limits Report shows how the solution changes when target and adjustable cells are maximized or minimized while other variables are held constant. (See "Solver: Using Solver" before you complete this task.)

Steps

  1. After using Solver to solve a problem in the worksheet, the Solver Results dialog box appears; select one or more of the reports from the Reports list. Use Ctrl+click if you want to select multiple reports.

  2. Click the Help button if you need more information on a particular report. Click OK when you are ready to generate the reports.

Each report you selected appears on its own worksheet. Select a sheet tab to browse through the reports and the original worksheet.

Solver: Saving and Loading Solver Data

Excel enables you to save and later reload different Solver settings and results by using the Save Model option in the Solver Options dialog box. This allows you to easily switch between the various models you've created in a Solver problem. (See "Solver: Using Solver" before you complete this task.)

Steps

  1. Open the workbook that contains the source data for Solver, and then choose Tools, Solver to display the Solver Parameters dialog box.

  2. Click the Options button. The Solver Options dialog box appears.

  3. Click the Save Model option. Then select a range of cells on the worksheet large enough to contain the number of constraints plus three additional cells.

  4. Click OK to accept the range in the Save Model dialog box; click OK again when the Solver Options dialog box appears. Click Close to return to the worksheet.

  5. To reload Solver models that you've already saved, click the Load Model button in the Solver Options dialog box. Specify the range for the model data you want to load, and click OK.

Solver: Using Solver

NOTE: This feature's task requires understanding of a complex subject. If you are not familiar with the Solver, you will probably want to become acquainted with it by reading Special Edition Using Microsoft Excel 97 for a complete tutorial coverage.

Unlike Goal Seek, which finds a specific solution to a problem, Solver finds an optimal solution by adjusting input cells while ensuring that other formulas in the worksheet stay within limits you specify. Use the Solver add-in to determine the maximum or minimum value of one cell by changing the adjustable cells--for example, the maximum profit to be generated by changing overhead expenditures. (See also "Goal Seek.")

Before you use Solver, include the following inputs in the worksheet: adjustable cells; the objective formula that you want to maximize or minimize; and the constraints, which place limits on the Solver problem.

NOTE: If Solver has not been installed, the Solver option doesn't appear in the Tools menu. If this is the case, see "Solver: Installing Solver" prior to doing the steps below.

Using the Solver Sample Worksheets

To make it easier for you to set up your own Solver problems in the worksheet, Excel comes with an on-line workbook that shows you specific examples of the types of problems you can use Solver to solve. Open the SOLVSAMP.XLS workbook (usually in the \EXAMPLES\SOLVER folder) to see these examples. If you don't see this workbook, you need to run the Excel Setup program to install it. After you open the workbook, switch to the worksheet containing the example you want to see, then choose Tools, Solver. The target cell, adjustable cells, and constraints for the worksheet are already specified.

Steps

  1. Open the workbook that contains the source data for Solver, and then choose Tools, Solver; the Solver Parameters dialog box appears. In the Set Target Cells box, enter a cell reference or name for the target cell; the target cell must contain a formula.

  2. For the value of the target cell to be as large as possible, click Max. For the value of the target cell to be as small as possible, click Min. For the value of the target cell to have a specified value, click Value of; then type the specific value in the box.

  3. In the By Changing Cells box, enter a name or cell address for each adjustable cell, separating nonadjacent cell references with commas.

  4. To have Solver automatically propose the adjustable cells based on the target cell, click Guess. In the Subject to the Constraints box, enter any constraints to be applied; then click Solve.

TIP: If you want to restore the original values in the worksheet after you use Solver, click the Restore Original Values option in the Solver Results dialog box.

NOTE: The selected cells must be related through formulas in the worksheet. Otherwise, changing one cell will not change the other.

Template Wizard: Creating a Form

With the Template Wizard, Excel enables you to create professional-looking data entry forms that make it easier for others to enter data into an Excel database. The Template Wizard guides you through the process of converting your worksheet into a data entry form. (See "Forms: Creating a Form" before you complete this task.)

Steps

  1. To create a data entry form using the Template Wizard, open or create the workbook that contains the data you want to use for the form.

  2. Choose Data, Template Wizard. If you don't see the Template Wizard option, you need to install this add-in before you can continue. Search on "Template Wizard" in Excel Help for more information.

  3. Follow the steps in the Template Wizard.

  4. Click Finish when you are done creating the template.

  5. Save the worksheet that contains the template.

NOTE: To enter data using the data entry form you have created, create a new workbook based on the template you created. The new workbook contains all the formatting, formulas, and data contained in the template, but it must be saved using a different name.

(See also "Templates: Creating and Using a Workbook Template" in the section "Customizing.")

Template Wizard: Using Excel's Templates

Excel provides several prebuilt templates for your use. Not all of these Excel templates are installed if you used the standard installation, however. To install additional templates, run the Setup program again, and choose the Custom installation option. (See also "Templates: Creating and Using a Workbook Template" in the section "Customizing.")

The Invoice, Expense Statement, and Purchase Order templates provided with Excel are designed for use with the Template Wizard.

Steps

  1. With Excel open, choose File, New to use one of Excel's predefined templates.

  2. Click the Spreadsheet Solutions tab; then click the icon representing the template you want to use. A preview of the template appears in the Preview box.

  3. Click OK to open the selected template. Click the Customize button in the template if you want to customize the template. This enables you to insert your company logo, name, address, and so on.

  4. Save the file using a different name when you've finished customizing the template.

TIP: Use the custom toolbar provided with Excel's templates to perform actions on the template. Point to a button on the toolbar and pause to see a brief description of the button's purpose.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.