You've got a column of numbers and the average is lying to you. One outlier — a salary that's triple everyone else's, a single 90-minute support call that throws off the day — and the mean drifts somewhere nobody actually lives. The median doesn't move. It picks the middle value and holds.

Excel handles it in one function: =MEDIAN(range). That's the easy part. The hard parts are everything around it. What happens when a few cells are blank? What about text mixed into the numbers? How do you take the median of just the rows where Region = "North", and what's the cleanest way to do that without a helper column? Can you median across three sheets at once? Why does a Pivot Table not even offer Median in the summarise-by menu, and what do you do about it?

This guide answers each of those, in order, with the exact formula patterns you can paste into a workbook today. We'll start with the plain MEDIAN function, build up to conditional medians (MEDIANIF the way it actually works, since Excel doesn't ship one), and finish with the Pivot Table workaround and a quick contrast against the mean — because the two answer different questions and reaching for the wrong one is the most common mistake people make with central tendency.

If you only remember one thing from this article, make it the AGGREGATE trick for conditional medians. It dodges the array-formula keyboard shortcut, works in every modern Excel version, and reads cleanly six months from now when you're trying to remember why you wrote it.

The basic MEDIAN function

Syntax is exactly what you'd expect: =MEDIAN(number1, [number2], ...). The arguments can be individual numbers, cell references, or ranges. Excel sorts the values, finds the middle one, and returns it. With an even count of values, it averages the two centre numbers.

Drop a column of salaries in B2:B11 and write =MEDIAN(B2:B11). That's it. Multiple ranges work too: =MEDIAN(B2:B11, D2:D11) combines both into a single pool before finding the middle.

One gotcha that bites people: MEDIAN ignores text, logical values (TRUE/FALSE), and empty cells. It does not ignore zeros — a cell with a 0 in it counts as a value of zero. That matters more than you'd think when you've got "no sales" rows mixed in with real numbers. If those zeros shouldn't count, you'll want the conditional version covered further down.

Another quirk: if your "numbers" are actually numbers stored as text (the common cause: imported from CSV with a leading apostrophe, or formatted as text before typing), MEDIAN skips them entirely. A range that looks like ten numbers might only contain six real values. Spot it by clicking a cell — if the value sits left-aligned by default, it's text. Run =COUNT(B2:B11) against =COUNTA(B2:B11); if they don't match, you've got text contaminating the column.

Blanks, text, and zeros: how MEDIAN actually treats them

This is the second-most-common reason a median answer feels wrong. Behaviour is consistent but worth pinning down with a small test.

Put these values in B2:B7 — 10, blank, "n/a", 20, 0, 30. Run =MEDIAN(B2:B7). The result is 15. Why? MEDIAN sees four numeric values (10, 20, 0, 30), sorts them to 0, 10, 20, 30, takes the average of the middle two (10 and 20), and returns 15. The blank cell and the text "n/a" are simply skipped. The zero is treated as a real value.

If you want zeros excluded, replace the function with this pattern: =MEDIAN(IF(B2:B7<>0,B2:B7)). In Excel 365 or 2021, it works as a normal formula. In earlier versions, press Ctrl+Shift+Enter so Excel evaluates it as an array. Result on the same data: 20 (median of 10, 20, 30).

If you want text-as-number to count, use VALUE: =MEDIAN(IFERROR(VALUE(B2:B7),"")). The wrapper converts numeric-text into numbers and feeds them through. Again, array-enter it in pre-365 versions.

Conditional median — the "MEDIANIF" that Excel doesn't have

Microsoft ships SUMIF, AVERAGEIF, COUNTIF, and their plural cousins. There's no MEDIANIF. You'll need one of two workarounds — both are clean once you've seen them.

Approach 1: Array IF inside MEDIAN

The pattern: =MEDIAN(IF(criteria_range=criteria,value_range)). Example — median salary where Department = "Sales":

=MEDIAN(IF(A2:A100="Sales",B2:B100))

In Excel 365, 2021, or Excel for the Web, this returns the right answer immediately. In Excel 2019 or older, hit Ctrl+Shift+Enter after typing it — Excel wraps the formula in curly braces and evaluates the IF across every row before passing the result to MEDIAN. Without the array entry, you'll get a single-cell comparison and a wildly wrong number.

