Microsoft Office Tutorials and References

In Depth Information

**Using a Pivot Table Instead of Formulas**

Using a Pivot Table Instead of Formulas

The Excel pivot table feature is incredibly powerful, and you can often create pivot tables in lieu

of creating formulas. This tip describes a simple problem and provides three different solutions.

Figure 103-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 103-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 to display the Subtotal dialog box.

➜

➜