The default summary for every numeric field you drop into a pivot table is Sum, and for most business reporting, that is the right call. But the average of a column is not the same number as the middle value, and the most frequently appearing value is not the same as either. When a dataset is heavily skewed — income data, apartment prices, customer wait times — the average sits far away from the value a typical record shows. Switching a pivot table from Sum to Average, Median, or Mode changes the story your report tells, and each one fails under different conditions.
Below are the three summary methods ranked by how often they should be used in practice, with the settings required for each and the data conditions that make each one untrustworthy.
1. Average — the default alternative, but it hides outliers
Average is the first summary type most people reach for after Sum, because it is one click away in the Value Field Settings dialog. Right-click any value cell, choose Summarize Values By, and pick Average. The pivot table immediately shows the arithmetic mean of the underlying records in each row-column intersection.
The average is meaningful when your data is roughly symmetric — salaries in a single department, delivery times for a standard route, test scores in one class. Outliers stretch the average toward them. Five employees earning $50,000 and one earning $500,000 produce an average of $125,000, which describes nobody in that group. In testing with real-world income data, the median consistently falls 10–30% below the average whenever a small number of high-value records exist.
The second problem with averages in pivot tables is blank handling. If your source data has blank cells in the value column, Standard pivot tables ignore those blanks when calculating Average, but they do not warn you about it. A column with 100 records where 40 are blank shows an average based on 60 records, and nothing in the pivot table indicates the reduced count. The average is not wrong — it is just calculated on a subset you may not realize you are looking at.
Use Average when the data is symmetric and complete. If the maximum is more than double the median, the average is misleading.
2. Median — the middle value that resists distortion
Median is the middle number when all values are sorted — half the records fall below it, half above. For skewed data, median is the more honest summary. The median employee salary in the example above is $50,000, which reflects what most people in that group earn.
Here is the catch: standard pivot tables do not offer Median in the Summarize Values By dropdown. The option simply does not exist there. You have three workarounds, ranked by how much effort they require:
Option A — Add the data to the Data Model. When you create a pivot table, check the box for “Add this data to the Data Model.” Then you can write a DAX measure using MEDIAN(TableName[ColumnName]). This produces a true median that updates on refresh and respects all pivot filters. This is the cleanest solution, but it requires the Data Model, which behaves differently from a standard pivot table in a few ways — no Show Values As percentages on the same field, and a different field list layout.
Option B — Use a helper column in the source data. Add a column to your source table that assigns each row to a percentile bucket, or simply pre-sort and use a formula to identify the middle record. This adds maintenance overhead — every new row needs the helper formula applied — and it breaks if the data is not sorted properly before the pivot refresh.
Option C — Use the AVERAGEIF workaround outside the pivot table. Build a separate summary table with =MEDIAN(IF(range=criteria, values)) entered as an array formula. This does not live inside the pivot table, so it loses the automatic grouping and filtering that makes pivot tables worth using in the first place.
Median fails in one specific scenario: even-numbered datasets where the two middle values sit far apart. If a column has 100 records and the 50th value is $40,000 while the 51st is $90,000, the median is $65,000 — a number that appears nowhere in the data. In practice, choose the Data Model approach (Option A) whenever median reporting will be a regular need. The setup cost pays back after the third time you need it.
3. Mode — the most frequent value, useful only for discrete data
Mode answers the question “which value shows up most often?” For continuous data like revenue amounts or exact timestamps, every value may be unique, and mode becomes meaningless — each number appears exactly once. Mode only makes sense for discrete categories or rounded values: star ratings (1-5), order quantities, ages grouped into whole years.
Standard pivot tables do not offer Mode in the Summarize Values By dropdown either. As with median, the Data Model route works: a DAX measure using MAXX combined with a TOPN filter, or simpler, a calculated column that counts occurrences and then aggregates the maximum count. The formula pattern is more complex than the median measure, and most users end up creating a frequency table instead.
The simpler practical route for mode is a two-step process. First, drag the field into the Rows area of a pivot table. Second, drag the same field into the Values area with Count as the summary type. The row that shows the highest count is the mode. This is visible immediately, requires no DAX, and updates on refresh. The tradeoff is that you scan the list manually rather than having Excel return a single number — which works fine when your categories number in the dozens, and becomes impractical when hundreds of distinct values exist.
Mode becomes actively misleading when the distribution is flat. If your top three values each appear 10 times, calling any one of them “the mode” hides the fact that no value truly dominates. Report mode only when the highest count is measurably above the second-highest count — a gap of at least 20% is a reasonable threshold in most business datasets.
A Quick Reference for Choosing the Right Summary
| Question you’re answering | Summary type | Where to set it | Data condition required |
|---|---|---|---|
| What is the typical value in a symmetric dataset? | Average | Right-click value → Summarize Values By → Average | Symmetric distribution, no significant outliers |
| What value does the typical record sit at, in a skewed dataset? | Median | Data Model with MEDIAN() DAX measure | Works with outliers; falls apart with large gaps between middle values |
| Which specific value appears most often? | Mode | Rows + Values with Count, or DAX with frequency logic | Discrete values, no ties, one dominant category |
Most people default to Average because it is the only option visible without digging into settings. That default costs money in real scenarios — pricing decisions based on average transaction value get distorted by a few large orders, staffing decisions based on average wait times hide the fact that most customers wait far longer. A five-minute change to a median-based measure frequently changes a report’s conclusion in measurable ways.
The practical order of operations: check the shape of your distribution first with a quick frequency count, and if the spread between the average and median exceeds 20%, switch your primary summary to median. Use mode only when you need to know the most common specific value, never as a proxy for the typical value overall.
What to Do Next
Open the pivot table you use most often for reporting, right-click the primary value field, and check what Summarize Values By is currently set to. If it is Sum, that is likely correct for additive metrics. If it is Average, run a quick check: add the same field a second time to the Values area, set the second copy to Average, and compare it to a manually calculated median in a spare column. A gap larger than 20% between those two numbers means your report is currently telling a distorted story.
If you find that gap, move that field into the Data Model by rebuilding the pivot table with the “Add this data to the Data Model” checkbox selected, then write the MEDIAN() measure and swap it into your report. That one change takes under ten minutes and aligns your summary metric with what the data says.