How to Calculate Weighted Average in Excel Pivot Table

We have a dataset containing different grocery items and their sale details. We will calculate the weighted average price for each item in a Pivot Table.

Dataset Introduction

Step 1 – Adding a Helper Column

Add a column Sales Amount in the above table.
Use the following formula in the first cell of this new column.

=D5*E5

Hit Enter.

Adding Extra Column

Use the Fill Handle (+) tool to copy the formula to the rest of the column.

Adding Extra Column

You will get the following result.

Step 2 – Creating the Excel Pivot Table

Click on any cell of the dataset (B4:F14).

Creating Excel Pivot Table

Go to Insert and choose Pivot Table, then select From Table/Range.

Creating Excel Pivot Table

The PivotTable from table or range window will pop up. If your Table/Range field is correct, press OK.

Creating Excel Pivot Table

The Pivot Table is created in a new sheet. Choose the PivotTable Fields as in the screenshot.

Creating Excel Pivot Table

You will get the following Pivot Table.

Step 3 – Analyzing the Weighted Average in an Excel Pivot Table

Select the Pivot Table.
Go to Pivot Table Analyze.
Choose Field, Items, & Sets and select Calculated Field.

Analyzing Weighted Average Excel Pivot Table

The Insert Calculated Field window will show up.
Type Weighted Average in the Name field.
We have divided the helper column by weight (Sales Amount/Weight) to get the weighted average.
Click on OK.