- MS Excel Basics
- Excel - Home
- Excel - Getting Started
- Excel - Explore Window
- Excel - Backstage
- Excel - Entering Values
- Excel - Move Around
- Excel - Save Workbook
- Excel - Create Worksheet
- Excel - Copy Worksheet
- Excel - Hiding Worksheet
- Excel - Delete Worksheet
- Excel - Close Workbook
- Excel - Open Workbook
- Excel - Context Help

- Editing Worksheet
- Excel - Insert Data
- Excel - Select Data
- Excel - Delete Data
- Excel - Move Data
- Excel - Rows & Columns
- Excel - Copy & Paste
- Excel - Find & Replace
- Excel - Spell Check
- Excel - Zoom In-Out
- Excel - Special Symbols
- Excel - Insert Comments
- Excel - Add Text Box
- Excel - Undo Changes

- Formatting Cells
- Excel - Setting Cell Type
- Excel - Setting Fonts
- Excel - Text Decoration
- Excel - Rotate Cells
- Excel - Setting Colors
- Excel - Text Alignments
- Excel - Merge & Wrap
- Excel - Borders and Shades
- Excel - Apply Formatting

- Formatting Worksheets
- Excel - Sheet Options
- Excel - Adjust Margins
- Excel - Page Orientation
- Excel - Header and Footer
- Excel - Insert Page Breaks
- Excel - Set Background
- Excel - Freeze Panes
- Excel - Conditional Format

- Working with Formula
- Excel - Creating Formulas
- Excel - Copying Formulas
- Excel - Formula Reference
- Excel - Using Functions
- Excel - Builtin Functions

- Advanced Operations
- Excel - Data Filtering
- Excel - Data Sorting
- Excel - Using Ranges
- Excel - Data Validation
- Excel - Using Styles
- Excel - Using Themes
- Excel - Using Templates
- Excel - Using Macros
- Excel - Adding Graphics
- Excel - Cross Referencing
- Excel - Printing Worksheets
- Excel - Email Workbooks
- Excel- Translate Worksheet
- Excel - Workbook Security
- Excel - Data Tables
- Excel - Pivot Tables
- Excel - Simple Charts
- Excel - Pivot Charts
- Excel - Keyboard Shortcuts

- MS Excel Resources
- Excel - Quick Guide
- Excel - Useful Resources
- Excel - Discussion

# How to calculate weighted average in an Excel Pivot Table?

Using a combination of Excel's SUMPRODUCT and SUM functions, the weighted average may be quickly and easily calculated. However, it appears that the functions in a pivot table are not supported by the calculated fields. As a result, how exactly would one go about calculating the weighted average in a pivot table? This article will present a solution to the problem.

## Calculating Weighted Average in an Excel Pivot Table

Let’s understand step by step with an example.

### Step 1

In the first step, we must have a sample data for creating pivot table as shown in the below screenshot for the same.

### Step 2

Now, select the data range A1:D10. Click on the Insert tab on the toolbar ribbon and then select Pivot table option to insert pivot table for the selected data range. Refer to the below screenshot for the same.

### Step 3

In the next step, create pivot table window appears, make sure the data range is selected as A1:D10 under select table/range option. Now, choose new worksheet to create the pivot table in separate sheet then click on OK button. Below screenshot for the same.

### Step 4

The pivot table is now created in separate worksheet as shown in the below screenshot for same.

### Step 5

In the Pivot table fields check flavor, cost and weight. After those values appear. Please refer to the below screenshot for the same.

In order to demonstrate how to calculate the weighted average price of each flavor in the pivot table, I will use the pivot table as an example.

### Step 6

Next, add an Average helper column to the source data.

Create a new, empty column in the source data, name it Average, enter the below formula in the first cell of this helper column, and then drag the AutoFill handle to fill the entire column. Please check out the below screenshot for the same.

=D2*E2

### Step 7

To access the PivotTable Tools, choose any cell in the pivot table, and then click Pivot Analyze (or Options) then select Refresh option. Please refer to the below screenshot for the same.

### Step 8

After that, in pivot table tools select Analyze option and click Fields, items, & sets then choose calculated Field. Please check out the below screenshot.

### Step 9

Then, the Insert Calculated Field dialog box appeared, type Weighted Average in the Name box, and in the Formula box type =Average/Weight (please change the formula based on the names of your fields), and then click the OK button to close the dialog box. Please check out the screenshot below for the same.

### Step 10

Once you return to the pivot table at this point, you will see that the subtotal rows now contain the weighted average price of each flavor. Below screenshots for the same.

The final screen will appear as the one shown below −

## Conclusion

In this tutorial, we used a simple example to demonstrate how you can calculate weighted average in an Excel pivot table.

- Related Articles
- How to alternate row colour in an Excel Pivot Table?
- How to Automatically Refresh a Pivot Table in Excel?
- How to calculate the moving/rolling average in an Excel?
- How to add average/grand total line in a pivot chart in Excel
- How to Add Percentage of Grand Total/Subtotal Column in an Excel Pivot Table?
- How to add multiple fields into a pivot table in Excel?
- How to calculate running total /average in Excel?
- How to add a calculated field to a pivot table in Excel?
- How to calculate average between two dates in Excel?
- How to calculate average cells from different sheets in Excel?
- How to calculate the average rate of change in Excel?
- How to calculate weighted mean in R?
- How to calculate average/compound annual growth rate in the Excel?
- How to calculate average speed from distance and time in Excel?
- How to calculate average without max and min values in Excel?