Table of Contents
To calculate the mode in an Excel pivot table, select the field containing the data, then select the “Value Field Settings” option. In the Value Field Settings window, select “Summarize Values By” and select “Mode” from the drop-down menu. Finally, click the OK button to apply the mode calculation. The pivot table will now display the mode value for the selected field.
Often you may want to calculate the mode in an Excel pivot table.
Unfortunately Excel doesn’t have a built-in feature to calculate the mode, but you can use a MODE IF function as a workaround.
The following step-by-step example shows how to do so.
Related:
Step 1: Enter the Data
First, let’s enter the following data that shows the points scored by basketball players on various teams:
Step 2: Calculate the Mode by Group
Next, we can use the following formula to calculate the mode for the points value for each team:
=MODE(IF($A$2:$B$13=A2,$B$2:$B$13))
The following screenshot shows how to use this formula in practice:
Step 3: Create the Pivot Table
To create a pivot table, click the Insert tab along the top ribbon and then click the PivotTable icon:
In the new window that appears, choose A1:C13 as the range and choose to place the pivot table in cell E1 of the existing worksheet:
Drag the Team field to the Rows box, then drag the Points and Mode of Points fields to the Values box:
Next, click the Sum of Mode Points dropdown arrow and then click Value Field Settings:
In the new window that appears, change the Custom Name to Mode Pts and then click Average as the summarize value:
Once you click OK, the mode number of points scored for each team will be added to the pivot table:
The pivot table now contains the following information:
- Each unique team name
- The sum of points scored by each team
- The mode number of points scored by each team