The MEDIAN function finds the middle number in a list of values

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. This is different from the AVERAGE function, which adds all numbers and divides by how many there are — the median ignores extremely high or low outliers that would skew an average.

For example, if you have five test scores (65, 72, 88, 91, 100), the median is 88 because it sits in the middle. If you have four scores (65, 72, 88, 91), the median is 80 because Excel averages the two middle numbers (72 and 88). The median is useful when one or two extreme values would make an average misleading — like household income in a neighborhood where one mansion skews the average upward.

Key Takeaways

  • Type =MEDIAN(A1:A10) to find the middle value in cells A1 through A10, replacing the cell range with your actual data.
  • The MEDIAN function automatically sorts numbers and ignores text, empty cells, and logical values like TRUE or FALSE.
  • Use MEDIAN when one or two extreme values would make an average misleading, such as salary data or home prices.
  • You can use MEDIAN with non-contiguous ranges by separating them with commas: =MEDIAN(A1:A5,C1:C5).

How to enter the MEDIAN formula in a cell

Click on the cell where you want the result to appear. Type an equals sign to start the formula: =MEDIAN. Then type an opening parenthesis and the range of cells containing your numbers. For a column of data in cells A1 through A20, type =MEDIAN(A1:A20). Press Enter, and Excel calculates the median when ready.

The colon between A1 and A20 tells Excel to include every cell in that range. If your data is in a row instead — say B5 through H5 — type =MEDIAN(B5:H5). You can also type individual cell addresses separated by commas if your data is scattered: =MEDIAN(A1,A5,A10) would find the median of only those three cells.

When MEDIAN gives you a different answer than AVERAGE

Imagine you are tracking monthly sales for a small business: $2,000, $2,100, $2,050, $2,200, and $15,000 (a one-time bulk order). The average is $4,670, which makes a typical month look much stronger than it actually is. The median is $2,100, which better represents what a normal month looks like.

This happens because AVERAGE adds all five numbers and divides by five, so the $15,000 outlier pulls the result way up. MEDIAN ignores the size of the numbers and only cares about position — it finds the middle value no matter how extreme the highest or lowest numbers are. For data with outliers, the median is often more useful than the average.

Combining MEDIAN with other functions

You can nest MEDIAN inside other formulas. For example, =IF(MEDIAN(A1:A10)>50,"High","Low") checks whether the median is greater than 50 and returns "High" or "Low" accordingly. You can also use MEDIAN as part of a larger calculation: =MEDIAN(A1:A10)*1.1 finds the median and multiplies it by 1.1 to increase it by 10 percent.

If you want to find the median of only cells that meet a condition — for example, only sales over $1,000 — you will need a more complex formula. Excel does not have a built-in MEDIANIF function, but you can use an array formula with IF: =MEDIAN(IF(A1:A10>1000,A1:A10)). After typing this formula, press Ctrl+Shift+Enter (not just Enter) to make it work as an array formula. In Excel 365, you can also use =MEDIAN(FILTER(A1:A10,A1:A10>1000)) if the FILTER function is available in your version.

What happens when your data includes text or blank cells

Excel's MEDIAN function ignores text, blank cells, and logical values automatically. If you have a column with numbers, some empty rows, and a few text entries, MEDIAN skips all of those and calculates based only on the numeric values. This makes it forgiving when your data is messy.

However, if every cell in your range contains text or is empty, MEDIAN returns a #NUM! error because there are no numbers to find a median from. If you see this error, check that your range actually contains numeric data and that you have not accidentally included a header row that contains text instead of numbers.

Using MEDIAN with filtered or sorted data

MEDIAN always works on the actual values in your cells, regardless of how the data is sorted or filtered on screen. If you sort a column from highest to lowest, MEDIAN still returns the same result — it does not care about the order you see. If you filter a table to show only certain rows, MEDIAN still calculates based on all cells in the range you specified, including the hidden ones.

If you want MEDIAN to work only on visible (filtered) cells, you need a different approach. The SUBTOTAL function with code 12 calculates the median of visible cells only: =SUBTOTAL(12,A1:A10). This is useful when you have filtered a large dataset and want to know the median of only what you are currently viewing.

Common mistakes and how to fix them

The most common error is forgetting the equals sign at the start. If you type MEDIAN(A1:A10) without the =, Excel treats it as text and displays the formula itself instead of calculating. Always start with =.

Another mistake is using the wrong range. If your data is in A1:A10 but you type =MEDIAN(A1:B10), you are including column B as well, which changes the result. Double-check that your range covers only the cells with the numbers you want. If you see a result that seems too high or too low, click the cell with the formula and look at the range highlighted in blue — that shows you exactly which cells are included.

Frequently Asked Questions

Can I use MEDIAN with negative numbers?

Yes. MEDIAN treats negative numbers the same as positive ones — it sorts all numbers from smallest to largest and finds the middle value. If your range is (-10, -5, 0, 5, 10), the median is 0.

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 finds the values at the 25th, 50th, and 75th percentiles. Use QUARTILE when you need to understand the spread of your data across quarters.

Does MEDIAN work with dates?

Yes. Excel stores dates as numbers, so MEDIAN can find the middle date in a range. If you have dates from January 1 to December 31, MEDIAN returns the date closest to the middle of the year.

How do I find the median of multiple columns at once?

Type =MEDIAN(A1:A10,C1:C10) to include non-adjacent ranges. The comma tells Excel to include both ranges in the calculation. You can add as many ranges as you need, separated by commas.

What if I want the median of only the top 10 values in a list?

Use the LARGE function inside MEDIAN: =MEDIAN(LARGE(A1:A100,ROW(1:10))) and press Ctrl+Shift+Enter. This is an advanced technique that requires array formula syntax, but it returns the median of the 10 largest values in your range.