Excel Pivot Table Median Instead of Average

Yes—but a standard Excel PivotTable cannot choose Median in its usual Value Field Settings list. In Excel for Microsoft 365 on Windows, the most direct solution is to add the source data to the Data Model and create a DAX measure using MEDIAN; without the Data Model, calculate medians beside the PivotTable with a formula or use another summary method.

Why Median Is Missing from PivotTable Value Settings

When you drag a numeric field into a PivotTable’s Values area, Excel offers built-in summaries such as Sum, Count, Average, Maximum, Minimum, and standard deviation. Median is not one of the normal options. Changing the field’s number format or opening Value Field Settings does not add it, and renaming an Average field “Median” would only relabel the result, not change the calculation.

The distinction matters because a median is not generally recoverable from a sum, count, or average alone. A median requires the values to be ordered and the middle value or pair of middle values identified. A PivotTable’s ordinary aggregation choices do not retain that ordered distribution for a later median calculation.

Excel can still show medians in a PivotTable by using a measure in the Data Model. The measure is evaluated for the current PivotTable group: for example, it can return the median sales amount for each region, then recalculate for a selected year or product. This is different from adding a worksheet formula to a cell beside the PivotTable.

Create a Median Measure with the Data Model

First make sure your source data has one header row, no merged cells, and a numeric field to summarize. Converting the source range to an Excel Table is useful because the table can expand when new rows are added. Select a source cell and choose Insert > Table, confirming that the table has headers. Then create a PivotTable that includes the source in the Data Model:

Select a cell in the source table and choose Insert > PivotTable.
In the Create PivotTable dialog, select the option to add the data to the Data Model, then choose where to place the PivotTable.
After Excel creates the PivotTable, open the Power Pivot tab and choose Measures > New Measure. If the Power Pivot tab is missing, availability can depend on your Excel edition and add-in setup.
In the measure dialog, enter a name such as Median Amount. With an Excel Table named Sales and numeric values in its Amount column, enter this DAX expression in the formula box: =MEDIAN(Sales[Amount]).
Choose an appropriate number format, such as Currency, and confirm. In the PivotTable Fields pane, place the new measure in Values and add fields such as Region or Month to Rows or Columns.

The measure appears as a calculated value, usually marked with a calculator icon in the field list. You can use it like a value field: add or remove categories, apply report filters, and use slicers. The formula is DAX, the Data Model’s expression language, rather than an ordinary cell formula. Its table and column names must match the names in your model.

How the Median Measure Responds to Groups

A measure is calculated in the PivotTable’s current filter context. If Region is in Rows, the measure calculates a separate median for each region. If you add Year as a filter, each region’s result is recalculated using the rows that remain for the selected year. This makes a measure more flexible than calculating one fixed median for the entire source.

For a numeric column, DAX MEDIAN finds the middle value after ordering the eligible values. With an even number of values, it returns the average of the two middle values. Blank entries are not treated as numeric observations. Check that the source column really contains numbers: values imported as text, error values, or inconsistent entries can lead to confusing results or errors.

Be careful when interpreting totals. A grand-total median is calculated from the underlying rows included in the total; it is not the average of the medians displayed for each region. For example, if one region has 10 observations and another has 1,000, taking the simple average of their two medians would give each region equal weight. The median for all 1,010 observations instead comes from the combined set of observations. Those are different statistical questions.

A Small Example: Median Versus Average

Suppose the East region has four order amounts: 10, 20, 100, and 120. Its median is (20 + 100) ÷ 2, or 60. Its average is (10 + 20 + 100 + 120) ÷ 4, or 62.5. The two summaries are close in this example, but the median is less affected by unusually large or small values.

If the last order were 1,000 instead of 120, the East median would still be 60, while the average would rise to 282.5. The median better describes the middle order in a skewed set; the average reflects the total amount divided by the number of orders. Neither is universally “better.” If the question is typical order size in the presence of occasional unusually large sales, median may be informative. If the question is revenue per order and every dollar must be represented, average or total may be more relevant.

To compare categories fairly, also look at their sample sizes. A median based on three records can shift substantially when one more record is added, while a median based on thousands of records is usually more stable. A PivotTable can show a count alongside the median measure so readers can see how much data supports each group.

Choose Median or Average for the Question

Use the statistic that answers the business or research question, not just the one that looks less extreme. Median is the 50th percentile and is useful for skewed distributions, such as home prices, response times, or transaction sizes. Average is useful when the arithmetic mean is meaningful, particularly when totals and per-record contributions matter. A median does not preserve the total, so multiplying a median by the row count will not generally reproduce the sum.

Choose median when you want the middle observation and outliers would distort the average.
Choose average when you need the arithmetic mean or are reconciling a total across records.
Consider showing both when readers need to understand a skewed distribution.
Show a count as well when group sizes differ or some groups contain very few records.

Remember that median alone does not describe spread. Two groups can share a median while having very different ranges or variability. If the decision depends on consistency, include other descriptive statistics or inspect the distribution rather than treating the median as a complete summary.

Alternatives When You Cannot Use the Data Model

If your Excel setup does not offer the Data Model or you prefer to keep the PivotTable conventional, calculate the grouped median in cells next to it. In Microsoft 365 or Excel 2021, FILTER can select matching records and MEDIAN can summarize them. With categories in A2:A9, numeric amounts in B2:B9, and the category to summarize in D2, use:

=IFERROR(MEDIAN(FILTER($B$2:$B$9,$A$2:$A$9=D2)),"")

For example, if the East amounts in B2:B5 are 10, 20, 100, and 120, and A2:A5 contain East, this returns 60 when D2 contains East. IFERROR returns a blank if there are no matching numeric rows; you can replace the empty string with a message if that is clearer. FILTER is available in Microsoft 365 and Excel 2021. In older Excel versions, this particular formula is not available, so use a compatible array formula or calculate the grouped medians in Power Query or another tool.

This worksheet approach is not an actual PivotTable aggregation. It will not automatically become a value field when someone rearranges PivotTable fields, and the references must cover the relevant source rows. Structured Excel Table references or a well-managed dynamic range can make maintenance easier. You can also build a separate summary table of categories and formulas, but verify that it refreshes or expands when new source categories appear.

Common Problems and Refresh Considerations

No Power Pivot tab: Data Model and Power Pivot authoring features are not equally available in every Excel edition or platform. On Windows, check File > Options > Add-ins and the COM Add-ins list to see whether Microsoft Power Pivot for Excel is available to enable. If the add-in is not offered, use a worksheet formula or a different supported environment.
The measure is missing from the field list: Confirm that it was created for the same workbook Data Model used by the PivotTable, and check the table selected in the measure dialog. Reopen the field list if necessary.
The measure returns an error: Check the table and column names, confirm the column contains numeric values, and look for errors or text entries in the source. A measure name is separate from its DAX expression in the New Measure dialog.
New rows do not appear: Refresh the PivotTable after updating its source. If the source is an Excel Table, appended rows are generally easier to include than rows added outside a fixed cell range; check the PivotTable’s data source if new records are still missing.
The total does not match the visible category medians: That is expected. The grand total recalculates the median over its own set of rows; it does not average the displayed medians.

Power Pivot measure creation is a Windows desktop workflow. Excel for Mac and Excel for the web do not provide the same Power Pivot authoring experience, so a workbook that contains a Data Model measure may be easier to create or modify in supported Windows desktop Excel. If colleagues use another platform, check that their Excel version can display and refresh the workbook as needed, or provide a separate worksheet summary that works in their environment.

Frequently Asked Questions

Can I change Average to Median in Value Field Settings?
No. Standard PivotTable Value Field Settings does not offer Median. Create a Data Model measure with DAX MEDIAN, or calculate each group’s median outside the standard PivotTable.

What is the DAX formula for a PivotTable median?
For a table named Sales and a numeric column named Amount, create a measure with =MEDIAN(Sales[Amount]). The measure recalculates for the PivotTable’s current filters and groups.

Does a PivotTable median ignore blank cells?
DAX MEDIAN ignores blanks in its numeric column. Make sure values that should count are stored as numbers rather than text, and check for source errors or inconsistent data.

Why is the grand-total median not the average of the group medians?
The grand total calculates a median from the underlying records in the total’s filter context. A median is not additive, so group medians cannot usually be combined by taking their average.

Can I calculate a median without Power Pivot?
Yes. In Microsoft 365 or Excel 2021, FILTER combined with MEDIAN can calculate a median for a specified category in worksheet cells. It is a separate summary, not a native PivotTable value field.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.