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

Excel's MEDIAN function calculates the middle number in a list of values — the point where half the numbers fall above and half fall below. If you have an odd number of values, MEDIAN returns the exact middle one. If you have an even number of values, it returns the average of the two middle numbers.

The function works the same way in Excel for Windows, Excel for Mac, and Excel Online. You type the formula, point it to your data, and it returns the result in a single cell.

Key Takeaways

  • Type =MEDIAN(A1:A10) to find the median of cells A1 through A10, replacing the range with your actual data location.
  • MEDIAN ignores empty cells and text, so you can include a range with gaps and it will still calculate correctly.
  • You can use MEDIAN with multiple separate ranges by typing =MEDIAN(A1:A5,C1:C5) to include non-adjacent columns.
  • MEDIAN differs from AVERAGE: the median is the middle value, while the average adds all values and divides by the count.

Basic syntax: typing the MEDIAN formula

Click the cell where you want the result to appear. Type an equals sign, then the word MEDIAN, then open a parenthesis. Inside the parenthesis, type the range of cells containing your numbers — for example, A1:A10. Close the parenthesis and press Enter.

The complete formula looks like this: =MEDIAN(A1:A10). Excel will calculate the median and display it in that cell. If your data is in a different column or row, substitute those cell references instead.

Working with data in different locations

If your numbers are spread across multiple separate ranges, you can include all of them in one MEDIAN formula. Type =MEDIAN(A1:A5,C1:C5) to calculate the median of cells A1 through A5 and also C1 through C5 together. Separate each range with a comma.

You can also type individual cell references instead of ranges. For example, =MEDIAN(A1,A3,A7,B2) will find the median of just those four cells, skipping any cells in between. This is useful when your data is scattered or when you want to exclude certain rows.

How MEDIAN handles empty cells and text

MEDIAN automatically skips empty cells, so you do not need to clean them out first. If a cell in your range is blank, the function ignores it and calculates based only on the cells that contain numbers.

If a cell contains text, MEDIAN skips that cell too. This means you can include a range that has labels, notes, or other non-numeric data mixed in, and the function will still work correctly. Only numeric values are used in the calculation.

MEDIAN versus AVERAGE: when to use each one

MEDIAN and AVERAGE both describe the center of a dataset, but they work differently. AVERAGE adds all the numbers and divides by how many there are. MEDIAN finds the middle value when all numbers are arranged in order.

Use MEDIAN when your data contains outliers — unusually high or low values that skew the picture. For example, if five houses on a street sold for $300,000, $310,000, $305,000, $320,000, and $2,000,000, the average price is $827,000, but the median is $310,000. The median better represents what a typical house on that street costs. Use AVERAGE when you want to know the total divided evenly across all items.

Practical example: finding the median of test scores

Suppose you have test scores in cells A2 through A11: 78, 85, 92, 88, 76, 95, 81, 89, 87, 83. Click cell A13 and type =MEDIAN(A2:A11). Press Enter. Excel arranges the scores in order (76, 78, 81, 83, 85, 87, 88, 89, 92, 95) and returns 86 — the average of the two middle values, 85 and 87.

If you add a new score to the list, the MEDIAN formula updates automatically. You do not need to retype it. Just make sure the new score is within the range you specified — if you add it to A12, you would need to change the formula to =MEDIAN(A2:A12).

Combining MEDIAN with other functions

You can nest MEDIAN inside other formulas. For example, =IF(MEDIAN(A1:A10)>50,"High","Low") will return "High" if the median is greater than 50, and "Low" if it is not. This lets you make decisions based on the median value.

You can also use MEDIAN with conditional logic. The function MEDIAN.IF does not exist in standard Excel, but you can use an array formula with MEDIAN and IF together to find the median of only cells that meet a certain condition. This is more advanced and requires pressing Ctrl+Shift+Enter instead of just Enter, but it is useful for filtered datasets.

Frequently Asked Questions

What is the difference between MEDIAN and MODE?

MODE finds the number that appears most often in a dataset, while MEDIAN finds the middle value. If your numbers are 2, 3, 3, 5, 7, the mode is 3 (it appears twice) and the median is 3 (it is the middle value). They can be the same or different depending on your data.

Can I use MEDIAN with a formula that generates numbers?

Yes. You can type =MEDIAN(A1:A10) even if the cells in A1:A10 contain formulas rather than typed numbers. MEDIAN looks at the result of each formula, not the formula itself, and calculates the median of those results.

Why does my MEDIAN formula show an error?

The most common cause is typing the range incorrectly — for example, =MEDIAN(A1:A) without a row number, or =MEDIAN(A1 A10) with a space instead of a colon. Check that your range uses a colon between the first and last cell, like A1:A10. If all cells in your range contain text or are empty, MEDIAN returns 0.

Does MEDIAN work with negative numbers?

Yes. MEDIAN treats negative numbers the same as positive numbers. If your data is -5, -2, 0, 3, 8, the median is 0. The function arranges all numbers in order and finds the middle one, regardless of sign.