The median is the middle value in a sorted list of numbers — and unlike the average, it isn't thrown off by a few unusually high or low values. Excel calculates it in one step with the built-in MEDIAN function, no manual sorting required.
This guide from Anchor AI Tools covers the basic formula, how to calculate a median with a condition, and when median gives you a more honest picture of your data than the average does.
Quick Answer: To calculate the median in Excel, use =MEDIAN(range) — for example =MEDIAN(A2:A11). Excel automatically sorts the values and returns the middle one, or the average of the two middle values if the count is even. To find a median for only values matching a condition, use an array formula: =MEDIAN(IF(criteria_range=criteria,range)) entered with Ctrl+Shift+Enter.
The median is the middle value in a dataset once it's sorted from smallest to largest. If there's an odd number of values, the median is the single middle one. If there's an even number, the median is the average of the two middle values.
Because it only cares about the middle position, the median isn't dragged up or down by extreme outliers the way an average is — which is why household income, home prices, and salary data are usually reported as medians rather than averages.
=MEDIAN(range)
Excel sorts the values automatically and returns the middle oneMEDIAN automatically ignores empty cells and text, so you don't need to clean the range first — though it's still good practice to check what's actually in it.
A small business owner wants the typical sale amount from 9 recent transactions, one of which is an unusually large outlier order.
| Sale | Amount ($) |
|---|---|
| 1 | 42 |
| 2 | 38 |
| 3 | 51 |
| 4 | 45 |
| 5 | 39 |
| 6 | 47 |
| 7 | 44 |
| 8 | 950 |
| 9 | 41 |
📋 Formula Used
With amounts in B2:B10:
The $950 outlier order pulls the average up to $155.22 — a figure that doesn't represent any typical sale. The median of $44 reflects what a normal transaction actually looks like.
Excel has no built-in MEDIANIF function, but you can build the same result with an array formula that combines MEDIAN and IF.
In older Excel versions, confirm this formula with Ctrl+Shift+Enter instead of just Enter — it needs to run as an array formula. In Excel 365 and Excel 2021, array formulas work automatically without the extra keystroke.
| Use Median When | Use Average When |
|---|---|
| Your data has outliers (salaries, home prices, one-off large orders) | Your data is fairly evenly distributed with no extreme values |
| You want the "typical" value, not the mathematical center | You need every value to count equally toward the result |
| You're reporting on income, pricing, or duration data | You're calculating totals, rates, or proportional splits |
Assuming median and average will match. They're only equal when data is perfectly symmetrical — in most real datasets, they differ, sometimes significantly.
Forgetting Ctrl+Shift+Enter for conditional medians. In older Excel versions, a conditional MEDIAN(IF(...)) formula entered normally can return an error or wrong result instead of the array calculation.
Including header rows or labels in the range. Text in the selected range is ignored by MEDIAN, but it's still worth double-checking your range starts at the first data row.
Using median when you actually need a total. Median describes a typical value — it's not additive, so you can't use it to estimate a sum or total the way you could with a count times an average.
This comes up for small business owners looking at typical sale size, HR teams reporting median salary instead of a skewed average, real estate professionals discussing median home prices, and analysts summarizing survey or performance data that includes outliers.
Looking at variability instead of typical value? See our guide on standard error in Excel. Testing whether a difference between two groups is real? See how to calculate a p-value in Excel.
Need more free calculators and formula guides?
Explore Free Tools →AVERAGE) adds all values and divides by the count. Median (MEDIAN) finds the middle value once sorted. Median is less affected by outliers.MEDIAN automatically ignores blank cells and text values in the selected range, so you don't need to remove them first.MEDIAN handles this automatically — it returns the average of the two middle values once the data is sorted. You don't need a different formula.=MEDIAN(IF(criteria_range=criteria,range)), confirmed with Ctrl+Shift+Enter in older Excel versions.=MEDIAN(A2:A10,C2:C10), and Excel treats them as one combined dataset.MEDIAN with identical syntax, including array-based conditional medians.Standard Error in Excel
How precise your sample average really is.
P-Value in Excel
Test whether a difference in your data is statistically significant.
SEM in Excel
Use standard error for chart error bars and confidence intervals.
Excel Ran Out of Resources
7 fixes for this common calculation error.
Percentage Calculator
Calculate percentage increase, decrease, and discounts.
SEO Audit Tool
Check your site's SEO health and AI visibility.
Published by Anchor AI Tools · © 2026 Anchor AI Tools