How Do You Find the Median in Excel? (2026 Guide)

The median is the value sitting exactly in the middle of a sorted set of numbers. Half your data is above it, half below. That makes it the statistic you reach for whenever a handful of extreme values would drag an average somewhere misleading.

Excel has a dedicated function for it, and for a simple column of numbers the job takes about four seconds. The trouble starts when your data is not a simple column of numbers: it is grouped into bands, or it lives in a pivot table, or you only want the median of the rows that match a condition. Each of those needs a different approach, and two of them are not obvious at all.

This guide covers the straightforward case first, then each of the awkward ones.

The short answer

Type `=MEDIAN(` , select your range of numbers, close the bracket, and press Enter.

`=MEDIAN(B2:B41)` returns the middle value of everything in B2 through B41. Excel sorts the values internally, so your data does not need to be in order beforehand. If the count is odd, you get the single middle number. If it is even, you get the average of the two middle numbers.

That is the whole answer for a plain list. Everything below is for when your data is shaped differently.

Guessing which of your pages are close to ranking and which are nowhere near? Get a free SEO audit and see the numbers instead of guessing.

What MEDIAN actually does with your data

Understanding the mechanics saves you from the two most common wrong results.

It ignores text and blanks, but not zeros

MEDIAN skips empty cells, text values and logical values in a referenced range. A blank row in the middle of your data will not shift the answer.

Zeros are different. A zero is a number, so it counts, and a column padded out with zeros instead of blanks will pull the median down hard. If those zeros mean "no data" rather than "a measured value of nothing", clear them before you calculate.

It does not care about sort order

This trips people up who have previously found a median by hand. You do not need to sort the column first, and sorting it will not change the result. Excel handles the ordering internally.

It accepts up to 255 separate arguments

You can pass non-contiguous ranges: `=MEDIAN(B2:B20, D2:D20, F5)`. Excel pools every number it finds across all of them and returns one median for the combined set. This is useful when the same measurement lives in several columns.

How to find the median for grouped data

This is the case most search results skip, and it is the one that produces genuinely wrong answers if you treat it like a plain list.

Grouped data means you no longer have individual values. You have bands and counts: 12 people earned $0 to $25,000, 31 people earned $25,001 to $50,000, and so on. The raw numbers are gone. Feeding `MEDIAN` the band midpoints gives you the median of the midpoints, which is not the median of the underlying population.

The correct method estimates the median from the cumulative frequency. Set up four columns.

1. Column A — the class interval. One row per band.
2. Column B — the frequency. How many observations fall in that band.
3. Column C — the cumulative frequency. In C2 put `=B2`, in C3 put `=C2+B3`, and fill down.
4. Column D — the lower boundary of each class.

Now find the median class. Total your frequencies somewhere with `=SUM(B2:B10)` and halve it. The median class is the first band whose cumulative frequency reaches or passes that halfway point. You can locate it with a MATCH:

`=MATCH(SUM(B2:B10)/2, C2:C10, 1) + 1`

Then apply the grouped median formula. In plain terms it is the lower boundary of the median class, plus the proportion of the way through that class you need to travel, multiplied by the class width:

`= L + ((n/2 - CF) / f) * w`

Where `L` is the lower boundary of the median class, `n` is the total frequency, `CF` is the cumulative frequency of the class *before* the median class, `f` is the frequency of the median class itself, and `w` is the class width.

As a live formula with the median class in row 5, that becomes:

`=D5 + ((SUM($B$2:$B$10)/2 - C4) / B5) * (D6-D5)`

The result is an estimate, not an exact value, and it should be labelled as one. It assumes observations are spread evenly inside the median class, which real data rarely is. It is still far more defensible than averaging band midpoints.

A shortcut worth knowing

If you still have access to the raw data that the bands were built from, use it. Run `=MEDIAN()` on the original column and ignore the grouped calculation entirely. The grouped formula exists for the situation where the underlying values are genuinely unavailable — a published summary table, a survey report, a legacy export.

Wondering whether the traffic numbers in your own reports hold up to scrutiny? Run a free audit and find out what is actually driving your visits.

How to calculate a median in a pivot table

Pivot tables do not offer Median in the "Summarize Values By" list. Sum, Average, Count, Max, Min, StdDev and Var are all there. Median is not, and it is not an oversight — the way pivot caches aggregate data makes a true median expensive to compute.

You have three workarounds, in order of how much setup they need.

Option one: add a helper column before you pivot. Calculate the median per group in the source data with a conditional formula (see the next section), then pivot on that column using Average or Max as the aggregation. Since every row in a group carries the same value, either one returns it unchanged.

Option two: use the Data Model and a DAX measure. Tick "Add this data to the Data Model" when you create the pivot, then add a measure:

`Median Sales := MEDIAN(Sales[Amount])`

This gives you a real, filter-aware median that responds correctly as you slice and expand the pivot. It is the only option that behaves like a native aggregation.

