Counting with PivotTables

by Allen Wyatt (last updated May 9, 2015)

Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting with PivotTables.

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, follow these steps:

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. (See Figure 1.)



Figure 1. 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.

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!