MEDIAN Function in Excel — The Outlier-Resistant Average (2026 Guide) | Sheets & Cells
Statistical · Basic Aggregate

MEDIAN Function in Excel

Returns the middle value of a dataset. When one CEO earns $500K and nine analysts earn $50K, MEDIAN tells you the truth AVERAGE hides. This is your outlier-resistant weapon.

Universal Support
MEDIAN has existed in Excel since Excel 1.0 (1985). Works identically in Excel 2003, 2007, 2010, 2013, 2016, 2019, 2021, Microsoft 365, Excel Online, Excel Mobile, Google Sheets, and LibreOffice Calc. No compatibility caveats.

The Outlier-Resistance Advantage

Same 10 salaries. Two functions. Two very different stories.

Setup: A startup has 9 analysts earning $50,000 each and 1 CEO earning $500,000. What is a "typical" salary here?
=AVERAGE(salaries)
$95,000
Pulled up by one outlier. Nobody in the company actually earns near this figure — it misrepresents 90% of the team.
=MEDIAN(salaries)
$50,000
The honest middle. Reflects what a typical employee actually earns. The CEO's outlier salary can't move it.

Quick Answer

MEDIAN returns the middle value in a list of numbers. If the count is odd, it returns the single middle value. If the count is even, it returns the average of the two middle values.

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

Example: =MEDIAN(A2:A20) returns the middle value of the range A2:A20. Excel sorts internally — you don't need to sort first.

📊 MEDIAN Practice Workbook

All 5 examples below, ready to open in Excel. Salary skew case study included.

↓ Download .xlsx (Free)
Filebasic-aggregates-examples-2026.xlsx
Size~48 KB
Sheets9 tabs
CoversAVERAGE · MEDIAN · MIN · MAX
1985
Introduced
1–255
Arguments
100%
Compat
17+
Templates Use It

Syntax

MEDIAN accepts between 1 and 255 arguments. Each argument can be a single number, a cell reference, or a range.

ArgumentTypeDescription
number1 Required The first number, cell reference, or range for which you want the median.
number2, ... Optional Up to 254 additional numbers, references, or ranges. In practice, one range covers most cases.

What MEDIAN ignores: empty cells, text values, and logical values (TRUE/FALSE) inside a range reference. What it counts: the number zero — it's a valid number and gets included.

5 Worked Examples

Example 01

Median of an odd count (7 values)

Seven exam scores. Excel sorts internally, then picks the middle.

StudentScore
Emma62
David71
Michael78
Sofia82
Aisha85
James91
Priya96
=MEDIAN(B2:B8)

Result: 82 — the 4th value out of 7 when sorted.

Example 02

Median of an even count (6 values)

Six house prices. No single middle exists, so Excel averages the two middle values.

PropertyPrice
House A$180,000
House B$210,000
House C$245,000
House D$275,000
House E$310,000
House F$390,000
=MEDIAN(B2:B7)

Result: $260,000 — the average of $245,000 and $275,000 (the two middle values).

Example 03

Salary skew — real estate case study

A firm reports "average salary $95K." True — but misleading. The median tells the honest story.

RoleSalary
Analyst 1$50,000
Analyst 2$50,000
Analyst 3$50,000
Analyst 4$50,000
Analyst 5$50,000
Analyst 6$50,000
Analyst 7$50,000
Analyst 8$50,000
Analyst 9$50,000
CEO$500,000
=MEDIAN(B2:B11) → $50,000 =AVERAGE(B2:B11) → $95,000

MEDIAN: $50,000 reflects what most employees actually earn. AVERAGE: $95,000 is pulled up by a single outlier. When someone says "average," ask: median or mean?

Example 04

MEDIAN ignores text and blanks

Mixed data in the range. MEDIAN silently skips text and empty cells — no error, just cleaner math.

CellValueTreated as
B210Number
B320Number
B4"N/A"Ignored
B530Number
B6(blank)Ignored
B740Number
B850Number
=MEDIAN(B2:B8)

Result: 30 — MEDIAN evaluates only {10, 20, 30, 40, 50}. Middle of 5 sorted values = 30.

Example 05

Multiple ranges combined

MEDIAN can take several ranges at once. Combine two teams' scores into one median.

=MEDIAN(A2:A10, C2:C10)

Excel merges both ranges into one pool, sorts internally, and returns the middle value. Same math as if you pasted both columns into one.

How MEDIAN Handles Odd vs Even Counts

Two rules, one function. This is the whole trick.

Odd Count → Single Middle

Sorted: 3, 7, 11, 15, 22

