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

Excel's MEDIAN function returns the middle value when numbers are arranged from smallest to largest. 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. This is different from AVERAGE, which adds all values and divides by how many there are.

The basic syntax is =MEDIAN(number1, number2, ...) or =MEDIAN(range). You can point to a range of cells, type individual numbers separated by commas, or mix both methods. MEDIAN ignores empty cells and text, so it works even if your data has gaps.

Key Takeaways

  • Type =MEDIAN(A1:A10) to find the middle value in cells A1 through A10, replacing the cell references with your actual data range.
  • MEDIAN returns the exact middle value for odd-numbered lists and the average of the two middle values for even-numbered lists.
  • You can calculate MEDIAN for non-adjacent cells by separating ranges with semicolons (Windows) or commas (Mac): =MEDIAN(A1:A5;C1:C5).
  • MEDIAN ignores empty cells, text, and logical values, so it works on incomplete or mixed data without extra steps.

Using MEDIAN with a single range of cells

The most common way to use MEDIAN is to point to a range. Click the cell where you want the result to appear, then type the formula. For example, if your sales data is in cells B2 through B15, type =MEDIAN(B2:B15) and press Enter. Excel counts the cells, arranges the values from smallest to largest, and displays the middle number.

You can also select the range by clicking and dragging. Type =MEDIAN(, then click the first cell in your data, hold Shift, and click the last cell. Excel fills in the range automatically. Type the closing parenthesis and press Enter.

Calculating MEDIAN for non-adjacent cells

Sometimes your data is scattered across different parts of the sheet. You can include multiple separate ranges in one MEDIAN formula. On Windows, separate each range with a semicolon: =MEDIAN(A1:A5;C1:C5;E1:E5). On Mac, use a comma instead: =MEDIAN(A1:A5,C1:C5,E1:E5).

Excel treats all the values from all ranges as one combined list and finds the middle value. This is useful when your data is organized in columns with labels or gaps between sections, and you want to find the median across specific groups without moving the data around.

MEDIAN versus AVERAGE and MODE

MEDIAN, AVERAGE, and MODE each describe the center of your data in different ways. AVERAGE adds all values and divides by the count — it can be pulled high or low by extreme numbers. MEDIAN finds the middle value — it ignores how far apart the extremes are. MODE returns the value that appears most often.

If you have salaries of $30,000, $35,000, $40,000, $45,000, and $500,000, the AVERAGE is $130,000 (pulled up by the outlier), the MEDIAN is $40,000 (the true middle), and the MODE has no answer (no salary repeats). For skewed data with outliers, MEDIAN often tells a clearer story than AVERAGE.

Handling empty cells, text, and errors in MEDIAN

MEDIAN automatically skips empty cells, so you do not need to clean them out first. It also ignores text entries and logical values like TRUE or FALSE. If a cell contains an error like #DIV/0!, MEDIAN will return an error for the whole formula — you will need to fix or remove that cell.

If you want to exclude specific values or only include cells that meet a condition, use MEDIAN.IF (available in Excel 2019 and later, and in Excel Online). The syntax is =MEDIAN.IF(range, criteria). For example, =MEDIAN.IF(B2:B15, ">50") finds the median of only the values greater than 50.

Using MEDIAN in a table or with filtered data

If your data is in an Excel table, you can reference the table column directly. For a table named "Sales" with a column called "Amount", type =MEDIAN(Sales[Amount]). This formula updates automatically if you add or remove rows from the table.

When you filter a table to show only certain rows, MEDIAN still calculates using all rows in the range, including the hidden ones. If you want the median of only the visible (filtered) cells, use AGGREGATE instead: =AGGREGATE(12, 5, range). The 12 tells AGGREGATE to calculate MEDIAN, and the 5 tells it to ignore hidden rows.

Common mistakes and how to fix them

A frequent error is typing the range wrong. Double-check that your first cell reference comes before your second — =MEDIAN(A1:A10) is correct, but =MEDIAN(A10:A1) still works (Excel reverses it automatically). Make sure you are using a colon between the first and last cell, not a comma.

Another mistake is including a header row. If your data starts in A1 with a label like "Sales" and the actual numbers are in A2:A10, use =MEDIAN(A2:A10), not A1:A10. Including the text header will cause an error. If you use a table, this is handled for you — table headers are excluded automatically.

Frequently Asked Questions

What is the difference between MEDIAN and AVERAGE in Excel?

AVERAGE adds all values and divides by the count. MEDIAN finds the middle value when numbers are sorted from smallest to largest. AVERAGE is pulled toward extreme values; MEDIAN is not. For data with outliers, MEDIAN often better represents the typical value.

Can I use MEDIAN with text or mixed data types?

MEDIAN ignores text and empty cells, so it works on mixed data. However, if you want to find the median of only certain rows based on a condition (like "only sales over $100"), use MEDIAN.IF or AGGREGATE instead of plain MEDIAN.

How do I find the median of only visible cells after filtering?

Use the AGGREGATE function with the syntax =AGGREGATE(12, 5, range). The 12 tells AGGREGATE to calculate MEDIAN, and the 5 tells it to skip hidden rows. MEDIAN alone includes hidden rows in its calculation.

What happens if I have an even number of values?

MEDIAN returns the average of the two middle values. For example, if your sorted list is 10, 20, 30, 40, the two middle values are 20 and 30, so MEDIAN returns 25.

Can I use MEDIAN in a formula with other functions?

Yes. You can nest MEDIAN inside other functions or combine it with them. For example, =ROUND(MEDIAN(A1:A10), 2) rounds the median to two decimal places, or =IF(MEDIAN(A1:A10)>100, "High", "Low") compares the median to a threshold.