How to Count Entries on a Pivot Table

Pivot tables are spreadsheet tools used to summarize a larger data table into a smaller one. In Excel, the default is to sum the data in each requested field combination. Suppose that you would rather count the frequency of data in a field combination instead of sum the numbers. Pivot tables allow you to do this for both discrete and grouped continuous data. You can count the number of times a certain value appears in the data, and even count frequency of occurrence within user-defined ranges.

Instructions

1

Define a table of purchase data--serving as a pivot tables source--as follows. The underscores are used merely to maintain spacing and alignment.
Day of Purchase__Price___
Sunday_________5
Sunday_________10
Sunday_________7
Sunday_________5
Monday________11
Monday________10
Tuesday________7
Tuesday________10
Tuesday________11
Tuesday________6
Wednesday______3
Wednesday______10
Wednesday______11
The default is for the pivot table to return a table summing the purchase prices:
Day of Purchase__Price___
Sunday__________27
Monday__________21
Tuesday_________34
Wednesday_______24
But instead youd like to change the pivot table to give you the frequency of purchases. Then go to the next step.

2

Click on the "Pivot Table Wizard" button in the Pivot Table toolbar. Click the "Layout" button. Double-click on the "Price" field button. Select Count. Click OK, OK again, and then Finish. The pivot table will change to a frequency table of purchase counts. But suppose you want the frequency of purchase prices and dont care anymore about day of purchase. Then go to the next step.

3

Click on the "Pivot Table Wizard" button in the Pivot Table toolbar. Click the "Layout" button. Remove the Day of Purchase field from the Row area. Drag Price from the far right of the window over to the Row area. Now you have Price in both the left Row region serving as a row label, and in the Data region. Click OK, OK again, and then Finish. The resulting pivot table will look like this:
Price___Price Count
3_________1
5_________2
6_________1
7_________2
10________4
11________3
Suppose that your data isnt this discrete and you need to group the data some before the price counts will go above 1. Then go to the next step.

4

Group data by selecting a cell in the column to be grouped and right-click your mouse. A menu will pop up. Select Group and Outline, and then Group. Select the width you want each interval to have in the By: field. Select OK. The pivot table will now have collapsed into a smaller, more summarized table than before. If an interval has no data, the pivot table wont show it.