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?

Inserting subtotals

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.