MEDIAN and MODE in Excel: Find the Typical Value
By ExcelReady TeamUpdated 6 min read
The short answer
MEDIAN returns the middle value of a range once it is sorted, and MODE returns the value that appears most often. Type =MEDIAN(B2:B8) or =MODE(D2:D11). Use MEDIAN instead of AVERAGE when one extreme value, like a founder's salary, would pull the average away from what is typical. MODE returns #N/A if nothing repeats.
Key takeaways
- =MEDIAN(range) sorts the numbers and returns the one in the middle.
- With an even count, MEDIAN returns the average of the two middle numbers.
- =MODE(range) returns the most frequent number, and #N/A if no number repeats.
- An outlier moves AVERAGE a lot but MEDIAN very little.
- MODE.SNGL is the newer name for MODE; both return the same result.
On this page+
Excel MEDIAN and MODE describe a "typical" value in two different ways. MEDIAN returns the middle number once the list is sorted, and MODE returns the number that appears most often. They matter most when AVERAGE gives a misleading answer, which is common with salaries, prices and order sizes.
What do MEDIAN and MODE do in Excel?#
MEDIAN finds the middle value and MODE finds the most common value. Both take a range, just like AVERAGE.
| Function | Syntax | Returns | Example on 1, 1, 2, 5, 100 |
|---|---|---|---|
| AVERAGE | =AVERAGE(range) | The mean: total ÷ count | 21.8 |
| MEDIAN | =MEDIAN(range) | The middle value when sorted | 2 |
| MODE | =MODE(range) | The value that appears most often | 1 |
The example shows why you might need all three. One large number, 100, pushes the average to 21.8, far above most of the values. The median stays at 2.
You don't need to sort the data before using MEDIAN. Excel sorts it internally and leaves your sheet unchanged.
Why one outlier skews the average#
An outlier is a value far from the rest. It pulls the average towards it, but it barely moves the median. Here is a small company with six staff and a founder:
| A | B | |
|---|---|---|
| 1 | Employee | Salary |
| 2 | Aisha | 48,000 |
| 3 | Ben | 52,500 |
| 4 | Carla | 51,000 |
| 5 | Dmitri | 55,000 |
| 6 | Elena | 49,500 |
| 7 | Farid | 53,000 |
| 8 | Grace (founder) | 310,000 |
| Question | Formula | Result |
|---|---|---|
| Average salary, everyone | =AVERAGE(B2:B8) | 88,428.57 |
| Median salary, everyone | =MEDIAN(B2:B8) | 52,500 |
| How far the founder pulls the average up | =AVERAGE(B2:B8)-MEDIAN(B2:B8) | 35,928.57 |
Six of the seven people earn between 48,000 and 55,000, yet the average is over 88,000. The median, 52,500, is what the person in the middle earns. That's why salaries and house prices are usually reported as a median. For the full argument, read AVERAGE vs MEDIAN: which tells the truth?
Results like 88,428.57 have long decimals. To show a clean figure in a report, wrap the formula in ROUND, as explained in how to round numbers in Excel.
How to use MEDIAN in Excel#
Type =MEDIAN(, select the range and type ). Excel puts the numbers in order and returns the one in the middle.
Sorted, the seven salaries are 48,000, 49,500, 51,000, 52,500, 53,000, 55,000 and 310,000. The fourth one is the middle, so =MEDIAN(B2:B8) returns 52,500.
MEDIAN also accepts several ranges separated by commas. If two teams' salaries sit in B2:B8 and E2:E8, =MEDIAN(B2:B8,E2:E8) returns the middle salary across both teams. Empty cells and text are skipped, just like in AVERAGE.
What if there's an even number of values?#
With an even count there is no single middle number, so MEDIAN averages the two in the middle. Leave the founder out and use the six staff salaries:
=MEDIAN(B2:B7)Sorted, they are 48,000, 49,500, 51,000, 52,500, 53,000 and 55,000. The two middle values are 51,000 and 52,500, so the result is 51,750.
How to use MODE in Excel#
MODE returns the number that appears most often in a range. It's useful for questions like "what is our most common order size?" or "which score did most people get?".
Here are ten orders and how many items each one had:
| Order | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|---|---|
| Items | 1 | 1 | 1 | 2 | 3 | 4 | 5 | 6 | 2 | 1 |
If the item counts are in D2:D11, =MODE(D2:D11) returns 1, because four orders had one item. The average here is 2.6 and the median is 2, but neither is an order size that happens most often.
Three things to know about MODE:
- No repeats gives #N/A. In the salary table no salary appears twice, so
=MODE(B2:B8)returns #N/A. - Ties return the first one. If 3 and 5 both appear twice, MODE returns whichever repeated value comes first in the range.
- MODE only works on numbers. It ignores text, so it can't tell you the most common city or product name. Use COUNTIF for that, as shown in how to use COUNTIF.
MODE, MODE.SNGL and MODE.MULT#
MODE.SNGL is the newer name for MODE, added in Excel 2010. Both give the same answer. MODE.MULT returns every value that ties for most common, as a list, which is useful when there are several modes.
When to use MEDIAN, MODE or AVERAGE#
Pick the measure that matches the question you're asked.
| Use | When | Example |
|---|---|---|
| AVERAGE | Values are fairly even, with no extreme outliers | Average daily visitors over a normal month |
| MEDIAN | A few values are much higher or lower than the rest | Typical salary, house price, time to hire |
| MODE | You want the most frequent value | Most common order size or shoe size |
A good habit is to calculate AVERAGE and MEDIAN side by side. If they are close, the data is fairly even. If they are far apart, something is pulling the average, and you should check for outliers. To see exactly which values are the extremes, LARGE and SMALL find the top or bottom N values. The AVERAGE function reference covers the mean in more detail.
Common mistakes with MEDIAN and MODE#
- Leaving out a row.
=MEDIAN(B3:B8)misses the first person and gives a different middle value. Check that the range covers every row. - Zeros that mean "no data". MEDIAN and MODE count a 0 like any other number, but skip empty cells and text. Leave a cell empty if the value is unknown.
- Numbers stored as text are skipped. Count the real numbers with COUNT to make sure none are missing, as described in COUNT, COUNTA and COUNTBLANK.
- Expecting MODE to work on words. It only counts numbers.
Practise it#
Practise MEDIAN and MODE in a live spreadsheet: you'll compare the average and median of the salary table above, find the median of an even count, and use MODE on order sizes. Then try the time-to-hire exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
HR wants the typical time to hire. In D2, type =MEDIAN(B2:B8) and press Enter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Days to hire | Median days | |||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 | |||||
| 11 |
Tip: MEDIAN takes a range just like AVERAGE: include all seven roles, B2 to B8.
Practise this in a lesson
Frequently asked questions
How do I find the median in Excel?
Type =MEDIAN( then the range, then ), for example =MEDIAN(B2:B8). You don't need to sort the data first; Excel does that internally.
What does MEDIAN do with an even number of values?
There is no single middle value, so MEDIAN returns the average of the two middle numbers. For 2, 4, 6 and 8 the median is 5.
Why does MODE return #N/A?
No number in the range appears more than once, so there is no most common value. MODE also ignores text, so it can't find the most common word.
What is the difference between MODE and MODE.SNGL?
They return the same result. MODE.SNGL was added in Excel 2010 as the newer name, and MODE is kept so older files still work.
Should I use the median or the average for salaries?
Usually the median. A few very high salaries pull the average up, while the median shows what the person in the middle earns.