In this article, I will discuss how to **calculate weighted average** in Excel **Pivot Table**. Finding weighted average in **Pivot Table** is a bit complex. Usually, in an Excel worksheet you can apply functions to find weighted averages. On the other hand, you cannot apply excel functions in a **Pivot Table**. So, in this case, we have to apply an alternative technique.

**Table of Contents**hide

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Easy Method to Find Weighted Average in Excel Pivot Table**

**Calculate Weighted Average in Excel Pivot Table by Adding an Extra Column (Helper Column)**

The weighted average is considered as the average where weight is determined for each quantity required to average. This type of average calculation helps us determine the relative importance of each amount on average. A weighted average can be considered more accurate than any general average as all the numbers in the data set are assigned to the same weight.

Basically, we calculate the weighted average in Excel using the combination of **the SUMPRODUCT function** along with** the SUM function**. However, in this method, we will use an alternative way as functions cannot be used in **Pivot Table**. So, we will add an extra column to the **Pivot Table **source data and thus calculate weighted averages.

**Dataset Introduction**

For example, we have a dataset containing different grocery items’ date-wise sales. Now, I will calculate the **weighted average price** for each of the grocery items in a **Pivot Table**.

So, here are the steps associated with the process.

**Step 1: Adding Extra Column**

- First, add an extra column (helper column), ‘
**Sales Amount**’ in the above table. Next, type the below formula in the first cell of this new column.

`=D5*E5`

- Now, you will get the below result. Then, use the
**Fill Handle**(**+**) tool to copy the formula to the rest of the column.

- As a result, you will get the following result.

**Step 2: Creating Excel Pivot Table**

- Initially, click on a cell of the dataset (
**B4:F14**) to create the**Pivot Table**.

- Next, go to
**Insert**>**Pivot Table**>**From Table/Range.**

- Then, the ‘
**PivotTable from table or range**’ window will show up. Now, if your ‘**Table/Range’**field is correct, press**OK**.

- After that, the
**Pivot Table**is created on a new sheet. Later, choose the**PivotTable Fields**as the below screenshot.

- As a consequence, you will get the following
**Pivot Table**.

**Step 3: Analyzing Weighted Average Excel Pivot Table**

- First, select on the
**Pivot Table**. - After that, go to the
**Pivot Table Analyze**>**Field, Items, & Sets**>**Calculated Field**.

- Subsequently, the
**Insert Calculated Field**window will show up. - Now, type ‘
**Weighted Average**’ on the**Name**field. - Then, we have divided the helper column by weight (
**Sales Amount/Weight**) to get the weighted average. - Next, click on
**OK**.

- Finally, we got the weighted average price for each of the grocery items in the
**subtotal**rows of our**Pivot Table**.

**Read More: Calculate Conditional Weighted Average with Multiple Conditions in Excel**

**Conclusion**

In the above article, I have tried to discuss the weighted average calculation method in **Pivot Table** elaborately. Besides, this method is very simple. Hopefully, the explanations will help you to find weighted averages in Pivot Tables. Please let me know if you have any queries regarding the article.