Excel PivotTables do not have a built-in Weighted Average summary option. The dependable approach is to add a row-level value-times-weight column to the source data, summarize that column and the weights as sums, then divide the two sums; for a PivotTable built on the Data Model, you can instead create a weighted-average measure.
What a weighted average means
A regular average gives every observation equal influence. A weighted average gives each observation influence in proportion to its weight. The calculation is the sum of each value multiplied by its weight, divided by the sum of the weights. For example, an average rating based on 100 responses should usually count more than a rating based on only two responses.
Suppose a report contains three orders with unit prices of $90, $80, and $100, and quantities of 2, 3, and 5. The weighted average price is ($90 × 2 + $80 × 3 + $100 × 5) ÷ (2 + 3 + 5), or $92. A simple average of the three prices is $90. The two figures answer different questions: the simple average treats each order line equally, while the weighted average treats each unit equally.
Weighted averages are useful for rates, prices, scores, percentages, and other measures where rows represent different amounts of activity. The weight might be units sold, number of survey responses, hours worked, or another meaningful quantity. Choosing an appropriate weight is a business or analytical decision, not something Excel can infer automatically.
Why a PivotTable cannot just average the values
A standard PivotTable can summarize a numeric field by Sum, Count, Average, and other options. Its Average summary calculates the arithmetic mean of the records in each group. It does not automatically use a second field as the weight. If you put Rate in Values and choose Average, Excel gives each source row equal influence, even if one row represents one item and another represents a thousand.
This difference matters when you group the data. Suppose one region has a 10% rate on 10 transactions and another has a 20% rate on 1,000 transactions. Averaging the two regional percentages gives 15%, but combining all transactions gives a rate much closer to 20%. Averaging group averages is generally not a valid way to combine groups of different sizes. The correct calculation needs the underlying numerator and denominator, or an equivalent weighted calculation.
The PivotTable’s built-in Average option is still right when every source row should count equally. Use a weighted average only when the weights represent the relative contribution you intend to measure.
Method 1: Add a helper column to the source data
This method works in Excel for Microsoft 365 on Windows and is also a practical choice in Excel for Mac and Excel for the web. It keeps the calculation visible in the source and lets a regular PivotTable summarize the necessary totals.
- Arrange the source data with one record per row and clear headers. For example, use Region in column A, Item in column B, Rate in column C, and Weight in column D.
- Add a column named Weighted total. With the first data record in row 2, the layout is Rate in C2 and Weight in D2. Enter
=C2*D2in E2. For a row with a rate of 90 and weight of 2, the result is 180. - Fill the formula down through all records. If the source is an Excel Table, entering the formula in the new column generally fills the column automatically and extends it for added rows.
- Create a PivotTable from the source using Insert > PivotTable. Put the fields you want to report by, such as Region, in Rows. Put Weighted total and Weight in Values, and make sure both are summarized by Sum rather than Count.
- Divide the displayed sum of Weighted total by the displayed sum of Weight for each PivotTable group. Label the resulting values Weighted average and format them appropriately, such as as percentages or currency.
For example, if a PivotTable row has Sum of Weighted total in G5 and Sum of Weight in H5, the layout assumption is those two displayed totals are in G5 and H5. Enter =IFERROR(G5/H5,"") beside that row. If the totals are 920 and 10, the result is 92. IFERROR returns a blank when the denominator is zero or another error occurs; you can replace the empty text with a message if you prefer.
Keep the calculation beside the PivotTable rather than trying to type into its value cells, which Excel controls. If PivotTable rows can expand or contract, a formula tied to a particular row address may stop matching the intended group after refresh. For a report that needs to remain robust, use a Data Model measure or carefully reference the PivotTable output, and verify the results after refresh.
Method 2: Create a measure in the Data Model
A measure calculates the ratio from the records currently included in the PivotTable’s filter context. This is convenient when users will filter by region, product, month, or other fields and expect the weighted average to recalculate for each selection. It also avoids adding a separate division formula beside the PivotTable.
- Start from a clean data table with numeric Rate and Weight columns. Select the data and choose Insert > PivotTable. In the Create PivotTable dialog, select Add this data to the Data Model.
- Build the PivotTable using fields from the model. If the Power Pivot tab is available, use Power Pivot > Measures > New Measure. Depending on the ribbon layout, the Measures command may be grouped under Calculations.
- Give the measure a name such as Weighted Average. In its formula box, enter a DAX measure expression. Assuming the model table is named Sales and it contains Rate and Weight columns, use
=DIVIDE(SUMX(Sales, Sales[Rate]*Sales[Weight]),SUM(Sales[Weight])). - Set the measure’s number format to Percentage, Currency, or the suitable format for the data, then add the measure to the PivotTable’s Values area.
This is a DAX measure, not a worksheet-cell formula. SUMX evaluates Rate multiplied by Weight for each row in the current filter context, and SUM adds the weights in that same context. DIVIDE handles a zero denominator safely by returning blank by default. With the three example rows from above, the measure returns 92. Because filters change the records in context, the measure recalculates for each PivotTable group and slicer selection.
Power Pivot authoring and Data Model features are not equally available across platforms. On Windows Microsoft 365, Power Pivot may need to be enabled through File > Options > Add-ins, then selecting COM Add-ins and choosing Go. Excel for Mac and Excel for the web do not provide the same Power Pivot measure-authoring workflow, so the helper-column method is the more portable choice.
Choosing between the two methods
- Choose a helper column when you want a straightforward workbook, need compatibility across Excel platforms, or want to inspect the row-level weighted totals directly.
- Choose a Data Model measure when the report has many dimensions or slicers, must recalculate cleanly across different PivotTable layouts, and will be maintained in desktop Excel for Windows with Power Pivot support.
- Choose a separate formula beside the PivotTable for a small, fixed report when you are comfortable checking that the formula continues to align with the correct PivotTable row after refresh.
A helper column stores an intermediate result for every source row, while a measure computes the result at report time. Neither approach changes the underlying meaning of the weighted average. The important point is that the denominator and numerator must cover the same set of records and respond to the same filters.
Data checks that prevent misleading results
Before building the PivotTable, decide what each row represents and whether the weight is valid for that record. A weighted average can be mathematically calculated from bad or inconsistent inputs, so check the source rather than assuming that a plausible-looking result is correct.
- Check blanks and text. Confirm Rate and Weight are numeric where expected. A blank weight should not silently stand for a meaningful zero unless that is the intended business rule. Text values may be ignored or treated unexpectedly in calculations.
- Decide how to handle zero weights. A zero-weight row contributes nothing to the numerator or denominator. If all weights in a group are zero, the weighted average is undefined; return a blank or an explicit message rather than interpreting it as zero.
- Review negative weights. Negative weights can make a ratio mathematically possible but often do not make sense for quantities or response counts. Investigate them instead of automatically including them.
- Use consistent units. Do not combine weights measured in different units without converting them first. Likewise, ensure rates use the same scale: 0.15 and 15% can represent the same rate in Excel, but the number 15 may mean something different.
- Check the intended grain. If one source row is already an aggregate of many records, its weight should normally reflect the number of underlying observations or another suitable exposure measure. A count of rows may not be the right weight.
It is also useful to validate one group manually. Filter the source to a small set of records, multiply each value by its weight, add those products, and divide by the total weight. The result should agree with the PivotTable or measure. This simple check can reveal a wrong field, a missing row, or a PivotTable value accidentally summarized by Count.
Refreshes, filtering, and report maintenance
After changing source records, refresh the PivotTable with PivotTable Analyze > Refresh, or use Refresh All when multiple connections or PivotTables need updating. A PivotTable based on an Excel Table is usually easier to maintain because the table expands when records are added; still, confirm the new records are within the source and refresh the report.
With the helper-column approach, the PivotTable’s sums update after refresh, but a separate formula next to the PivotTable may need to be copied or repositioned if the number of displayed groups changes. Avoid hard-coding a formula that assumes a particular region will always occupy a particular row. For recurring reports, test the process by adding a record and a new group, refreshing, and checking both the calculated result and its label.
A Data Model measure is naturally evaluated for each group and filter combination in the PivotTable. That makes it less dependent on the shape of the displayed report. But it does not remove the need to validate the source columns, refresh the model, and confirm that filters include the records you intend to analyze.
Frequently Asked Questions
Can I add Weighted Average to the PivotTable value field settings?
Not as a standard built-in summary option. The usual Average setting is an unweighted mean. Add a helper column and divide its sum by the sum of the weights, or create a Data Model measure to calculate the ratio in the PivotTable.
Can a calculated field make a weighted average?
A standard PivotTable calculated field is not a dependable substitute for a row-level weighted average. It operates within PivotTable calculation rules and does not simply multiply every source row’s value by its corresponding weight before aggregation. Use a source helper column or a Data Model measure instead.
How do I calculate a weighted average by category?
Put the category field in Rows, then calculate the sum of value-times-weight divided by the sum of weight for each category. With a measure, the PivotTable applies the category filter automatically. With a helper column, include both sums and divide the matching totals for each displayed category.
Why does my weighted average equal the regular average?
That can happen when all weights are equal, because equal weights give every row the same influence. It can also signal that the report is using Average of the original value field rather than the weighted calculation. Check the weights and the Values area, then verify one category by hand.
Should I average percentages or combine their counts?
When percentages are based on different numbers of observations, combine their underlying numerators and denominators, or weight each percentage by its relevant count. A plain average is appropriate only when each percentage should have equal influence. The correct denominator depends on what the percentage measures.
Bottom line
Excel PivotTables do not directly offer a weighted-average aggregation, but the calculation is simple once the numerator and denominator are made explicit. For broad compatibility, multiply each row’s value by its weight, sum those products and the weights in the PivotTable, and divide the sums. For a filter-responsive report in the Data Model, use a DAX measure. In either case, confirm that the weights, source grain, and treatment of zero or missing values match the question your report is meant to answer.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Funnel Chart in Excel: Create, Format, and Fix OneTo create a funnel chart in Microsoft Excel for Microsoft 365 on Windows, put your process stages in…
- How to Make a Chart in Excel From a Table: Steps That WorkTo make a chart from a table in Excel, click a cell in the table, choose Insert >…
- Think-Cell Waterfall Chart: How It Works and When to Use ItA think-cell waterfall chart is a PowerPoint chart that shows how a sequence of positive and negative changes…
- Excel Dashboard Visualization: How to Design and BuildExcel dashboard visualization means presenting important measures and trends in a compact, interactive worksheet using elements such as…
- Excel Pivot Table: Multiple Rows at the Same LevelYes. In Excel for Microsoft 365 on Windows, add each field to the PivotTable Rows area, then choose…
- Automatic Pareto Chart in Excel: Make One That UpdatesTo make an automatic Pareto chart in Excel for Microsoft 365 on Windows, select a single column of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.