The following step-by-step example shows how to calculate a weighted average within a pivot table in Excel.

**Step 1: Enter the Data**

First, let’s enter the following dataset that contains information about basketball players on two different teams:

**Step 2: Create Helper Column**

Suppose we would like to create a pivot table that summarizes the sum of games for each team along with the average points scored per player on each team.

To calculate the average points scored per player, we will need to use a weighted average that takes into account the average points along with the total games.

Since pivot tables in Excel don’t allow you to calculate weighted averages, we will need to first create a helper column in our original dataset.

We can type the following formula into cell **E2**:

=C2*D2

We can then drag and fill this formula down to the remaining cells in column E:

**Step 3: Create the Pivot Table**

To create the pivot table, we’ll highlight the cells in the range **A1:E9**, then click the **PivotTable** icon within the **Insert** tab along the top ribbon.

In the PivotTable fields panel that appears, we’ll drag **Team** to the rows box and **Games** to the Values box:

The following pivot table will appear:

**Step 4: Add Weighted Average Column to Pivot Table**

To add a weighted average column that shows the average points per game per player for each team, click any cell in the pivot table, then click the icon called **Fields, Items, & Sets** within the **PivotTable Analyze** tab, then click **Calculated Field**:

In the new window that appears, type **= Helper / Games** in the Formula box, then click **OK**:

A new column that shows the average points per game per player for each team will be added to the pivot table:

We can confirm that the average points per game per player is correct by manually calculating it from the original dataset.

For example, we could calculate the average points per game per player for team A as:

Avg Points per Game per Player: (22*3 + 20*4 + 15*4 + 13*5) / (3+4+4+5) = **16.9375**

This matches the value that appears in the pivot table.

**Additional Resources**

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

How to Create Tables in Excel

How to Group Values in Pivot Table by Range in Excel

How to Group by Month and Year in Pivot Table in Excel