Microsoft Office Tutorials and References
In Depth Information
Tip 78: Using a Pivot Table Instead of Formulas
Using a Pivot Table Instead of Formulas
The Excel PivotTable feature is incredibly powerful, and you can often create pivot tables in lieu of
creating formulas. This tip describes a specific problem and provides three different solutions.
Figure 78-1 shows a range of data that contains student test scores. The goal is to calculate the
average score for all students plus the average score for each gender.
Figure 78-1: What’s the best way to calculate the average test score for males versus females?
The first solution involves automatically inserting subtotals. To use this method, the data must be
sorted by the column that will trigger the subtotaling. In this case, you need to sort by the Gender
column. Follow these steps:
1. Select any cell in column C.
2. Right-click and choose Sort ➜ Sort A to Z from the shortcut menu.
3. Choose Data ➜ Outline ➜ Subtotal.
The Subtotal dialog box appears.