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

Excel's MEDIAN function returns the middle number in a set of data — the value where half the numbers fall below it and half fall above it. If you have an odd number of values, MEDIAN picks the exact middle one. If you have an even number, it averages the two middle values together.

The basic formula is =MEDIAN(range), where range is the cells containing your numbers. You type it into any cell, and Excel calculates the result when ready. This is different from the AVERAGE function, which adds all values and divides by how many there are — the median ignores extremely high or low outliers and often better represents a typical value.

Key Takeaways

  • Type =MEDIAN(A1:A10) into any cell to find the middle value of numbers in cells A1 through A10.
  • The median ignores outliers better than average does, making it useful for data like home prices or test scores where one extreme value can skew the picture.
  • You can select non-adjacent cells by holding Ctrl (Windows) or Command (Mac) and clicking each range, then typing =MEDIAN(A1:A5,C1:C5).
  • Excel automatically ignores empty cells and text when calculating the median, so you do not need to clean your data first.

Entering the MEDIAN formula in a single cell

Click the cell where you want the median to appear. Type =MEDIAN( and then select the range of cells containing your numbers. You can click and drag to highlight them, or type the range directly — for example, A1:A20 means all cells from A1 to A20 in column A. Close the parenthesis and press Enter.

Excel shows the result when ready. If your data is in rows instead of columns, the same method works: =MEDIAN(A1:E1) finds the median of five numbers across row 1. The function works with any contiguous block of cells, whether they run horizontally or vertically.

Using MEDIAN with non-adjacent cells

Sometimes your data is scattered across different parts of the spreadsheet. Hold Ctrl (on Windows) or Command (on Mac) and click each separate range you want to include. Then type =MEDIAN( and the ranges appear automatically separated by commas: =MEDIAN(A1:A5,C1:C5,E1:E5).

This is useful when you have data organized in columns with labels or gaps between them. Excel treats all the selected cells as one combined list and finds the median of all values together. You can include as many separate ranges as you need.

When MEDIAN gives you a different answer than AVERAGE

Imagine you have five home sale prices: $200,000, $210,000, $215,000, $220,000, and $1,500,000. The average is $589,000 because that one mansion pulls the total up dramatically. The median is $215,000 — the actual middle price, which better represents what a typical home in that area sold for.

This matters when your data includes outliers — unusually high or low values that do not represent the typical case. Test scores, salaries, property values, and customer wait times often benefit from median instead of average. If your numbers are fairly consistent with no extreme values, average and median will be very close to each other.

Handling empty cells and text in your data

Excel automatically skips empty cells when calculating the median, so you do not need to delete blank rows or reorganize your data. If a cell contains text instead of a number, Excel ignores it too. This means you can run MEDIAN on a range that includes labels, missing data, or mixed content without getting an error.

If every cell in your range contains text or is empty, Excel returns a #NUM! error because there are no numbers to find a median for. Otherwise, the function works on whatever numbers it finds and ignores everything else.

Combining MEDIAN with other functions

You can nest MEDIAN inside other formulas. For example, =IF(MEDIAN(A1:A10)>100,"High","Low") checks whether the median is greater than 100 and returns either "High" or "Low". Or use =MEDIAN(IF(B1:B10="Yes",A1:A10)) to find the median of values in column A only where column B says "Yes" — though this requires pressing Ctrl+Shift+Enter instead of just Enter to work as an array formula.

Most commonly, people pair MEDIAN with conditional logic to compare it against a threshold, or use it alongside AVERAGE to see how different the two measures are. These combinations let you build more sophisticated analysis without needing separate calculations.

Finding the median of a filtered or sorted list

MEDIAN works on whatever cells you specify, regardless of whether the data is sorted or filtered. If you sort your numbers from smallest to largest, the median is still the middle value — sorting does not change the result, it just makes it easier to see which value is in the middle. If you explore an Excel filter to hide some rows, MEDIAN still includes the hidden values in its calculation.

If you want the median of only the visible (filtered) rows, you need a different approach: the AGGREGATE function with option 12 ignores hidden rows. The formula would be =AGGREGATE(12,5,A1:A10), where 12 means median and 5 means ignore hidden rows. This is less common but useful when you are working with filtered data and want to exclude the hidden values.

Frequently Asked Questions

What is the difference between MEDIAN and AVERAGE?

AVERAGE adds all numbers and divides by how many there are. MEDIAN finds the middle value. If you have one extremely high or low number, AVERAGE changes dramatically but MEDIAN stays stable. Use MEDIAN when outliers might distort the picture.

Can I use MEDIAN with dates or times?

Yes. Excel stores dates and times as numbers internally, so MEDIAN works on them. =MEDIAN(A1:A10) on a column of dates returns the middle date. The result appears as a number, so you may need to format the cell as a date to see it properly.

What happens if I have an even number of values?

Excel averages the two middle values. If you have four numbers (10, 20, 30, 40), the two middle values are 20 and 30, so MEDIAN returns 25. This is the standard statistical definition of median for even-sized datasets.

Does MEDIAN work across multiple sheets?

Yes. Type =MEDIAN(Sheet1.A1:A10,Sheet2.A1:A10) to include ranges from different sheets. Use the sheet name followed by a period, then the cell range. This works for any number of sheets.

Why does my MEDIAN formula show an error?

The most common cause is selecting a range with no numbers in it — only text or empty cells. Check that your range actually contains numeric data. If the formula itself has a typo (like missing parenthesis), Excel will show a syntax error. Click the cell and review the formula bar at the top.