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

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: