Management, Supervision & Leadership
Jul 23, 2026
How to Calculate Median in an Excel PivotTable
Key Takeaways
- Excel PivotTables don't include median as a built-in summary function, but two reliable workarounds exist.
- The MEDIAN+IF array formula method works in all Excel versions and places median values beside a PivotTable.
- The Power Pivot DAX method lets you display median directly inside a PivotTable as a custom measure.
- Choosing the right method depends on your Excel version, data size and whether you need the result inside the PivotTable itself.
A frequent question that comes up when working with Excel PivotTables is how to calculate the median using the table's filters and analysis. The short answer is that there is no native option in the Value Field Settings to summarize by median. The closest built-in function is Average. The good news is that two proven workarounds let you get an excel pivot table median calculation: a MEDIAN+IF array formula placed beside your PivotTable, or a Power Pivot DAX measure that displays the median directly inside the table.
However, averages aren't always the best way to understand your data. Our example shows donations collected by a regional charity. Most of the donations are small amounts, a handful are more significant, and one or two are very large. The average donation for the year is $1380.50, but that does not accurately show us how most of our donors give. The median calculation shows us that most of our donations are around $50 with half giving more, and half giving less. This number more realistically reveals our donor giving patterns.
Median is the better choice whenever your data is skewed or contains outliers. Consider using median vs average in a pivot table analysis when you are working with:
- Donation or revenue data where a few large values pull the average up
- Salary or compensation figures with wide ranges across roles
- Response times, delivery durations or any metric where outliers distort the mean
- Survey scores or ratings with non-uniform distributions
To follow using our example below, download Calculate Median in PivotTables.xlsx
If we want to understand our donor giving patterns by state, we would naturally turn to a PivotTable and get something like we see in the first image. But what if we want the median donation for each state?
Method 1: Use a MEDIAN+IF Array Formula Alongside Your PivotTable
Here is the first of two workaround solutions to get a median in pivot table reporting. This approach uses the array feature to pair a MEDIAN IF array formula with your existing PivotTable layout. As with all workarounds, it may not be elegant, but it can get you started. The steps demonstrated apply to Excel 2010 and later.
Technically, the end result isn't a PivotTable, but you can use the PivotTable feature to set up your array more easily:
Step-by-Step Walkthrough
- First, create a PivotTable with the data organized in the way you want except with an Average calculation instead of Median in the Values area.
- Then, select the PivotTable cells (headings included) and hit Ctrl+C to copy the table to your clipboard.
- Right-click on an open cell and select Paste Special > Paste Values to paste a copy of the table's headers and values in your worksheet.
- Add a column to the new table for the Median calculation.
- For each Row Label, combine the IF function with the MEDIAN function to pull only the data that corresponds to the fields in the PivotTable. Type the pivot table median formula in the Median column. In our example, the formula will look like:
=MEDIAN(IF($H$2:$H$65=J19, $C$2:$C$65))
To break down the formula:
- =MEDIAN( tells Excel to calculate the median of the numbers that the IF statement returns
- IF(($H$2:$H$65=J19, tells Excel to only return donor amount values if the value in the H column – State – matches the value in the J column (our PivotTable State Row Label). Use the $ symbol to specify absolute cell values.
- $C$2:$C$65)) tells Excel to return the values in the C column that meet the above criteria for the MEDIAN function. Use the $ symbol to specify absolute cell values.
- Hit Ctrl+Shift+Enter Important! Don't just hit enter to complete the formula. You must hold Ctrl+Shift+Enter down together to complete the formula and tell Excel that it is an array formula. Excel will add curly braces around the formula if this is done correctly:
If you are using Microsoft 365 or a newer version of Excel that supports dynamic arrays, you can simply press Enter instead of Ctrl+Shift+Enter. Excel will treat the formula as a dynamic array automatically, and you will not see curly braces in the formula bar.
- Now you can drag the formula to the other cells in the "fake" PivotTable and quickly see the Median Donation for all States covered by the charity.
So, "this isn't really a PivotTable", but this method does allow you to quickly harness the visual power of PivotTables to organize your information. You still have to do the manual work of making the Median calculation, but the PivotTable step gives you a shortcut to organizing your information and creating the formula. And until Microsoft adds the MEDIAN function directly to PivotTables, this is the most common solution out there.
When to Use This Method
The array formula approach is the best fit when:
- You are using Excel 2010, 2013 or 2016 without access to Power Pivot
- You need a quick, one-off median calculation rather than an ongoing report
- Your dataset is relatively small (a few hundred to a few thousand rows)
- You prefer formula-based solutions and want to avoid add-ins
If your reporting needs are more complex or you want the median to appear directly inside the PivotTable, the Power Pivot method below is a stronger option.
Method 2: Use Power Pivot and a DAX Measure for a True PivotTable Median
The array formula method works well, but it places the median outside the PivotTable in adjacent cells. If you want to calculate median in an excel pivot table so that it appears as a native value inside the table itself, Power Pivot and a DAX median measure are the way to go. This method requires Excel 2013 Professional Plus or later or Microsoft 365.
How to Enable Power Pivot and Create a Median Measure
- Activate the Power Pivot add-in. Go to File > Options > Add-ins. At the bottom of the dialog, set the Manage dropdown to COM Add-ins and click Go. Check the box for Microsoft Power Pivot for Excel and click OK. A Power Pivot tab will appear on the ribbon.
- Add your data to the Data Model. Select any cell in your source data table and go to Power Pivot > Add to Data Model. Excel will open the Power Pivot window and import your table. Close the Power Pivot window when the import is complete.
- Create a DAX median measure. Back in your workbook, click the Power Pivot tab and select Measures > New Measure. In the Measure dialog, set the table name to your data table and enter a formula like the following (adjusted for your column names):
Median Donation:=MEDIANX(Table1, Table1[Amount])
The MEDIANX function evaluates the Amount column for every row in Table1 and returns the median of those values. Click OK to save the measure.
- Build or update your PivotTable. Insert a new PivotTable (or use an existing one connected to the Data Model). Drag your grouping field (for example, State) to the Rows area. In the field list you will see the Median Donation measure you just created. Drag it to the Values area. The PivotTable now displays the power pivot median for each group, and it updates automatically whenever you refresh the data.
This approach gives you a true pivot table custom calculation that lives inside the PivotTable, responds to slicers and filters and refreshes alongside your data.
Which Method Should You Use?
Both methods deliver a reliable median, but they suit different situations. The comparison table below can help you decide.
| Factor | Array Formula Method | Power Pivot DAX Method |
|---|---|---|
| Excel versions supported | 2010 and later | 2013 Pro Plus, 2016+ and Microsoft 365 |
| Median displayed inside PivotTable | No (displayed in adjacent cells) | Yes (appears as a PivotTable value) |
| Setup complexity | Low to moderate | Moderate (requires enabling add-in) |
| Best for | Quick analysis, smaller datasets | Ongoing reporting, larger datasets, multiple groupings |
| Dynamic updates when source data changes | Manual (re-copy PivotTable values) | Automatic (refreshes with PivotTable) |
Dynamic updates when source data changes
Manual (re-copy PivotTable values)
Automatic (refreshes with PivotTable)
For a one-time look at median values, the array formula method gets the job done fast. For dashboards and recurring reports where you need the median to stay current, Power Pivot is the stronger choice. Either way, download the practice file above and try both approaches with real data. If you want to sharpen your Excel skills further, explore Pryor Learning's Excel training courses for hands-on instruction on PivotTables, formulas, Power Pivot and more.