Option three: skip the pivot. For a handful of groups, a small grid of `MEDIAN(IF(...))` formulas beside your data is faster to build and easier for someone else to audit than a Data Model.

How to find a median with conditions

You want the median salary for one department, or the median order value for one region. There is no `MEDIANIF` function, so you combine MEDIAN with IF.

In Microsoft 365 and Excel 2021 or later, dynamic arrays make this clean:

`=MEDIAN(IF(A2:A200="West", B2:B200))`

The IF returns an array of the values where the condition holds and FALSE everywhere else, and MEDIAN ignores the FALSEs. Type it and press Enter.

In Excel 2019 and earlier, the same formula needs to be entered as an array formula with Ctrl + Shift + Enter. Excel will wrap it in braces itself. Press plain Enter and you will get a wrong answer or an error, with no warning that anything went amiss.

For two or more conditions, multiply the tests together:

`=MEDIAN(IF((A2:A200="West")*(C2:C200="Q3"), B2:B200))`

A cleaner modern alternative is FILTER, which reads much better six months later:

`=MEDIAN(FILTER(B2:B200, (A2:A200="West")*(C2:C200="Q3")))`

If no rows match, FILTER returns an error rather than a misleading zero. Wrap it in IFERROR if you are building a dashboard someone else will use.

Building reports that someone else has to trust? Get a free SEO audit and start from numbers you can defend.

When to use the median instead of the average

The median is the right choice more often than most spreadsheets suggest.

• Money. Incomes, salaries, house prices, order values and customer lifetime value are all skewed by a small number of very large figures. The median describes the typical case; the mean describes a case that may not exist.
• Time-to-event data. Page load times, support ticket resolution times and session durations have long right tails. One 40-second load skews a mean built from 200 sub-second loads.
• Small samples with a known outlier. With twelve data points and one obvious anomaly, the median absorbs it. The mean does not.
• Ranked or ordinal data. Where the gaps between values are not equal, averaging is arguably meaningless, and the median at least identifies a real middle category.

Use the average when the distribution is roughly symmetrical, when you need the total to reconcile (means multiply back up to sums, medians do not), or when a downstream statistical test requires it.

The honest answer is usually to report both. If the mean and median are close, the distribution is tidy and either is fine. If they are far apart, that gap is itself the finding.

Not sure which of your numbers tell the real story? Get a free SEO audit and get a clear read on where your traffic actually comes from.

Limitations to know about

MEDIAN cannot be made to skip zeros on its own. You need a condition: `=MEDIAN(IF(B2:B200<>0, B2:B200))`.

Text that looks like a number is invisible to it. Values imported as text, complete with the little green triangle, are skipped silently. The formula returns an answer and the answer is wrong. Convert the column to numbers first — Text to Columns on a single column is the fastest fix.

Hidden and filtered rows still count. Unlike SUBTOTAL, MEDIAN has no filter-aware variant. Filtering a table does not change what `=MEDIAN(B2:B200)` returns. For a median of visible rows only, you need a condition that reproduces the filter logic, or a Data Model measure.

Two medians of subgroups do not average into the overall median. You cannot compute a median per region and average those to get a company-wide median. You have to go back to the full data set.

The grouped-data result is an estimate. Say so wherever you report it.

MEDIAN versus the alternatives

MEDIAN vs AVERAGE. MEDIAN resists outliers; AVERAGE reflects every value including the extremes. Neither is more correct in the abstract — they answer different questions.

MEDIAN vs QUARTILE.INC. The median is the second quartile. `=QUARTILE.INC(B2:B200, 2)` returns exactly the same number as `=MEDIAN(B2:B200)`. Use QUARTILE when you want the spread as well as the centre.

MEDIAN vs PERCENTILE.INC. Same relationship: `=PERCENTILE.INC(B2:B200, 0.5)` is the median. Reach for PERCENTILE when you care about the 90th or 95th, which is where performance data usually gets interesting.

MEDIAN vs TRIMMEAN. `=TRIMMEAN(B2:B200, 0.1)` discards the top and bottom 5% and averages the rest. It is a middle ground between mean and median, and it keeps the arithmetic behaviour of a mean while damping the tails.

MEDIAN vs MODE. Different statistic entirely. MODE returns the most frequently occurring value, which is useful for categorical data and nearly useless for continuous measurements.

Final Thoughts

For a plain column of numbers, `=MEDIAN(range)` is all you need, and it is more robust than the average you probably reached for by habit.

The three cases worth memorising are the ones that quietly go wrong. Grouped data needs the cumulative-frequency formula, not the midpoints. Pivot tables need a helper column or a DAX measure, because there is no Median aggregation. Conditional medians need MEDIAN with IF, and on Excel 2019 or earlier they need Ctrl + Shift + Enter or they return nonsense.

One habit is worth more than any of the formulas: report the mean and the median side by side. When they diverge, you have learned something about the shape of your data that neither number tells you alone.

If your data arrives in uneven chunks that need organising into bands before any of this makes sense, our guide to how to stratify data in Excel covers the step that comes first.