As we work with Excel, we may want to calculate the mode (most frequently occurring value) in a Pivot Table. This tutorial shows how to calculate the mode in a Pivot Table in Excel.

## How to Calculate the Mode in a Pivot Table

Let’s consider the following dataset showing the number of goals scored by respective football clubs in various seasons.

We use the following steps to calculate the mode of goals scored by each football club in a Pivot Table:

### Step #1: Compute the Mode by Group

Excel has no built-in feature for calculating the mode, but we can use a formula combining the **MODE **and **IF** functions as a workaround.

We proceed as follows:

- Select cell C1 and enter the following formula:

1 |
=MODE(IF($A$2:$B$17=B2,$B$2:$B$17)) |

- Drag or double-click the Fill Handle to copy the formula down the column.

### Step #2: Create the Pivot Table

Use the steps below to create the Pivot table:

- Click
**Insert >> Tables >> PivotTable >> From Table/Range**.

- On the
**PivotTable from table or range**dialog box, use the range selector on the**Table/Range**box to select the entire dataset in cell range A1:C17. Next, click the range selector on the**Location**box to select cell E1, and click OK.

The **PivotTable Fields** pane appears on the right of the Excel window.

- On the PivotTable Fields pane, drag the
**Football Club**field to the**Rows**box, then drag the**Goals**and**Mode of Goals**fields to the**Values**box.

- On the
**Rows**box, click the down arrow next to**Sum of Mode of Goals**and choose**Value Field Settings**.

- On the
**Value Field Settings**dialog box, change the Custom Name to**Mode Goals**, select**Average**as the summary value, and click OK.

The Pivot Table shows each unique football club, the sum of goals scored by each club, and the mode of goals scored by each club.

## Conclusion

This tutorial showed how to calculate the mode in a Pivot Table. We hope you found the tutorial helpful.