How to Calculate the Median in Excel

Key Insights

• The MEDIAN function ignores text and empty cells automatically, making it robust for real-world datasets with missing values
• For conditional median calculations, Excel 365’s FILTER function eliminates the need for legacy array formulas and Ctrl+Shift+Enter
• Median outperforms mean for skewed distributions—always calculate both when analyzing salary, housing, or income data to detect outliers

Introduction to Median

The median is the middle value in a sorted dataset. If you have an odd number of values, it’s the center value. If you have an even number, it’s the average of the two center values. Simple concept, powerful application.

Why does this matter? Because the mean lies to you when outliers exist.

Consider a team of five employees with salaries of $50,000, $52,000, $55,000, $58,000, and $250,000. The mean salary is $93,000—a number that describes nobody on the team. The median is $55,000, which actually represents the typical employee’s compensation.

This resistance to outliers makes median essential for analyzing:

• Income and salary data where executives skew averages upward
• Housing prices where luxury properties distort market perception
• Response times where occasional timeouts inflate means
• Customer spending where whale customers dominate averages

Excel provides straightforward tools for median calculation, from basic ranges to conditional filtering. Let’s work through each approach.

Using the MEDIAN Function (Basic)

The MEDIAN function accepts up to 255 arguments and returns the middle value of all numeric entries.

Syntax:

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

For a contiguous range of cells, the implementation is straightforward:

=MEDIAN(A1:A10)

Here’s a practical example. Suppose column A contains daily website response times in milliseconds:

=MEDIAN(A1:A10)

This returns 130, the average of the 5th and 6th values when sorted (128 and 131). Notice how the outlier of 890ms—likely a timeout or server hiccup—doesn’t corrupt the result. The mean of this dataset is 207ms, which misrepresents typical performance by 60%.

MEDIAN with Multiple Ranges and Mixed References

Real datasets aren’t always in neat, contiguous columns. MEDIAN handles multiple ranges and individual values as separate arguments.

=MEDIAN(A1:A10, C1:C10, 50)

This formula calculates the median across:

• All values in A1:A10
• All values in C1:C10
• The literal value 50

You can also use named ranges for clarity. If you’ve defined “Q1_Sales” and “Q2_Sales” as named ranges:

=MEDIAN(Q1_Sales, Q2_Sales)

This approach improves formula readability and reduces errors when ranges change. To create a named range, select your data, then use the Name Box (left of the formula bar) or go to Formulas → Define Name.

For cross-sheet calculations:

=MEDIAN(Sheet1!A1:A50, Sheet2!A1:A50, Sheet3!A1:A50)

This consolidates data from multiple worksheets into a single median calculation—useful for combining regional data or time-period comparisons.

Handling Edge Cases

MEDIAN is forgiving with messy data, but understanding its behavior prevents surprises.

Empty cells: Ignored completely. A range of 10 cells with 3 empty cells calculates the median of the 7 remaining values.

Text values: Ignored. If A5 contains “N/A” as text, MEDIAN skips it.

Zero values: Included. Zero is a valid number and participates in the calculation.

Boolean values: TRUE (1) and FALSE (0) are included when typed directly into the formula but ignored when they’re cell references.

Error values: Any error (#N/A, #VALUE!, #DIV/0!) causes the entire MEDIAN function to return that error.

To handle potential errors gracefully:

=IFERROR(MEDIAN(A1:A10), "No valid data")

This returns a friendly message instead of an error when the range contains only errors or no numeric data.

For more control, you can nest MEDIAN with other error-handling functions:

=IF(COUNT(A1:A10)=0, "Empty dataset", MEDIAN(A1:A10))

This explicitly checks whether any numeric values exist before attempting the calculation.

When dealing with imported data that might contain error values mixed with valid numbers, consider using AGGREGATE function instead:

=AGGREGATE(12, 6, A1:A10)

The 12 specifies MEDIAN, and the 6 tells Excel to ignore error values. This calculates the median of all valid numbers while skipping any cells containing errors.

Conditional Median with MEDIAN + IF (Array Formula)

What if you need the median of only certain values? For example, the median salary for the Sales department only?

Before Excel 365, this required an array formula combining MEDIAN with IF:

=MEDIAN(IF(B1:B10="Sales", A1:A10))

Critical: In Excel 2019 and earlier, you must press Ctrl+Shift+Enter instead of just Enter. Excel wraps the formula in curly braces {} to indicate it’s an array formula:

{=MEDIAN(IF(B1:B10="Sales", A1:A10))}

Here’s how it works with sample data:

The formula =MEDIAN(IF(B1:B10="Sales", A1:A10)) returns 53,500—the median of only the Sales salaries (45000, 48000, 52000, 55000, 59000).

For multiple criteria, nest additional IF statements or use multiplication for AND logic:

=MEDIAN(IF((B1:B10="Sales")*(C1:C10="West"), A1:A10))

This calculates the median for Sales department employees in the West region only.

Modern Approach: FILTER Function for Conditional Median

Excel 365 and Excel 2021 introduced the FILTER function, which dramatically simplifies conditional calculations. No array formula syntax required.

=MEDIAN(FILTER(A1:A10, B1:B10="Sales"))

This reads naturally: “Calculate the median of A1:A10, filtered to rows where B1:B10 equals Sales.”

The FILTER function returns an array of matching values, which MEDIAN then processes. Same result, cleaner syntax, and it spills naturally if you need to see the filtered values.

For multiple conditions, use the multiplication operator for AND logic:

=MEDIAN(FILTER(A1:A10, (B1:B10="Sales")*(C1:C10>=50000)))

This returns the median salary for Sales employees earning $50,000 or more.

For OR logic, use addition:

=MEDIAN(FILTER(A1:A10, (B1:B10="Sales")+(B1:B10="Marketing")))

Handle cases where no matches exist with FILTER’s third argument:

=MEDIAN(FILTER(A1:A10, B1:B10="Executive", ""))

If no Executive department exists, this returns an empty string instead of a #CALC! error. Wrap with IFERROR for complete protection:

=IFERROR(MEDIAN(FILTER(A1:A10, B1:B10="Sales")), "No matching data")

Practical Application: Comparing Mean vs. Median

Let’s demonstrate why calculating both measures matters with a realistic salary dataset:

Side-by-side comparison:

=AVERAGE(A1:A10)

Returns: $82,200

=MEDIAN(A1:A10)

Returns: $52,000

The executive salary of $340,000 inflates the mean by 58%. If you’re a job candidate researching “average salary” at this company, the mean suggests $82,200. The median tells the truth: most employees earn around $52,000.

Create a quick diagnostic by calculating both and their ratio:

=AVERAGE(A1:A10)/MEDIAN(A1:A10)

When this ratio exceeds 1.2, you have significant positive skew (high outliers). Below 0.8 indicates negative skew. A ratio near 1.0 suggests symmetric distribution where mean and median are interchangeable.

For comprehensive analysis, build a summary block:

Mean:           =AVERAGE(A1:A10)
Median:         =MEDIAN(A1:A10)
Skew Ratio:     =AVERAGE(A1:A10)/MEDIAN(A1:A10)
Count:          =COUNT(A1:A10)
Min:            =MIN(A1:A10)
Max:            =MAX(A1:A10)

This gives you immediate insight into distribution shape without building a histogram.

The median is your default choice for central tendency when outliers might exist—which is most real-world data. Use the mean only when you’re confident your data is normally distributed or when outliers represent legitimate, proportional influence (like calculating average revenue per customer where whales should pull the number up).