Multiple conditions chain with multiplication: =MEDIAN(IF((A2:A100="Sales")*(C2:C100=2026),B2:B100)). Each parenthesised condition returns TRUE/FALSE, which Excel coerces to 1/0; multiplying them produces 1 only where every condition is true.

Approach 2: AGGREGATE with a filter

Cleaner for many real workbooks. AGGREGATE function 17 is QUARTILE.INC, and quartile 2 is the median. Combine it with the function's option to ignore errors:

=AGGREGATE(17,6,B2:B100/(A2:A100="Sales"),2)

What's happening: dividing by the condition produces a value where it's true and a #DIV/0! error where it's false. Argument 6 tells AGGREGATE to ignore errors, so only the true-condition values remain. The trailing 2 is the second quartile — that's your median. No array-formula shortcut required. Works back to Excel 2010.

I reach for the AGGREGATE form nine times out of ten. It's faster on large ranges, doesn't need the keyboard incantation, and the logic reads naturally once you've used it a couple of times.

Why Pivot Tables don't have a "Median" option (and what to do)

Open any Pivot Table, drop a numeric field into the Values area, click "Value Field Settings", and scan the list: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp. No Median. That's not an oversight — it's a memory and performance trade-off. Median requires sorting the whole dataset for each cell of the pivot, while sums and averages can be computed in a single pass. Microsoft chose not to ship it.

Three workarounds, ranked by how often I actually use them.

Option 1: Power Pivot with a DAX measure. If your data is loaded to the Data Model, write a measure: Median Salary := MEDIAN(Table1[Salary]). DAX has a native MEDIAN function and it works inside Pivot Tables driven by the Data Model. This is the right answer if you're already in Power Pivot or you can move the data there.

Option 2: Helper column with a formula above. Build a regular Pivot, then add an output cell beside it using =MEDIAN(IF(...)) against the source range, filtered to match the row label you're interested in. Quick and dirty, breaks down at scale, but fine for a one-page report.

Option 3: GROUPBY (Excel 365 only). The newer GROUPBY function takes any aggregation as an argument, including a lambda. Write: =GROUPBY(A2:A100, B2:B100, LAMBDA(x,MEDIAN(x))) and you get a clean two-column output of group → median. It's the closest thing to a "Pivot with Median" Excel has shipped.

MEDIAN across multiple sheets (3D references)

You've got monthly sales on twelve sheets named Jan, Feb, …, Dec, with the daily total in cell B2 on each one. To find the median of all twelve months' totals: =MEDIAN(Jan:Dec!B2). Excel walks every sheet between Jan and Dec inclusive, gathers the B2 value from each, and returns the median.

The sheets have to be contiguous in the tab order. If you reorder them (drag November after April), the 3D reference picks up whatever sits between the start and end sheet — which might or might not be what you want. Add hidden bookend sheets named "Start" and "End" if you want the formula to lock to a specific group regardless of tab order.

3D ranges also work across a block of cells. =MEDIAN(Jan:Dec!B2:B32) grabs the daily numbers from every month and returns the median across all of them. Just remember it's the median of every cell, not the median-of-monthly-medians — those are different statistics and the difference matters.

Median vs mean: when to use which

The two answer different questions. The mean asks: "If I shared this evenly, what would everyone get?" The median asks: "What does the person in the middle have?"

For symmetric data — heights, exam scores, temperature readings — the two values land in the same neighbourhood and either is fine. The mean is a little tighter mathematically (it uses every data point), so when the spread is even, prefer the mean.

For skewed data — salaries, house prices, response times, ad revenue per user — the mean lies. A handful of huge values drags it upward and away from the experience of most of the dataset. Median ignores the magnitude of outliers; it only cares about position. That's why "median household income" is the standard reported measure in every census on Earth: a few billionaires don't move it.

The rule of thumb I use: run both. If they differ by more than 10%, the data is skewed and median is the better summary. If they agree, use whichever is easier to explain. And whatever you pick, label it explicitly on the report — "Median: $54,200 (Mean: $71,800)" is honest; a single number with no qualifier is begging for a misreading.

