Analyzing Data in Lists

As you learned in Chapter 2, data lists are highly structured, and they share common field names and data values. Because of this, many of Excel’s features, although they are not considered true data analysis tools, can still provide quick facts about data lists. This section provides some additional, miscellaneous tasks that you can perform with lists.

To reiterate, for the best results when using these data list features, be sure of the following:

• The data values in a data list share the same field names.

• The data values for each field are presented consistently.

• The data values have some type of data in each field, even if the data value is 0, N/A, or Unknown.

• Only one list appears on each worksheet.

• Field names are descriptive but not verbose.

• Fields are separated into their most basic parts.

One helpful data analysis task for lists of values is to add subtotals to the list. Adding subtotals is much simpler and faster than inserting numerous SUM or SUMIF worksheet functions. To add subtotals, select the data you want to summarize, click Subtotals on the Data menu, and then provide the subtotal settings in the Subtotal dialog box.

Subtotals work best when the data contains a header row. You should also perform any required sorting or filtering of the data before adding subtotals. Use the outline buttons and the plus and minus buttons to show or hide data and subtotals. These buttons are located to the left of the row number indicators, as shown in Figure 3-19.

Figure 3-19: Use the outline buttons and the plus and minus buttons to the left of the row number indicators to work with subtotals.

Tip

To remove subtotals, click Subtotals on the Data menu and then click Remove All in the Subtotal dialog box.

Your Turn

To determine any visible trends, you want to quickly subtotal the number of nights booked and the room service charges for each preferred customer for the entire year.

1. Open Hotel.xls. If the file is already open, close it (do not save it) and open the file again.

2. Click cell A1, the column heading for Gold Customer Name.

3. On the Data menu, click Subtotals.

4. In the At Each Change In list, select Gold Customer Name.

5. In the Add Subtotal To list, select the Nights Booked and Total Room Service options.

6. Click OK.

7. Click outline button 2 to display subtotals by nights booked and total room service. Compare your results with Figure 3-20.

   
   Figure 3-20: Subtotals displayed by nights booked and total room service.

Another helpful data analysis task to perform with lists is to quickly display simple function results in the Excel status bar for selected cell groups. To do so, select a group of cells (the cells should all be in the same column), and then right-click anywhere in the status bar to display a list of common data analysis functions. (See Figure 3-21.)

Figure 3-21: A list of simple status bar functions.

If you click a function, such as Sum, the status bar shows the simple function’s result, as you can see in Figure 3-22.

Figure 3-22: The sum of the subtotals for total room service is displayed in the status bar.

Tip

To display the status bar if it is not visible, click Options on the Tools menu. On the View tab, select the Status Bar check box and then click OK.

Your Turn

You want to display simple function results for customer Abercrombie.

1. Open the Hotel.xls file. If the file is already open, close it (do not save it) and open it again.

2. Select cells D2 through D13 in column D.

3. Right-click anywhere in the status bar, and then click Average. The average month’s room service charge was $630.l9.

Experiment with other summary functions such as Count, Max, Min, and Sum.