Excel MEDIAN Function

MEDIAN returns the middle number in a set of values. Sort your numbers from smallest to largest and MEDIAN is whatever sits in the exact center, not the average, not the most common value, just the midpoint. One thing to know before you use it: MEDIAN quietly ignores blank cells, text, and TRUE/FALSE values, so it's not always evaluating everything you think it's evaluating.

Syntax

=MEDIAN(number1, [number2], ...)

Parameter	Required	Description
`number1`	Yes	A number, cell reference, or range you want included in the median calculation.
`number2, ...`	No	Additional numbers or ranges. You can supply up to 255 arguments, and they don't need to be adjacent to each other.

ℹ

A median is the middle value in an ordered list of numbers. Half the values fall above it, half fall below.

Basic Example

Wren & Oak, an online home goods boutique, wants to know the typical order value for July, but three large wholesale orders are inflating the average.

=MEDIAN(C2:C15)

// C2:C15 = order values for July

If those 14 orders range from $18 to $940, MEDIAN sorts them and returns the value sitting exactly in the middle. It doesn't matter that one order is 50 times larger than the rest. That single outlier has zero effect on where the middle falls, which is exactly why MEDIAN is the better stat here.

How MEDIAN Works

Odd count vs. even count

With an odd number of values, MEDIAN returns the actual middle number from your data. With seven order values, for example, MEDIAN returns the 4th value once everything is sorted, an actual number that exists in your dataset.

With an even number of values, there's no single middle number, so MEDIAN averages the two values that sit in the center. If your two middle values are 74 and 80, MEDIAN returns 77. That 77 might not appear anywhere in your original data, and that's expected, not a bug.

Non-numeric values are ignored, not counted as zero

MEDIAN skips blank cells, text entries, and logical TRUE/FALSE values entirely. It doesn't treat them as zero and it doesn't count them toward the total number of values.

This matters more than it sounds. If your range is B2:B20 and five of those rows are blank because orders haven't shipped yet, MEDIAN calculates the middle of the 15 numbers that actually exist, not the middle of 20 rows. Compare that to a function like COUNTA, which would count those blanks as part of the row total.

That ignore rule only holds for cell references and ranges. Type a logical value directly into the formula and MEDIAN counts it: =MEDIAN(1, 2, TRUE) returns 1, not the median of just 1 and 2, because TRUE becomes 1 the moment it's a literal argument instead of something MEDIAN reads out of a cell.

It accepts multiple ranges and non-contiguous data

MEDIAN doesn't require one clean, continuous range. You can hand it several separate ranges, individual cells, and typed numbers all in the same formula, and it treats every value as one combined pool before finding the middle.

=MEDIAN(B2:B10, D2:D10, 387)

// B2:B10 = Q1 order values
// D2:D10 = Q3 order values (Q2 data lives elsewhere and isn't included)
// 387    = one manually entered order value

This is useful when your data lives in scattered blocks across a sheet, say, quarterly batches that got pasted into different columns over time. MEDIAN pulls every number from every argument into one dataset before calculating.

Common Use Cases

Reporting a typical salary instead of a skewed average

Radley Manufacturing's HR team wants to report the typical salary in the Operations department, but two senior directors earning well above the rest are pulling the average up.

=MEDIAN(salaries)   // returns the middle salary, unaffected by the two outliers

A median salary figure gives leadership a realistic sense of what a typical employee earns. An average would overstate it the moment a couple of high earners enter the mix.

Combining scattered data ranges into one median

Order data from the Chicago, Denver, and Austin offices sits in three separate columns on the same sheet.

=MEDIAN(Chicago_Orders, Denver_Orders, Austin_Orders)

Because MEDIAN treats every argument as part of one pool, you don't need to consolidate the three ranges into a single column first.

Median by category with FILTER

You want the median order value for just the "Wholesale" category, not the whole table.

=MEDIAN(FILTER(orders[Value], orders[Category]="Wholesale"))

// orders[Value]    = the full column of order amounts
// orders[Category] = the column FILTER checks against "Wholesale"

