Cellwise Try it free

Excel guide · 3 min read

How to find the median in Excel

Quick answerUse =MEDIAN(C2:C10). It returns the middle value of the numbers once they are sorted, or the average of the two middle values when there is an even count. Empty cells and text are ignored, but zeros are counted. For a median with a condition, use =MEDIAN(FILTER(C2:C10,B2:B10="Sales")) in Excel 365 or 2021.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1EmployeeTeamSalary
2J. OkaforSupport48500
3L. ChenEngineering96000
4M. RossiSupport51200
5S. PatelEngineering104500
6D. NovakSales62000
7A. HaddadSales0
8R. SilvaEngineering245000
9K. BrownSupportn/a
10T. ItoSales58750

Method 1: the MEDIAN function

You do not need to sort anything first. MEDIAN finds the middle by itself. You can give it a range, several ranges or single cells: =MEDIAN(C2:C6,C9:C10).

This salary list has 8 numbers (the "n/a" text is ignored), so the median is the average of the 4th and 5th values once sorted: 58,750 and 62,000.

=MEDIAN(C2:C10)

Result: 60375

60,375, halfway between the two middle salaries.

Median vs average: which to report

One engineering salary of 245,000 pulls the average far above what most people here earn. The median does not care how big the top value is, only that it is above the middle. That is why house prices and incomes are usually reported as medians.

Use the average when every unit adds up to a total you care about, such as cost per order for a budget. Use the median to describe a typical value when the data has outliers or a long tail.

=AVERAGE(C2:C10)

Result: 83243.75

83,243.75, well above the median of 60,375, because of the one high salary.

Leave out zeros

A. Haddad shows 0 because the salary was not entered yet. MEDIAN counts zeros as real values, which drags the result down. FILTER keeps only the values above zero before MEDIAN sees them. FILTER needs Excel 365 or Excel 2021.

In Excel 2019 and older, use =MEDIAN(IF(C2:C10>0,C2:C10)) and confirm it with Ctrl+Shift+Enter so it runs as an array formula. The best fix is leaving unknown values blank instead of typing 0.

=MEDIAN(FILTER(C2:C10,C2:C10>0))

Result: 62000

62,000, the median of the 7 real salaries.

Median for one group (no MEDIANIF needed)

Excel has AVERAGEIF but no MEDIANIF. Filter the values by the group column, then take the median. For two conditions, multiply them inside FILTER: =MEDIAN(FILTER(C2:C10,(B2:B10="Sales")*(C2:C10>0))) gives 60,375 for Sales without the zero.

To build a table of medians, list the teams in one column (=UNIQUE(B2:B10) in Excel 365) and point the formula at that cell instead of typing the team name.

=MEDIAN(FILTER(C2:C10,B2:B10="Engineering"))

Result: 104500

104,500 for Engineering, the middle of 96,000, 104,500 and 245,000.

Where Excel does not show a median

The status bar shows Average, Count, Sum, Min and Max for a selection, but has no median option. A regular PivotTable has no Median in Value Field Settings either. Add the data to the Data Model and write a DAX measure using MEDIAN, or put MEDIAN(FILTER(...)) formulas beside the pivot.

For a full summary in one go, Data > Data Analysis > Descriptive Statistics (after enabling the Analysis ToolPak add-in) lists the median with the mean, mode and standard deviation.

Common mistakes

Typing 0 for missing data

Zeros are counted. Leave unknown cells empty, or filter them out with FILTER(range,range>0).

Numbers stored as text are skipped

Imported values like "51200" are ignored, which changes the middle. Check with COUNT versus COUNTA and convert them.

Confusing median with mode

The median is the middle value. The most frequent value is the mode, which is MODE.SNGL in Excel.

FAQ

How does Excel calculate the median with an even number of values?

It averages the two middle values. For 0, 48,500, 51,200, 58,750, 62,000, 96,000, 104,500 and 245,000 that is (58,750 + 62,000) / 2 = 60,375.

Does MEDIAN ignore blank cells?

Yes. Empty cells and text in a range are ignored. Cells that contain 0 are counted.

Is there a MEDIANIF function in Excel?

No. Use =MEDIAN(FILTER(values,criteria_range=criteria)) in Excel 365 or 2021, or =MEDIAN(IF(...)) entered with Ctrl+Shift+Enter in older versions.

When should I use median instead of average?

When a few very large or small values would distort the average, such as salaries, house prices or delivery times. The median shows the typical value.

Functions used

Related guides

Updated