Written and maintained by CASRAI Editorial Board
Last updated
To calculate the median in Excel, type =MEDIAN(A2:A20) into any empty cell, replacing the range with your own numbers, and press Enter. Excel returns the middle value of the sorted numbers, and when the count is even it returns the average of the two middle values. What most guides leave out is exactly which cells MEDIAN skips and which it counts, and how to get a median that survives error values or hidden rows; both are covered below, with the Microsoft documentation as the source for every rule.
What the median is used for in research
The median is the value that splits an ordered dataset into a lower and an upper half. Researchers report it for skewed measurements (reaction times, costs, lengths of stay, citation counts) because a few extreme values move the mean far more than they move the median. The median is normally reported with a measure of spread; see median and interquartile range for how to report the pair, and percentiles and quartiles for the quartile definitions behind the interquartile range. If you want a location estimate that sits between the mean and the median, see the trimmed mean formula. For how skew shows up in a plot, see left-skewed histograms.
Access: no add-in needed for the MEDIAN formula
The MEDIAN function is a built-in worksheet function. Microsoft lists it as available in Excel for Microsoft 365 (including Excel for Mac), Excel 2024, Excel 2021, Excel 2019 and Excel 2016 (and the Mac versions of 2024 and 2021). Nothing has to be installed. The only route in this guide that needs an add-in is the Descriptive Statistics report, covered in its own section below.
Step by step: the MEDIAN formula for a column of numbers
The syntax is MEDIAN(number1, [number2], ...). number1 is required; you can supply up to 255 numbers in total, and each argument can be a number, a name, an array or a reference that contains numbers.
- Enter your numbers in a column, for example in cells A2:A7.
- Click an empty cell where the result should appear.
- Type
=MEDIAN(, then select the cells with the mouse (or type the range, for instanceA2:A7), and close the bracket. - Press Enter. The cell shows the median.
This is the same formula whatever you search for: the formula for median in Excel, the Excel median formula, and the median formula in Excel all refer to =MEDIAN(range). To find the median across several separate ranges, list them as separate arguments, for example =MEDIAN(A2:A20, C2:C20).
Check the formula by hand
Microsoft’s own example uses the numbers 1 to 6 in A2:A7. With an odd count, =MEDIAN(A2:A6) (the numbers 1 to 5) returns 3, the single middle value. With an even count, =MEDIAN(A2:A7) (the numbers 1 to 6) returns 3.5, the midpoint of the third and fourth values. Running these two checks on a scratch sheet confirms you are reading the result correctly before you apply the formula to real data.
Output and how to read it
The result is a single number. With an odd number of values it is one of your data points; with an even number it is the average of the two middle values, so it may not appear anywhere in the data. Report it with the same unit and precision as the raw measurements, and pair it with a spread measure such as the interquartile range.
What MEDIAN ignores and what it counts
This is where median results most often go wrong without any error message. The rules below are from Microsoft’s documentation of the function.
| What is in the argument | What MEDIAN does |
|---|---|
| Numbers, names, arrays or references containing numbers | Counted |
| Logical values and number-like text typed directly into the argument list | Counted |
| Text, logical values or blank cells inside an array or cell reference | Ignored |
| Cells containing zero | Counted (zero is a value, unlike a blank cell) |
| Error values, or text that cannot be converted to a number | The formula returns an error |
Two consequences matter for research data. First, a blank cell is not a zero: if missing observations are left empty they drop out of the median, but if they were entered as 0 they pull it down. Second, numbers stored as text inside a range are ignored rather than converted, so a column imported as text can return a median of only the cells that are real numbers.
Median ignoring errors or hidden rows (AGGREGATE)
A single error value in the range makes MEDIAN return an error. The AGGREGATE function can compute a median that skips them. In the reference form, AGGREGATE(function_num, options, ref1, [ref2], ...), the median is function number 12, and the options argument controls what is skipped:
4: ignore nothing5: ignore hidden rows6: ignore error values7: ignore hidden rows and error values
For example, =AGGREGATE(12, 6, A2:A20) returns the median of A2:A20 while skipping any error values, and =AGGREGATE(12, 7, A2:A20) also skips rows you have hidden or filtered out by hiding. Use this when a column contains #DIV/0! results from an earlier calculation and you want the median of the valid values.
Median with Descriptive Statistics (Analysis ToolPak)
The Analysis ToolPak is an add-in. Per Microsoft, on Windows go to File > Options > Add-Ins, choose Excel Add-ins in the Manage box, click Go, tick Analysis ToolPak and click OK. On a Mac, go to Tools > Excel Add-ins, tick Analysis ToolPak and click OK. After that, the Data tab shows a Data Analysis command, and its Descriptive Statistics tool generates a report of univariate statistics for the data in the input range. If you only need the median, the MEDIAN formula is quicker and recalculates automatically when the data change.
Troubleshooting
The formula returns an error
Check the range for error values (for example from a division) or for text that cannot be converted to a number; either makes MEDIAN return an error. Use AGGREGATE(12, 6, range) to skip errors, or fix the source cells.
The median looks too low
Missing observations entered as 0 are counted. Leave them blank instead, or exclude them from the range.
The median ignores some of my numbers
Numbers stored as text inside a range are ignored. Convert them to numbers, then recalculate.
I need a median for a subset of rows
Point MEDIAN at a range that contains only those rows, for example by sorting or filtering the data into a separate block first, or use AGGREGATE with option 5 or 7 to leave out hidden rows.
The same task in other software
| Tool | How to get the median | Source |
|---|---|---|
| Google Sheets | MEDIAN(value1, [value2, ...]); averages the two center values for an even count and ignores text in the arguments |
Google Docs Editors Help, MEDIAN (https://support.google.com/docs/answer/3094025) |
| SPSS | See how to calculate the median in SPSS | CASRAI guide |
| Stata | See how to find the median in Stata | CASRAI guide |
FAQ
How do I find the median in Excel?
Type =MEDIAN(, select your numbers, close the bracket and press Enter. The result is the middle value, or the average of the two middle values when the count is even.
What is the formula for the median in Excel?
=MEDIAN(number1, [number2], ...). Up to 255 arguments are allowed, and each can be a number, a name, an array or a reference.
Does the Excel median formula count blank cells or zeros?
Blank cells inside a range are ignored; zeros are counted.
How do I find the median in Excel if my data contain errors?
Use =AGGREGATE(12, 6, range), which computes the median while ignoring error values.
Sources
- Microsoft Support, MEDIAN function (checked 2026-10-29).
- Microsoft Support, AGGREGATE function (checked 2026-10-29).
- Microsoft Support, Use the Analysis ToolPak to perform complex data analysis (checked 2026-10-29).
- Google Docs Editors Help, MEDIAN (checked 2026-10-29).