FILTER narrows the range down to only wholesale rows first, then MEDIAN calculates the middle of that smaller set. This only works in Excel 365 or 2021, since FILTER is a dynamic array function.

Handling Errors

MEDIAN throws #NUM! when it has nothing numeric to work with. This usually means your range is entirely blank, entirely text, or points at the wrong cells.

Common causes of #NUM!:

The range is empty or every cell is text
A typo sends the range to the wrong column
Numbers are stored as text and Excel isn't recognizing them as numbers

=IFERROR(MEDIAN(orders), "No numeric data found")

✓

If MEDIAN returns #NUM! unexpectedly but the range looks full of numbers, check for numbers stored as text. A stray apostrophe before a value, or data pasted from a website, is the usual culprit. Select the range and check the small green triangle warning in the corner of each cell.

Notes & Gotchas

How do you calculate the median in Excel?

Use the MEDIAN function with a range of numeric values: =MEDIAN(range). Excel sorts the values internally and returns whichever number sits in the exact middle, or the average of the two middle numbers if the count is even. You don't need to sort the data yourself first.

What is the syntax for the MEDIAN function?

The syntax is =MEDIAN(number1, [number2], ...), where each argument is a number, a cell reference, or a range. You can supply up to 255 separate arguments in modern Excel, and they can be typed directly, referenced from cells, or a mix of both.

Can MEDIAN handle multiple ranges or non-contiguous data?

Yes. MEDIAN accepts several ranges and individual values in the same formula, combining everything into one dataset before calculating the middle. =MEDIAN(A2:A10, C2:C10, 99) treats all 21 values as one pool, not three separate calculations.

Why is the MEDIAN function important?

MEDIAN gives you a "typical" value that isn't distorted by extreme highs or lows. A single seven-figure transaction or an entry error of 18,400 instead of 184 barely moves the median, while it can swing an average dramatically. That makes MEDIAN the more honest statistic for skewed data like salaries, home prices, or order sizes.

How does MEDIAN relate to AVERAGE and MODE?

MEDIAN, AVERAGE, and MODE are the three standard measures of central tendency, and each answers a slightly different question. AVERAGE tells you the mathematical mean, MEDIAN tells you the actual middle value, and MODE tells you which value appears most often. Running all three side by side on the same data set often reveals just how skewed a distribution really is.

Why does MEDIAN return #NUM!?

MEDIAN returns #NUM! when none of the supplied arguments contain a numeric value. An entirely blank range, a range of text labels, or numbers that Excel is reading as text will all trigger it. Check for hidden text formatting on your numeric cells if the data looks correct but the error persists.

Does MEDIAN work with hidden or filtered rows?

MEDIAN includes hidden and filtered rows in its calculation. This is different from AVERAGE combined with SUBTOTAL, which can be set to ignore hidden rows entirely. If you've filtered a table down to one region but MEDIAN still reflects the full dataset, this is why: it's referencing the underlying range, not the visible rows.

⚠

MEDIAN doesn't respect filters, and SUBTOTAL can't fix it — SUBTOTAL has no median option built in. Use AGGREGATE(12, 5, range) instead: 12 selects MEDIAN and 5 tells AGGREGATE to ignore hidden rows. It works in any Excel version back to 2010, no dynamic arrays required. If you're already on Excel 365 and want the filter criteria written into the formula itself rather than applied through the UI, MEDIAN(FILTER(...)) does the same job.

Does MEDIAN work inside PivotTables?

Not natively. PivotTable value fields offer Sum, Average, Count, Max, Min, and a handful of others, but Median isn't on that list. To get a median inside a PivotTable-style report, build the summary with MEDIAN and FILTER outside the pivot, or use Power Pivot with a DAX measure that calculates it manually.

Related Functions

Function	Use this when...
`AVERAGE`	You want the arithmetic mean and your data doesn't have significant outliers.
`FILTER`	You need to narrow a range down before running MEDIAN on just a subset of it.
`MODE`	You want the most frequently occurring value instead of the middle one.
`AGGREGATE`	You need a median that ignores hidden rows or error values in the range.