Counting with PivotTables

Summary: Got a slew of data you need to analyze? You can count instances of various pieces of information quite easily using a PivotTable. The way you create the table depends on the version of Excel you are using, however. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Suppose you have a data table set up in Excel that represents your club membership. In the first column are the names of club members. In the second column are the cities in which the members live. If you want to find out how many people live in each city, there are several methods you can choose. One method is to create a PivotTable. To create a PivotTable on your data using Excel 97, follow these steps:

1. Select a cell within your data table.
2. Choose the PivotTable Report option from the Data menu. Excel begins the PivotTable Wizard.
3. Choose the Microsoft Excel List or Database option and click on Next.
4. In the Range box, make sure your entire data table is selected, then click on Next.
5. Drag the City field button to the Row area of the PivotTable builder.
6. Drag the Name field button to the Data area of the PivotTable builder.
7. Click on Finish. A new worksheet is created that contains your PivotTable.

If you are using Excel 2000, Excel 2002, or Excel 2003, the steps are a bit different:

1. Select a cell within your data table.
2. Choose the PivotTable and PivotChart Report option from the Data menu. Excel begins the PivotTable and PivotChart Wizard.
3. Choose the Microsoft Excel List or Database option.
4. Indicate you want to create a PivotTable, then click on Next.
5. In the Range box, make sure your entire data table is selected, then click on Next.
6. Indicate you want to put the PivotTable in a new worksheet, then click on Finish. Excel creates the bare-bones PivotTable worksheet, and the PivotTable Field List dialog box is visible.
7. Drag the City field button from the PivotTable dialog box to the Row area of the PivotTable.
8. Drag the Name field button from the PivotTable dialog box to the Data area of the PivotTable. Your PivotTable is complete.

The steps are different still in Excel 2007:

1. Select a cell within your data table.
2. Make sure the Insert tab of the ribbon is displayed.
3. In the Tables group, click the PivotTable tool.
4. Excel displays the Create PivotTable dialog box.
5. In the Range box, make sure your entire data table is selected, then click on OK. Excel creates an empty PivotTable worksheet.
6. Drag the City field from the field list to the Row Labels area.
7. Drag the Name field from the field list to the Values area. Your PivotTable is complete.

We found this tip on a great website called excel.tips.net. Find out more here!

Originally posted 2008-10-16 09:25:44.