How to Calculate Median in Excel - Practical Guide

Learn to calculate the median—the middle value in a dataset—using Excel's MEDIAN function. This tutorial covers basic syntax, handling various data ranges, and practical applications for statistical analysis. The median is essential for understanding data distribution, especially when outliers skew averages.

Why This Matters

The median provides a robust measure of central tendency unaffected by extreme values, making it invaluable for financial analysis, salary reviews, and quality control.

Prerequisites

•Basic Excel navigation and cell selection
•Understanding of dataset organization (rows/columns)
•Familiarity with formula entry

Step-by-Step Instructions

Select your target cell

Click the cell where you want the median result to appear (e.g., cell D5).

Enter the MEDIAN formula

Type =MEDIAN( into the cell, then select your data range or type it directly (e.g., =MEDIAN(A1:A10)).

Include your data range

Reference the cells containing your numerical values; Excel automatically ignores text and empty cells within the range.

Close the formula

Type the closing parenthesis ) to complete the formula structure.

Press Enter to execute

Hit Enter to calculate and display the median value in your selected cell.

Alternative Methods

Using multiple non-contiguous ranges

Separate multiple ranges with semicolons: =MEDIAN(A1:A10;C1:C10) to include non-adjacent data.

Combining with other functions

Nest MEDIAN within IF arrays or use with IFERROR to handle conditional medians or missing data gracefully.

Tips & Tricks

✓The MEDIAN function automatically ignores text, logical values, and empty cells in your range.
✓For odd-length datasets, MEDIAN returns the exact middle value; for even-length datasets, it returns the average of the two middle values.
✓Use absolute references ($A$1:$A$10) when copying formulas to prevent range shifts.

Pro Tips

★Combine MEDIAN with SMALL or LARGE functions to identify outliers beyond statistical thresholds.
★Use conditional median formulas (array formulas with IF) to calculate medians for filtered subsets without helper columns.
★In Excel 365, leverage dynamic arrays to auto-expand median results across multiple criteria.

Troubleshooting

#VALUE! error appears

Check that your range contains only numbers or Excel-compatible data types. Remove any text strings or special characters from your data range.

Formula returns unexpected result

Verify your range is correct and includes all intended data. Check for hidden rows or filtered data that may exclude values from the calculation.

MEDIAN shows 0 or blank

Ensure your range references are correct and contain numerical values, not text-formatted numbers or empty cells.

Related Excel Formulas

AVERAGE MODE QUARTILE PERCENTILE SMALL LARGE

Frequently Asked Questions

What's the difference between MEDIAN and AVERAGE?

AVERAGE calculates the sum divided by count, while MEDIAN finds the middle value. MEDIAN is more resistant to outliers—for example, in {1, 2, 100}, the average is 34.3 but the median is 2.

Can I use MEDIAN with criteria or conditions?

MEDIAN alone doesn't filter by criteria, but you can use array formulas like =MEDIAN(IF(criteria_range=criteria, value_range)) in older Excel versions, or use FILTER with MEDIAN in Excel 365.

Does MEDIAN work with negative numbers?

Yes, MEDIAN handles negative numbers correctly and includes them in the calculation just like positive numbers.

What happens if I have an even number of values?

Excel returns the average of the two middle values; for {1, 2, 3, 4}, the median is 2.5 (average of 2 and 3).