Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 7 Tables 289

When you created the fi rst PivotTable, you hid the blank category levels

for the table. Do this as well for the two-way table.

To hide the missing values:

1 Click the Row Labels drop-down list arrow in the PivotTable and

deselect the blank checkbox. Click OK.

2 Click the Column Labels drop-down list arrow and deselect the

blank checkbox. Click OK.

Figure 7-13 shows the completed PivotTable.

Figure 7-13
Two-way table
of department
versus calculus
requirement

The table in Figure 7-13 shows frequencies of different combinations of

department and calculus requirement. For example, cell B5, the intersection of

Bus,Econ and Not req, shows that 74 professors in the business or economics

departments do not require calculus as a prerequisite for their statistics course.

There are a total of 333 responses. Note that missing combinations of Dept and

Calculus are displayed as blanks in the PivotTable. For example, none of the

statistics courses offered in the HealthSc category has calculus as a prerequi-

site. How do these values compare when viewed as percentages within each

department? Let’s modify the PivotTable to fi nd out.

To show column percentages:

1 Right-click any of the count values in the table; then click Value Field

Settings in the pop-up menu.

2 Click the Show values as tab and then select % of row from the

Show values as list box.

3 Click the OK button. The revised PivotTable is shown in Figure 7-14.