To calculate a weighted average in an Excel PivotTable, add a source-data helper column that multiplies each value by its weight, then divide the PivotTable’s sum of those products by its sum of weights. For example, for a quantity-weighted average price, sum Unit Price × Quantity and divide by total Quantity; a standard PivotTable does not have a built-in Weighted Average summary option.
What a weighted average means
A weighted average gives more influence to observations with larger weights. The calculation is the sum of each value multiplied by its weight, divided by the sum of the weights. In a sales report, the weight might be units sold, so an item sold 100 times has more effect on the average selling price than an item sold once.
This differs from a simple average, which gives every row equal influence. If you average transaction prices of $12, $10, and $15, the simple average is $12.33. But if those transactions represent 2, 5, and 3 units, respectively, the quantity-weighted average is $11.90. The larger transaction at $10 pulls the result down because it represents more units.
PivotTables can summarize a field by sum, count, average, and other standard calculations, but choosing Average for a price field usually averages the row values equally. It does not automatically use another field, such as quantity, as a weight. The reliable workaround is to summarize the numerator and denominator separately and divide them.
Recommended method: add a helper column
For a quantity-weighted average, create a column that calculates the value multiplied by the quantity on every source row. The PivotTable can then sum that column and the quantity column independently. This works for the whole data set and for each group in the PivotTable, such as product, region, month, or salesperson.
- Make sure the source data has one row per observation and includes the value to average and its weight. For example, use Unit Price and Quantity.
- Add a new source-data column with a clear heading, such as Extended Value or Price × Quantity.
- Calculate value times weight for each row, and fill the formula down through the data.
- Create or refresh the PivotTable, then add the helper column and the weight field to Values. Set both to Sum.
- Divide the summed helper-column result by the summed weight for the overall result or for each group.
Do not put the ordinary average of the source values in the PivotTable and expect it to become weighted. The helper column is what preserves the correct numerator when the PivotTable groups or filters the records.
Worked example with prices and quantities
Suppose a worksheet has Category in column A, Unit Price in column B, and Quantity in column C. The three records are North, $12, 2; North, $10, 5; and South, $15, 3. With Unit Price in B2:B4 and Quantity in C2:C4, enter this in D2 under the heading Extended Value:
=B2*C2
Copy the formula down. The resulting extended values are $24, $50, and $45. Their sum is $119, and the quantities sum to 10, so the overall weighted average price is $119 ÷ 10, or $11.90. The simple average of the three listed prices would instead be $12.33, which answers a different question.
For the North category, the weighted result is ($24 + $50) ÷ (2 + 5), or about $10.57. For South, it is $45 ÷ 3, or $15. The same two sums produce both the grand weighted average and the weighted average for each category.
Build the PivotTable in Excel for Microsoft 365
In Excel for Microsoft 365 on Windows, click inside the source range or Excel table and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK. If the data is a regular range, include the new helper column in the selected range. Using an Excel table helps keep the source range inclusive when rows are added.
In the PivotTable Fields pane, drag Category to Rows. Drag Extended Value and Quantity to Values. For each value field, open its field menu, choose Value Field Settings, and under Summarize Values By select Sum. If Excel has chosen Count, change it to Sum; a Count result is a warning to check whether the source values are stored as numbers or contain blanks or text.
Repeat this setup for any other grouping you need. For instance, put Month above Category in Rows, or add Region to Filters. Each displayed group will have a sum of Extended Value and a sum of Quantity. The weighted average for that group is the first sum divided by the second, not an average of the group-level averages.
On Mac, the standard Insert > PivotTable workflow and the helper-column method are also available, though some ribbon and pane details may vary by Excel release. Excel for the web supports many ordinary PivotTable tasks, but advanced Data Model and Power Pivot authoring is a Windows desktop option; use the helper-column approach when those tools are unavailable.
Calculate the result beside the PivotTable
A simple way to show the weighted average is to add a formula next to the PivotTable’s value columns. Suppose the PivotTable places Sum of Extended Value in D5 and Sum of Quantity in E5 for a particular row. With those two results in D5 and E5, calculate the weighted average in F5 with:
=D5/E5
For the worked example’s grand total, the inputs are 119 and 10, so the result is 11.9. Apply the appropriate number format, such as Currency with two decimal places, if the values represent prices. Format only the displayed result; do not round the source values or the helper-column calculations unless the business definition specifically requires rounding.
Copy the division down beside the category rows if you want a result for each category. Check that the formula on each row divides that row’s summed helper value by that row’s summed weight. Keep the grand total calculation separate if your PivotTable layout places its totals in a different row. A formula linked to fixed cell addresses can point to the wrong cells after a layout change, so verify the references whenever fields move.
Use a Data Model measure for a more integrated result
If you have Excel for Microsoft 365 on Windows and use the Data Model or Power Pivot, you can define the weighted average as a measure instead of displaying two sums and dividing beside the PivotTable. A measure recalculates under the PivotTable’s filters and grouping, which is useful for dashboards and reports with many categories.
For a table named Sales with numeric columns Unit Price and Quantity, a DAX measure expression is:
=DIVIDE(SUMX(Sales, Sales[Unit Price] * Sales[Quantity]), SUM(Sales[Quantity]))
In Power Pivot, create a measure, enter the expression, and add the measure to the PivotTable’s Values area. SUMX evaluates the price-times-quantity expression over the table rows, while SUM totals the quantities; DIVIDE returns the ratio. Ensure the table name and column names match the Data Model exactly. If total quantity can be zero, DIVIDE avoids an ordinary divide-by-zero error and returns blank unless an alternate result is specified. Power Pivot measure creation is not available in the same way on Excel for Mac, so the helper-column method is the practical cross-platform option.
Common problems and how to avoid them
- Using Average instead of Sum: The numerator must be the sum of value times weight, and the denominator must be the sum of weights. Summarizing either field by Average does not produce the desired ratio.
- Taking an average of group averages: A regional average cannot generally be averaged with another regional average unless the group weights happen to be equal. Recalculate from the summed numerators and denominators for the total.
- Missing or invalid weights: Blank, text, negative, or zero weights may be inappropriate depending on the data. Decide how each should be treated before reporting the result, and check that quantities or other weights are numeric.
- Wrong source range: If the helper column was added outside the original selected range, the PivotTable may not see it. Update the source or use an Excel table, then refresh the PivotTable.
- Misleading labels or number formats: Rename the summed helper field to something recognizable, and give the final ratio a meaningful label such as Weighted Average Price. A percentage, currency, and unit price need different formats.
- Unexpected Count: If Excel summarizes the helper field by Count, inspect the source for text values and confirm the entire helper range contains numeric results. Then choose Sum in Value Field Settings.
Refresh, filters, and checking the answer
After changing source data, refresh the PivotTable using right-click > Refresh, or use PivotTable Analyze > Refresh. If new rows are outside a fixed source range, refreshing alone may not include them; change the PivotTable data source or use an Excel table that expands with new records. If you edit the helper formula, make sure the formula is present for every source row before refreshing.
Filters affect both the numerator and denominator, which is normally what you want: the weighted average should describe the records currently included. To validate a report, compare the grand total against a direct calculation from the same filtered rows, and check a small group by hand. If the weighted average falls outside the range of valid source values, investigate the formula, weights, and filters.
Frequently Asked Questions
Can a PivotTable calculate a weighted average directly?
A standard PivotTable does not offer a built-in Weighted Average summary choice. Use a helper column and divide its sum by the sum of the weights, or create a Data Model measure in supported Windows editions.
Why is my PivotTable average different from the weighted average?
The Average summary usually gives each source row equal influence. A weighted average gives rows influence in proportion to their weights, such as units sold. Summing value times weight and dividing by total weight gives the weighted result.
Can I use the weighted average for each category?
Yes. Put the category in Rows and include the sum of the helper column and the sum of the weight in Values. Divide the two sums for each category; do not average the category averages to get an overall result.
Do I need to refresh after adding the helper column?
Yes, if the PivotTable already exists. Also check that its source range includes the new column. If the new column is outside the source, update the data source before refreshing.
What should I do when total weight is zero?
A ratio with a zero denominator is undefined. Check whether zero-weight rows belong in the analysis and whether any weights are missing or incorrectly entered. Do not replace the result with an arbitrary number.
Related
- Automatically Transpose Data in Excel: Methods That Work
- Average Date Calculation in Excel: Methods and Examples
- Auto-Update Calendar Excel Template: Setup and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Copy Data Validation Rules: How to Do ItTo copy only data validation in Google Sheets, copy a cell or range with the rule, select the…
- Excel Column Merge: Combine Cells or Join Text in Excel?In Excel, “merge columns” can mean either joining cells visually or combining the text they contain. To make…
- Excel Shortcut Keys to Highlight Cells to the End of DataIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+End to select from the active cell through the worksheet’s…
- 0 in Excel Conditional Formatting: 10 Practical RulesTo highlight zero values in Excel for Microsoft 365 on Windows, select the cells, then choose Home >…
- Where Is the Transpose Option in Excel?In Excel for Microsoft 365 on Windows, copy the cells you want to turn, select the destination’s top-left…
- Excel Undo Shortcut: How to Reverse Changes SafelyIn Excel for Microsoft 365 on Windows, press Ctrl+Z to undo your most recent action. On a Mac,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.