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

Excel's MEDIAN function calculates the middle number in a set of values — the point where half the numbers fall above and half fall below. This is different from the average (which adds everything up and divides), and it's useful when one very large or very small number would skew your picture. For example, if you're looking at house prices in a neighborhood and one mansion sells for ten times the typical price, the median tells you what a typical house actually costs.

The function works the same way in Excel, Google Sheets, and most other spreadsheet programs. You type a formula, point it at your data, and it returns the middle value in seconds.

Key Takeaways

  • Type =MEDIAN(A1:A10) to find the middle value in cells A1 through A10, replacing the cell references with your own data range.
  • The MEDIAN function ignores empty cells and text, so you can include extra rows without breaking the calculation.
  • If you have an even number of values, Excel returns the average of the two middle numbers — for example, the median of 10, 20, 30, 40 is 25.
  • You can use MEDIAN on data spread across multiple columns or non-adjacent cells by separating ranges with semicolons or commas, depending on your regional settings.

How to enter the MEDIAN formula in a single column

Start by opening your spreadsheet and locating the column that holds your numbers. Click on an empty cell below or to the right of your data — this is where the median result will appear. Type the formula =MEDIAN( and then highlight the range of cells containing your numbers, or type the range directly. For example, if your numbers are in cells A1 through A20, type =MEDIAN(A1:A20) and press Enter.

Excel will when ready show the median value in that cell. If you need to move the result elsewhere, click the cell containing the formula and drag it to a new location — the formula will adjust automatically.

Finding the median across multiple columns or non-adjacent cells

If your data is scattered across different columns or separated by rows you want to skip, you can include multiple ranges in one formula. Type =MEDIAN(A1:A10,C1:C10) to find the median of cells A1 through A10 and C1 through C10 combined. In some regions (particularly outside North America), use a semicolon instead of a comma: =MEDIAN(A1:A10;C1:C10).

You can add as many ranges as you need. Excel treats them all as one continuous list of numbers when calculating the median. This is helpful when your data is organized by category or time period in separate columns.

What happens when your data includes empty cells or text

Excel's MEDIAN function automatically skips empty cells and any cells containing text, so you don't need to clean up your data first. If you have a column with some blank rows or a few cells with notes instead of numbers, the formula will ignore those and calculate the median of only the numeric values.

This also means you can safely include extra rows in your range without worrying about breaking the calculation. For instance, =MEDIAN(A1:A100) will work correctly even if only 30 of those cells contain numbers.

Understanding the result when you have an even number of values

When your data set contains an even number of values, there is no single middle number. Excel handles this by averaging the two middle values. If you have four numbers — 10, 20, 30, 40 — the two middle values are 20 and 30, so the median is 25 (their average).

This behavior is standard across spreadsheet programs and matches how median is calculated in statistics. It ensures you always get a single result, even when the data doesn't divide evenly.

Comparing MEDIAN to AVERAGE and MODE in Excel

Excel offers three functions for finding a typical value, and each tells you something different. The AVERAGE function adds all numbers and divides by how many there are — it's pulled up or down by extreme values. The MEDIAN function finds the middle point and ignores how far away the extremes are. The MODE function finds the value that appears most often in your list.

For a list like 10, 15, 18, 20, 100, the average is 32.6 (pulled up by the 100), the median is 18 (the true middle), and there is no mode (no number repeats). Which one you use depends on your question: if you want the typical value and suspect outliers exist, use MEDIAN. If you want the mathematical average, use AVERAGE. If you want the most common value, use MODE.

Frequently Asked Questions

Can I use MEDIAN on data in different sheets?

Yes. Type =MEDIAN(Sheet1.A1:A10,Sheet2.A1:A10) to include ranges from multiple sheets in the same calculation. The exact syntax depends on your spreadsheet program — Google Sheets uses an exclamation mark (Sheet1!A1:A10) while Excel uses a period (Sheet1.A1:A10). Check your program's documentation if you're unsure of the format.

What if all my cells are empty or contain only text?

Excel will return a #NUM! error, which means there are no numbers to calculate a median from. Check that your data range is correct and that the cells actually contain numeric values rather than text that looks like numbers.

Does MEDIAN work with negative numbers?

Yes. Negative numbers are treated like any other number. If your list is -10, -5, 0, 5, 10, the median is 0. The function doesn't distinguish between positive and negative values.

Can I use MEDIAN inside another formula?

Yes. You can nest MEDIAN inside other functions. For example, =IF(MEDIAN(A1:A10)>50,"High","Low") will return "High" if the median is greater than 50, or "Low" if it's not. This is useful when you want to make a decision based on the median value.