How to Count Entries on a Pivot Table
By Paul Dohrman
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.
Define a table of purchase data--serving as a pivot table’s source--as follows. The underscores are used merely to maintain spacing and alignment. Day of PurchasePrice Sunday__5 Sunday_10 Sunday_7 Sunday_5 Monday_11 Monday__10 Tuesday__7 Tuesday__10 Tuesday__11 Tuesday__6 Wednesday__3 Wednesday_10 Wednesday11 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 you’d like to change the pivot table to give you the frequency of purchases. Then go to the next step.
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 don’t care anymore about day of purchase. Then go to the next step.
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: PricePrice Count 3__1 5_2 6_1 7_2 10__4 11__3 Suppose that your data isn’t this discrete and you need to group the data some before the price counts will go above 1. Then go to the next step.
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 won’t show it.
Paul Dohrman's academic background is in physics and economics. He has professional experience as an educator, mortgage consultant, and casualty actuary. His interests include development economics, technology-based charities, and angel investing.