The MEDIAN function finds the middle value in a list of numbers

Excel's MEDIAN function sorts your numbers from smallest to largest and returns the one in the middle. If you have an even count of numbers, it averages the two middle values. You type =MEDIAN() with your range of cells inside the parentheses, and Excel does the sorting and calculating for you.

The median is useful when you want to know a typical value but your data has outliers that would skew an average. If you're tracking home prices on a street and one mansion sells for $5 million while the rest sell for $400,000, the median price tells you what a normal house actually costs. The mean (average) would be misleading.

Key Takeaways

  • Type =MEDIAN(A1:A10) to find the middle value in cells A1 through A10; Excel automatically sorts and calculates.
  • The median works with any range size — single columns, rows, or non-adjacent cells — as long as they contain numbers.
  • If your range has an even number of values, Excel returns the average of the two middle numbers, not a value from your data.
  • You can combine MEDIAN with other functions like IF to find the median of only cells that meet a condition, though the syntax is more complex.

Basic MEDIAN syntax and what each part does

The simplest MEDIAN formula has two parts: the function name and the range. Type =MEDIAN(A1:A10) in any empty cell. Excel reads this as "find the median of the numbers in cells A1 through A10." The colon between A1 and A10 means "all cells from A1 to A10 inclusive."

You can also list individual cells separated by commas: =MEDIAN(A1,A3,A5,B2) tells Excel to find the median of only those four cells, skipping everything in between. This is useful when your data is scattered or when you want to exclude certain rows.

The result appears in the cell where you typed the formula. If your range is A1:A10 and you type the formula in cell C1, the median value shows up in C1, and the original data in column A stays unchanged.

Working with different data ranges and layouts

MEDIAN works with columns, rows, or a rectangular block of cells. If your numbers run down column B from B2 to B47, use =MEDIAN(B2:B47). If they run across a row — say C5 through H5 — use =MEDIAN(C5:H5). If your data fills a rectangle like A1:D10, use =MEDIAN(A1:D10) and Excel treats all 40 cells as one list.

Excel ignores empty cells and text within your range, so you don't have to clean them out first. If A1:A10 contains the numbers 5, 8, 12, and 20 plus six empty cells, MEDIAN calculates the median of just those four numbers. However, cells containing text that looks like numbers (like "5" stored as text rather than a number) are also ignored.

You can also reference ranges from different sheets. Use =MEDIAN(Sheet2!A1:A10) to find the median of cells in Sheet2. The exclamation mark tells Excel to look in a different sheet.

Median with an even versus odd count of numbers

When you have an odd count of numbers — say 5, 8, 12, 15, 20 — the median is the middle one: 12. Excel returns exactly that value.

When you have an even count — say 5, 8, 12, 15 — there is no single middle value. Excel returns the average of the two middle numbers: (8 + 12) ÷ 2 = 10. The result 10 may not appear anywhere in your original data, but it is the correct median. This behavior is standard in statistics and helps represent the center of your data fairly.

Finding the median of cells that meet a condition

The basic MEDIAN function works on any range you give it, but sometimes you need the median of only cells that match a condition — for example, the median salary of employees in the Sales department, not the whole company.

Excel does not have a built-in MEDIANIF function, but you can use an array formula. In Excel 2019 and earlier, type =MEDIAN(IF(B2:B100="Sales",C2:C100)) and press Ctrl+Shift+Enter instead of just Enter. The IF part filters the range, and MEDIAN calculates the result. In Excel 365, you can often use =MEDIAN(IF(B2:B100="Sales",C2:C100)) and press Enter normally because Excel handles the array automatically.

This approach is more complex than a straightforward MEDIAN, so use it only when you genuinely need to exclude rows. For most tasks, filtering your data first and then running MEDIAN on the filtered range is simpler and easier to understand later.

Common mistakes and how to fix them

The most common error is including text headers in your range. If your data starts in A1 with the label "Prices" and the actual numbers begin in A2, use =MEDIAN(A2:A10), not A1:A10. Excel ignores the text header, but including it makes the formula harder to read and maintain.

Another mistake is using MEDIAN on a range that contains no numbers. If all cells are empty or text-only, MEDIAN returns an error. Check that your range actually holds numeric data before you run the formula.

If your result seems wrong, verify that the cells contain numbers, not text that looks like numbers. A cell showing "5" but stored as text will be ignored. You can check by clicking the cell — if the formula bar shows an apostrophe before the number (like '5), it is text. Use the VALUE function or paste-special-as-values to convert it.

When to use MEDIAN instead of AVERAGE

Use AVERAGE when your data is fairly evenly distributed and you want the arithmetic mean. Use MEDIAN when outliers exist or when you want to know a typical value that actually appears in your data range.

In a dataset of test scores — 65, 72, 78, 81, 88 — the average and median are close (76.8 and 78). But if one student scored 15, the average drops to 60.4 while the median stays at 72, which better represents how most students performed. The median is also useful for skewed data like income (where a few very high earners pull the average up) or home prices (where one mansion skews the mean).

If you are unsure which to use, calculate both and see which one makes more sense for your question. Excel makes this straightforward — put =AVERAGE in one cell and =MEDIAN in another, and compare the results.

Frequently Asked Questions

Can I find the median of multiple non-adjacent cells?

Yes. Type =MEDIAN(A1,A3,A5,C2,C4) to find the median of those five specific cells. Use commas to separate each cell or range, and Excel treats them all as one list.

What happens if my range contains negative numbers?

MEDIAN handles negative numbers the same way as positive ones — it sorts them from smallest to largest and returns the middle value. A range of -10, -5, 0, 5, 10 has a median of 0.

Does MEDIAN work with dates or times?

Yes, because Excel stores dates and times as numbers internally. MEDIAN will return a date or time value if your range contains them, though the result may display as a number until you format the cell as a date or time.

Can I use MEDIAN in a conditional formula to find the median of the largest values?

Not directly with MEDIAN alone. You would need to combine it with other functions like LARGE or use filtering. For most tasks, sorting your data and then running MEDIAN on the sorted range is clearer.

What is the difference between MEDIAN and QUARTILE?

MEDIAN finds the middle value (the 50th percentile). QUARTILE divides your data into four equal parts and returns the value at a specific quarter — for example, the 25th percentile or 75th percentile. Use QUARTILE when you need to understand the spread of your data beyond just the middle.