How to Find the Median in Excel: Step-by-Step (2023 Guide)
“When Bill Gates walks into a bar, everyone becomes a millionaire on average”.
Outliers (like Mr. Bill) can skew data so much that simple statistical indicators (like averages) become misguiding.
In some of those cases, finding the median is more insightful 💡
Let me show you how to do that in Excel with the MEDIAN function, in just a few minutes.
Please download this workbook if you want to follow along as you read this guide.
Table of Contents
What is a median?
Median refers to the middle term in a sorted set of data. It separates a group of numbers into two equal parts and acts as the center of data when arranged symmetrically.
If there are any empty cells in the selected data range, the MEDIAN function will ignore them.
If your data consists of an odd number of values, the middle term will be the median of your data set.
But if your data set contains an even number of values, the average of the two middle values will make the median.
Luckily, Excel offers a built-in MEDIAN Function that you can use to calculate the median of a data range. Its syntax is:
=MEDIAN (number1, [number2], ..)
Number1, Number2 are the numeric values whose median is to be calculated.
The first argument, Number 1, is mandatory. It can be a cell reference, named range, date, or even logical values. Number 2 uses the same types of values, but its use is optional.
You can use up to 255 arguments for the Excel MEDIAN formula in Excel. But the first two arguments suffice for most cases.
How to find the median in Excel
Let’s now see how to find the median in Excel.
Say we have the following data set.
We want to find the median salary.
For that, we will apply the following formula:
Excel returns the result as:
As evident, the MEDIAN returns a value of ‘69,740’, which isn’t even included in the list.
That’s because the total count of list entries is an even number. And for even numbers, Excel returns the median as the average of the two middle numbers.
You can use the formula with odd numbers as well. Excel will divide the list in half and return the middle number as result.
The MEDIAN function also counts logical values TRUE and FALSE. You can enter them directly into a MEDIAN formula.
If a set is (FALSE, TRUE, 2, 3, 4, 5, 6), its median returned will be 4. The TRUE and FALSE values here represent 1, and 0, respectively.
Let’s now also calculate the median of the joining year.
We will first find the median of an even number of values.
The MEDIAN function returns the result:
Similarly, for an odd number of values, the result is:
That was pretty easy, no? 😀
That’s it – Now what?
And that’s how simple it is to use the MEDIAN function. All you need to do is sort the data in the proper order and apply the formula. Excel will return your median just as you expect 🤓
In this article, we learned what a median was and its uses. We also saw how to find the median of a data set having odd and even numbers of values.
MEDIAN truly is a fantastic function of Excel that helps ease your calculations. Similar to this, there are many other functions in Excel that you are missing out on.
If you have just started out, you should try practicing Excel’s SUM, AVERAGE, and COUNT functions.
You can learn them in my 30-minute free email course here. It’s delivered right into your inbox only at the cost of your email address 🤗
If you enjoyed reading this article, we’re sure you’d love to read more. Try the AVERAGE Function in Excel, Weighted AVERAGE, AVERAGEIF, and more.