How to Find Median in Excel: The Easy Way and the Long Way

If you need to pinpoint the middle value in any list or vector, and if you’re working with a skewed distribution or if you need a non-parametric statistic for some other reason, you can use the very aptly named MEDIAN() function in Excel. Keep reading, and I’ll show you how it works.

What Does Excel MEDIAN() Do?

Let’s me clarify exactly what MEDIAN() is/offers and why it’s useful. 

While measures like the mean can be thrown off by unusually high or low values, MEDIAN() gives you the number that sits in the center of your dataset in a way that is robust against outliers. This is why the median is reported for things like housing prices. 

When you arrange your numbers from smallest to largest, MEDIAN() picks the one in the middle. (And if you have an even count, it averages the two central numbers.)

Excel MEDIAN() Syntax and Arguments

The MEDIAN() syntax is relatively straightforward:

=MEDIAN(number1, [number2], ...)

number1, number2, ...: You can enter individual numbers, cell references, or entire ranges. 

You only need one argument to get started (it wouldn’t make a whole lot of sense to find the median of a vector with one number (the median of five, for example, if five)) but you can include as many arguments you need. 

If you’re wondering, MEDIAN() will automatically skip over empty cells and ignore any text, so you don’t need to worry about cleaning up your data beforehand. In other words, if you have a lot of blank cells, it would not add a bunch of zeros.

A Quick Example: Excel MEDIAN() in Action

Imagine you have a list of exam scores in cells A2 through A6:

To find the median score in this group, simply use:

=MEDIAN(A2:A6)

This is probably the most common kind of use case, where you input a range into the function.

Here’s how Excel is working through it: Excel first sorts the values so it looks like this: 78, 85, 88, 90, 92, and then it returns 88, which the value in the middle. 

How Excel MEDIAN() Handles Even and Odd Numbers

MEDIAN() adapts when your dataset has an even or odd number of entries. This is important because the function’s behavior changes slightly depending on the count:

• Odd number of values: MEDIAN() returns the single center value.

• Even number of values: MEDIAN() averages the two middle numbers.

Suppose you add another score—say, 94 in cell A7:

Now, enter:

=MEDIAN(A2:A7)

Excel sorts the scores as (78, 85, 88, 90, 92, 94). The two middle values are 88 and 90. MEDIAN() averages these and gives you 89. 

Excel MEDIAN() with Multiple Ranges or Numbers

MEDIAN() also handles more complex situations. It can accept multiple ranges, or a mix of ranges and individual numbers.

=MEDIAN(A2:A6, B2:B6, 100)

This formula takes all the numbers from A2:A6, all from B2:B6, and adds the value 100 into the mix, then finds the median of everything. So you don’t have to arrange all your numbers in one row or column before using the function. 

Finding the MEDIAN() Date

Excel treats dates as serial numbers, so MEDIAN() can find the “middle” date. This is a useful trick for project timelines.

How MEDIAN() Handles Text, Empty Cells, and Errors

You’re going to encounter cells that are empty, contain text, or logical values. It’s important to know how MEDIAN() reacts with these cates:

• Empty cells: Ignored completely. I mentioned this earlier.

• Text: Skipped if it appears in a referenced cell or range. However, if you type text directly into the formula (like =MEDIAN("apple", 10)), Excel will return a #VALUE! error, so don’t do this.

• Logical values (TRUE, FALSE): If included as part of a range, they’re ignored. If you type them directly as arguments, TRUE counts as 1 and FALSE as 0.

What If There Were No MEDIAN() Function?

You could find the median without the MEDIAN() function. I’ll show you how, if you’re curious, but I don’t recommend it because, as you will see, it’s too much work.

First, count how many numbers you have with =COUNTA(). Then, get the position of the middle value with ROUNDUP(). Then, extract that value with SMALL(). Your formula might look like this:

=SMALL(A2:A6, ROUNDUP(COUNTA(A2:A6)/2, 0))

This works when there’s an odd number of values. For even counts, you can average two SMALL() calls:

=(SMALL(A2:A7, COUNTA(A2:A7)/2) + SMALL(A2:A7, COUNTA(A2:A7)/2 + 1)) / 2

As you can see, it gets the same answer. But save yourself the trouble and use MEDIAN())

Common Mistakes

The following things can trip you up:

• Including text directly: Typing =MEDIAN(10, "apple", 20) produces a dreaded #VALUE! error.

• Mixing ranges of different sizes: MEDIAN() simply combines all the numbers; the size or shape of each range doesn’t matter.

Combining MEDIAN() with Other Functions

You can combine MEDIAN() with IF() to find the median of values that meet a condition. Do this with an array formula. For example, to calculate the median of scores above 80:

=MEDIAN(IF(A2:A10>80, A2:A10))

You may also combine MEDIAN() with FILTER()

=MEDIAN(FILTER(A2:A10, A2:A10>80))

These approaches let you zero in on specific subsets of your data, giving you a more targeted result.

Conclusion

Remember that MEDIAN()skips blanks, ignores text, and gives you a good idea of the middle value no matter the shape of your dataset. It’s good for those scenarios where outliers might throw off the average, like income distributions, as I mentioned, or project durations.

If you want to keep building your Excel skills, take a look at our Data Analysis in Excel course, where you'll learn about other summary stats and then learn those analysis techniques that drive better decisions (and will impress your boss).