One more thing. Median is robust to outliers but it's also throwing information away. If your goal is to find the outliers — fraud detection, quality control, anomaly hunting — the mean is what you compare individual values against, not the median. Different jobs, different tools.

Practical examples you'll actually use

A few patterns worth pasting into a scratch sheet.

Median ignoring blanks (already automatic): =MEDIAN(B2:B100) — blanks are skipped. No extra work.

Median ignoring zeros: =AGGREGATE(17,6,B2:B100/(B2:B100<>0),2). Reads as "median of B values where the value isn't zero".

Median ignoring negatives: =AGGREGATE(17,6,B2:B100/(B2:B100>0),2). Useful for response-rate columns where negative numbers signal bad data.

Median in a date range: =AGGREGATE(17,6,B2:B100/((A2:A100>=DATE(2026,1,1))*(A2:A100<=DATE(2026,12,31))),2). Median of 2026 values only.

Median of top 10% by value: two-stage. First, find the 90th percentile: =PERCENTILE.INC(B2:B100,0.9) in cell D1. Then: =MEDIAN(IF(B2:B100>=D1,B2:B100)) array-entered, or the AGGREGATE equivalent. Median of the top decile only.

Running median: in C2 type =MEDIAN($B$2:B2) and drag down. Each row shows the median of all values up to that point — useful for tracking how a metric's centre moves over time.

Common pitfalls and how to avoid them

Treating text-numbers as numbers. If =COUNT(range) and =COUNTA(range) return different values, you have text contamination. Either coerce with VALUE inside an array IF, or use Data > Text to Columns to convert the column in place.

Forgetting array entry on older Excel. =MEDIAN(IF(...)) in Excel 2019 returns a wrong answer silently if you press plain Enter. Always Ctrl+Shift+Enter unless you're on 365/2021/Web. The AGGREGATE pattern dodges this entirely — yet another reason to default to it.

Using the wrong function for "middle". MEDIAN finds the middle of sorted values. MODE finds the most frequent value. AVERAGE finds the mean. Three different statistics, three different functions. Mixing them up — especially confusing mode with median in a report — is a credibility-killer.

Median of medians. Median doesn't aggregate cleanly. The median of (monthly medians) isn't the same as the median of (all daily values). For a true overall median, point your formula at the underlying detail, not at a summary row of monthly medians.

Sample size of 1 or 2. Median is meaningful on small samples only down to a point. With two values, median is just their average. With one value, it's that value. Below five or six observations, any central-tendency statistic is fragile — note the sample size next to the median in any report.

The median deserves a spot in every analyst's toolkit. It's the right summary statistic for skewed data, it shrugs off outliers, and it's exactly one function call in Excel — until you need conditions or a Pivot Table, at which point a single AGGREGATE pattern handles ninety percent of real-world cases.

Save the conditional median snippet somewhere you can grab it later. =AGGREGATE(17,6,values/(conditions),2) is the one you'll reach for most often. Once it's muscle memory, MEDIANIF-style problems take seconds instead of minutes — and you'll never again have to remember whether the array braces went on this version of Excel. Keep a second snippet handy for excluding zeros, another for date-range filters, and a fourth for the multi-condition variant. Four formulas, every conditional-median question you'll ever face.

If you're building a report, label your central-tendency choices. "Median household income" is clear. "Average" by itself is ambiguous because half your readers will assume mean and the other half won't think about it. A single line under the headline — "Median: $54,200 (n=312)" — tells your audience exactly what you measured and how big the sample was. That habit alone has saved more reports than any formula trick. Pair it with a small note about sample size, and the document defends itself when somebody asks where the number came from.

One last thought before you close this tab. The MEDIAN function is the easy part — the hard part is knowing when to reach for it. A salary review, a load-time benchmark, a survey result skewed by a handful of strong opinions: those are median territory. A symmetric distribution of test scores or a forecast of total revenue: lean on the mean. Knowing the difference, and being able to justify it in one sentence, separates a competent analyst from a great one.

Practice cements this stuff. Try a handful of Excel quizzes on this site to drill the function syntax and the array-vs-AGGREGATE choice — the more reps you get on real datasets, the faster you'll stop pausing to remember syntax and start thinking about what the numbers actually mean. Build your snippet library once, share it with your team, and the next time someone asks "what's the median?" you'll have an answer in under a minute.