Excel Power Query Weighted Average: How to Calculate

How-to basics · Updated November 22, 2026

To calculate a weighted average in Excel Power Query, you add a custom column that multiplies each value by its weight, then sum that column and divide by the sum of the weights. For example, with a “Score” column and a “Weight” column, add a column with [Score] * [Weight], then use the Sum button on the new column and the Weight column, and divide the two totals in a final step.

Why Power Query for Weighted Averages?

Power Query (Get & Transform Data) is Excel’s data preparation tool, available on the Data tab in Excel for Microsoft 365, Excel 2021, and earlier versions. It lets you import, clean, and reshape data before loading it into a worksheet. A weighted average is common in scenarios like grading (where assignments have different point values), finance (portfolio returns weighted by capital), or survey analysis (responses weighted by population). Doing this in Power Query keeps the calculation repeatable: when you refresh the query, the weighted average recalculates automatically, even if the source data changes.

Unlike a worksheet formula such as SUMPRODUCT, Power Query is ideal when your data is messy, comes from multiple sources, or needs to be transformed first (e.g., unpivoting, filtering, or grouping). Once you build the query, you can also load the result to a worksheet or data model, and refresh it with one click.

Step-by-Step: Weighted Average in Power Query

Assume you have a table named “Grades” with columns: Assignment, Score, and Weight (weights as decimals or percentages, e.g., 0.2, 0.3, 0.5). Here’s how to compute the weighted average:

  1. Select any cell in the table, then go to Data > From Table/Range (or Data > Get & Transform Data > From Table/Range). The Power Query editor opens.
  2. With the query selected, go to Add Column > Custom Column.
  3. In the Custom Column dialog, name it WeightedScore and enter the formula: [Score] * [Weight]. Click OK.
  4. Now select both the WeightedScore column and the Weight column (hold Ctrl to select multiple), then go to Transform > Statistics > Sum. This adds a new row with the totals. (Alternatively, use the dropdown arrow on each column header and choose Aggregate > Sum.)
  5. You now have two totals in the same row. Add another custom column: WeightedAverage with formula [Sum of WeightedScore] / [Sum of Weight].
  6. To return to a single row, remove other columns if needed, then click Home > Close & Load to load the result to a worksheet.

If your weights are percentages that sum to 100 (e.g., 20, 30, 50), you can either divide them by 100 in the formula or use them as-is if you also divide the final result by 100. The key is consistency: the weighted average formula is sum(value * weight) / sum(weight).

Alternative: Using the Group By Feature

If your data has categories (e.g., students, products) and you need a weighted average per group, you can use Group By instead of manual steps. In Power Query, go to Home > Group By. In the dialog, choose the grouping column (e.g., Student ID), then add two aggregations: one for Sum of WeightedScore (using the custom column you created) and one for Sum of Weight. After grouping, add a custom column to divide the two sums. This avoids the manual sum steps and handles multiple groups at once.

Common Pitfalls and How to Avoid Them

  • Weights that don’t sum to 1 or 100: The formula still works—the division by total weight normalizes it. But if your weights are meant to sum to 1 and they don’t, double-check your data; the result will be biased.
  • Null or missing weights: If any weight is null, the multiplication yields null, and the sum will ignore it, which can skew results. Use Replace Values to set nulls to 0 or filter them out.
  • Text or errors in numeric columns: Power Query may treat a column as text if it has mixed types. Use Transform > Data Type to set Score and Weight to Decimal Number before calculating.
  • Accidentally summing the wrong columns: When you use the Sum button on multiple columns, Power Query creates separate sum rows. Make sure you only keep the row that contains both sums.

Power Query vs. Worksheet Formulas (SUMPRODUCT)

In a worksheet, you can calculate a weighted average with =SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10) where B contains values and C contains weights. This is simpler for one-off calculations. However, Power Query shines when:

  • Your data is external (e.g., CSV, database, web) and you want to automate the refresh.
  • You need to clean or reshape data before the calculation (e.g., unpivot a matrix).
  • You want to avoid volatile formulas or manual updates.

For a single static range, SUMPRODUCT is faster to set up. For repeatable, source-connected analysis, Power Query is more maintainable. You can also combine both: load the Power Query result to a worksheet and reference it in a formula if needed.

Refreshing and Automating the Calculation

Once your query is loaded, you can refresh it by right-clicking the table in the worksheet and choosing Refresh, or by going to Data > Refresh All. If your source data changes, the weighted average updates automatically. To change the calculation (e.g., add a new weight), edit the query by going to Data > Queries & Connections, double-click the query, and modify the steps. The query steps are recorded, so you can reorder or adjust them.

Frequently Asked Questions

Q: Can I use a weighted average with percentages in Power Query?
Yes. If your weights are percentages (e.g., 20% stored as 0.2), the formula [Score] * [Weight] works directly. If they are whole numbers like 20, you can divide by 100 in the custom column: [Score] * ([Weight] / 100), or just treat them as relative weights—the division by sum of weights normalizes it.

Q: Why does my weighted average show as a decimal? How do I format it as a percentage?
Power Query returns a decimal. After loading to Excel, select the column and apply a percentage format (Home > Number > %). If you need it as a percentage in Power Query, you can multiply by 100 and set the data type to Percentage, but it’s often easier to format in the worksheet.

Q: What if my weights sum to zero?
Division by zero returns an error in Power Query. You can add a conditional column to check if the sum of weights is zero and return null or 0. For example, add a custom column with if [Sum of Weight] = 0 then null else [Sum of WeightedScore] / [Sum of Weight].

Q: Can I do a weighted average without adding a custom column?
Yes, you can use the Group By feature with an aggregated expression. In the Group By dialog, after selecting your grouping column, click Add aggregation, choose Sum as the operation, and enter the expression [Score] * [Weight] as the column. That gives you the sum of weighted scores directly, then you divide by the sum of weights in a subsequent step.

Q: Does this work in Excel for Mac?
Yes, Power Query is available in Excel for Microsoft 365 on Mac, but the menu paths differ slightly. On Mac, go to Data > Get Data > From Table/Range (or use the Query Editor). The steps are the same, but keyboard shortcuts may differ (e.g., Ctrl on Windows vs. Cmd on Mac). The Power Query interface is essentially identical.

Q: How do I handle multiple weighted averages for different groups?
Use the Group By method described above. Group by your category column, sum the weighted scores and weights, then divide. This gives you a row per group with the weighted average for each.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.