3 · 7 · 11 · 15 · 22

position (n+1)/2 = 3rd value

MEDIAN = 11

Even Count → Average of Two Middles

Sorted: 4, 8, 12, 16, 20, 26

4 · 8 · 12 · 16 · 20 · 26

(12 + 16) / 2 = 14

MEDIAN = 14

Interactive Playground

Your Data (10 values)
A2 → 42
A3 → 58
A4 → 65
A5 → 71
A6 → 78
A7 → 84
A8 → 89
A9 → 92
A10 → 95
A11 → 2,400 ← outlier
Two Formulas, Two Answers
=MEDIAN(A2:A11)
81

Average of the two middles (78 & 84). Outlier ignored.

=AVERAGE(A2:A11)
307.4

Wrecked by the single outlier of 2,400.

The Basic Aggregates Family

Four functions that describe a dataset in a single number. Learn one, understand all four.

⚠ MEDIANIFS Doesn't Exist in Excel

Excel has SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS — but no MEDIANIFS. Never has, still doesn't as of 2026. If you need a median filtered by criteria (e.g. median salary of just the Analyst role), you need a workaround.

Array formula workaround (Excel 2019+ or 365)
=MEDIAN(IF(Role="Analyst", Salary))

In Excel 2016 and earlier, press Ctrl+Shift+Enter instead of Enter to make it array-evaluate. In Excel 365 it works with a normal Enter — the IF spills naturally.

Modern alternative (Excel 365)
=MEDIAN(FILTER(Salary, Role="Analyst"))

Cleaner syntax. FILTER returns only the matching salaries; MEDIAN takes the middle. Only works in Excel 365 / 2021+.

MEDIAN is Just QUARTILE 2 (in disguise)

Median splits data in half. Quartiles split it in quarters. The 2nd quartile IS the median.

What it returnsFormulaPosition
Smallest value=QUARTILE(range, 0)0% (min)
Bottom quartile boundary=QUARTILE(range, 1)25%
Middle value (median)=QUARTILE(range, 2) = =MEDIAN(range)50%
Top quartile boundary=QUARTILE(range, 3)75%
Largest value=QUARTILE(range, 4)100% (max)

Use MEDIAN when you want the middle only. Use QUARTILE when you're building a box plot or a five-number summary of the whole distribution.

Common Errors

ErrorCauseFix
#NUM! Range contains no numeric values (only text, blanks, or logical values). Add at least one number, or use IFERROR to catch: =IFERROR(MEDIAN(range), 0).
#VALUE! A directly-entered argument is text like =MEDIAN("hello", 5). (Text inside a range reference is silently ignored — this only fires when you type text as a literal argument.) Remove the text argument or wrap it in VALUE() if it's a numeric string.
#NAME? Function name typo: =MEDAIN or =MEDIAN( with a missing closing bracket. Check spelling. Excel auto-suggests as you type — accept the suggestion with Tab.
Wrong answer You expected MEDIAN to filter by criteria — it doesn't. MEDIAN of the entire range gives a different number than median of a subset. Use the array-formula workaround: =MEDIAN(IF(criteria, range)).

📊 Practice What You Just Read

Every example, every workaround, all in one workbook. Change the values, watch MEDIAN respond.

↓ Get Workbook

Related Functions

Compatibility

PlatformSupportedVersionNotes
Excel for Windows✓1.0+ (1985)All versions
Excel for Mac✓1.0+All versions
Microsoft 365✓CurrentFull support
Excel Online✓CurrentFull support
Excel Mobile (iOS/Android)✓CurrentFull support
Google Sheets✓AllIdentical behavior
LibreOffice Calc✓AllIdentical behavior

How to Use MEDIAN — Step by Step

  1. Click an empty cell where you want the median to appear.
  2. Type =MEDIAN( — Excel will auto-suggest the function name as soon as you type "MED".
  3. Select the range of numbers with your mouse, or type it manually: e.g. B2:B20.
  4. Close the parenthesis with ) and press Enter.
  5. Verify with AVERAGE. In an adjacent cell, type =AVERAGE( and select the same range. A big gap between median and mean signals outliers are present.
  6. If you need a filtered median, use the array-formula workaround: =MEDIAN(IF(criteria_range=criterion, value_range)). Press Ctrl+Shift+Enter in Excel 2016 or earlier.

Explore More Functions

📊 One More Time — Grab the Workbook

All 5 MEDIAN examples plus AVERAGE, MIN, MAX. Free forever.

↓ Download Free

Frequently Asked Questions

When should I use MEDIAN instead of AVERAGE?

Use MEDIAN whenever your data contains outliers, is skewed, or represents income, house prices, response times, or anything where a few extreme values can distort the mean. AVERAGE is fine for uniform data (test scores from a normal distribution, daily temperatures) but misleading for skewed data.

A good habit: compute both and compare. If they're close, either is fine. If the mean is much higher than the median, outliers are pulling the mean up. If the mean is much lower, outliers are pulling it down.

Does MEDIAN work with non-numeric data?

Inside a range reference, MEDIAN silently ignores text and empty cells — no error, it just skips them. This is usually what you want.

However, if you pass text as a direct literal argument (e.g. =MEDIAN("hello", 5, 10)), MEDIAN returns #VALUE!. And logical values (TRUE/FALSE) inside a range are also ignored, but if entered directly they get treated as 1 and 0 respectively.

Is there a MEDIANIFS function?

No. Excel has SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS — but MEDIANIFS has never existed and still doesn't in 2026. This is one of Excel's most-requested missing functions.

Workaround: =MEDIAN(IF(criteria_range=criterion, value_range)). In Excel 2016 and earlier, enter this with Ctrl+Shift+Enter. In Excel 365, plain Enter works. Or use the modern FILTER: =MEDIAN(FILTER(value_range, criteria_range=criterion)).

How does MEDIAN handle even-numbered datasets?

When there's an even count of values, no single "middle" exists. MEDIAN averages the two middle values instead.

Example: for the sorted list {10, 20, 30, 40}, the two middle values are 20 and 30. MEDIAN returns (20 + 30) / 2 = 25. For {12, 16}, MEDIAN returns 14.

Does MEDIAN count zeros?

Yes. Zero is a valid number and gets included in the median calculation. This is different from empty cells — zeros count, blanks don't.

If you want to exclude zeros (e.g. median sales excluding closed days), use: =MEDIAN(IF(range<>0, range)) as an array formula.

Can MEDIAN handle dates?

Yes — Excel stores dates as numbers (serial numbers counting days since 1900-01-01), so MEDIAN works on date ranges naturally. The result is a number that Excel displays as a date if the cell is formatted as one.

Useful for finding the "typical" date in a set of events: e.g. median order date across a quarter.

What's the difference between MEDIAN and PERCENTILE 50?

They're the same in intent — both return the value that splits the dataset in half. However, they can differ by one interpolation method in edge cases.

=MEDIAN(range) uses linear interpolation for even counts (averages the two middle values). =PERCENTILE(range, 0.5) also uses linear interpolation, so for most datasets they give identical results. Prefer MEDIAN for clarity — it's what everyone recognizes.

Does MEDIAN require sorted data?

No. Excel sorts the values internally before finding the middle. You can pass an unsorted range and get the correct answer instantly.

This is different from LOOKUP or MATCH with approximate match, which do require sorted input.

What if my range contains #N/A or other errors?

MEDIAN propagates errors — if any cell in the range contains an error like #N/A, #DIV/0!, or #REF!, MEDIAN returns that error too.

Fix with IFERROR at the source (better) or wrap MEDIAN itself: =MEDIAN(IFERROR(range, "")) as an array formula.

How is MEDIAN calculated for very large datasets?

Excel uses an efficient sorting algorithm internally. Performance is excellent for datasets up to hundreds of thousands of rows — MEDIAN across 100,000 cells calculates in milliseconds.

For millions of rows or repeated calculations, consider Power Query or a Power Pivot median measure using DAX's MEDIAN function, which is optimized for the columnar engine.

Can I use MEDIAN in conditional formatting?

Yes. A common pattern: highlight cells above the median with =A2>MEDIAN($A$2:$A$100). This flags "above-typical" values visually — great for spotting outliers or top performers at a glance.

Note the mix of relative (A2) and absolute ($A$2:$A$100) references — this pattern applies to any conditional format that compares each row to a whole-column statistic.

Is MEDIAN the "50th percentile"?

Yes — that's exactly what it means. The median is the value where 50% of the data falls below and 50% falls above.

In statistics-heavy contexts, you'll often see the median labeled as P50 or the "second quartile" (Q2). Excel has separate functions for these — PERCENTILE(range, 0.5) and QUARTILE(range, 2) — but all three return the same value for the same input.

Never Compute MEDIAN Wrong Again

Excel Wizard sits inside Excel and writes MEDIAN — with criteria filters, array workarounds, and outlier detection — from a plain-English prompt. AI-powered formulas, right in your ribbon.

Get Excel Wizard Add-in →