Excel Median Formula: How to Calculate the Middle Value

Formulas & functions · Updated November 22, 2026

Use Excel’s MEDIAN function to find the middle number in a set: if your values are in A2:A10, enter =MEDIAN(A2:A10). Excel sorts the values mathematically for the calculation, so you do not need to rearrange the cells first.

Enter the MEDIAN formula

In Microsoft Excel for Microsoft 365 on Windows, select the cell where you want the answer, type the formula, and press Enter. Replace A2:A10 with the range that contains your numbers. The formula can use a single column, a row, or a range of cells; it can also combine multiple ranges.

With the numbers 3, 8, 2, 10, and 5 in A2:A6, enter =MEDIAN(A2:A6). The result is 5: when the values are ordered as 2, 3, 5, 8, 10, the value in the center is 5.

To calculate a median from separate blocks, use commas between the references. With 1, 6, and 11 in A2:A4 and 3, 8, and 14 in C2:C4, enter =MEDIAN(A2:A4,C2:C4). The combined ordered list is 1, 3, 6, 8, 11, 14, so the result is 7, the average of the two central values, 6 and 8.

Excel for the web and current Excel for Mac also support MEDIAN. The formula syntax is the same; there is no special median command to choose from a ribbon menu.

How Excel finds the median

The median is the middle value after a set of numbers is arranged from smallest to largest. It divides the observations into two halves: about half are at or below the median, and about half are at or above it. This is a measure of the center of a data set, but it is not necessarily one of the original values.

For an odd number of observations, there is one value in the center. For an even number, there are two central values, and Excel averages them. With 4, 9, 12, and 20 in A2:A5, enter =MEDIAN(A2:A5). The result is 10.5, because the two central values are 9 and 12 and their average is (9 + 12) ÷ 2.

This averaging rule explains why a median can include a decimal even when every input is a whole number. It also means that the answer for an even-sized list may not appear anywhere in the source data.

Check the result with worked examples

A quick way to understand a result is to make a small example and order its values manually. For instance, a set of response times of 12, 15, 15, 18, and 42 has a median of 15. The high value 42 does not pull the median upward as much as it would affect an arithmetic average.

For a larger range, Excel does the ordering step internally. With 10, 12, 13, 15, and 100 in A2:A6, enter =MEDIAN(A2:A6). The result is 13. In this example the average is 30, because the unusually large 100 raises the mean, while the median remains the middle observation.

Use the same range in your formula that represents the group you actually want to summarize. If a table has a heading in row 1 and observations in rows 2 through 20, a range such as A2:A20 avoids including the heading. If the range includes more rows for future entries, remember that any numbers already present in those rows will be included too.

Choose the right cells and data

Before relying on a median, check what each cell represents and whether it should count as an observation. A range can contain numbers, blanks, labels, dates, or error values, and those contents can affect the outcome in different ways.

  • Use a numeric range. Include the cells that contain the observations, not labels or totals that would distort the set.
  • Check for accidental duplicates. MEDIAN counts every numeric cell, including repeated values. If a repeated row is a duplicate record rather than a real repeated observation, correct the data first.
  • Decide how to treat zero. A zero is a number, so Excel includes it. If zero means “not recorded” in your data, decide whether it should be excluded before calculating.
  • Confirm units and formats. A mix of dollars and cents, or minutes and seconds, can produce a mathematical result that is not meaningful.

Dates are stored by Excel as serial numbers, so a median of valid date cells is calculated numerically and can display as a date if the result cell has a date format. If it displays as a number, select the result cell and choose a suitable date format from Home > Number Format. Formatting changes how the result appears, not the calculation itself.

Median compared with average

The median and average (mean) answer different questions. The mean adds all the values and divides by their count. The median identifies the center after ordering. A mean is sensitive to unusually high or low values; a median is generally less affected by a small number of extreme observations.

That difference can be useful when summarizing household incomes, sale prices, waiting times, or other unevenly distributed data. For example, the median can describe a typical value more clearly when a few observations are far above most of the rest. But it does not show the full spread of the data, and it can hide meaningful differences between groups. For a fuller picture, consider reporting the number of observations and a measure of spread alongside the median.

Neither measure is universally better. If the data are fairly balanced and every value matters to the question, the mean may be appropriate. If extremes would make the mean misleading for the purpose of the summary, the median may be a better description of the center. Choose based on the data and the question, not simply because one number looks more convenient.

Blanks, text, zeros, and errors

When you give MEDIAN a cell reference, it ignores blank cells and text entries in the referenced range. A number stored as text is not treated as a numeric observation in that range, so a cell that looks like 12 may need converting to a real number before it is included. The function does include numeric zero. A blank cell and a cell containing zero are therefore not interchangeable.

Errors in the referenced data can cause the median formula to return an error rather than a numeric result. Check the source range for error values if the formula displays an error. Also check whether the range contains any numbers at all: if it does not, MEDIAN cannot calculate a numeric middle value.

To omit zeros while retaining other numeric values, use FILTER with MEDIAN in Microsoft 365 or Excel 2021 and later. With 0, 4, 7, 9, and 10 in A2:A6, enter =MEDIAN(FILTER(A2:A6,A2:A6<>0)). The result is 8, the median of 4, 7, 9, and 10. If the filter finds no values, FILTER returns an error unless you provide an optional value for its empty result; handle that case if your data may contain only zeros.

Do not remove zeros automatically just because they seem unusual. A zero may be a valid observation, such as a zero balance or a recorded duration of zero. Excluding it changes the population being summarized and should reflect what the data means.

Common formula problems

If Excel does not accept a formula, first verify that it begins with an equals sign, that the range references are valid, and that the parentheses are balanced. In some regional configurations Excel uses semicolons instead of commas between function arguments. The examples here use standard English-language Excel syntax with commas, as requested; the separator in an installed copy may depend on its regional settings.

If the result seems wrong, inspect the selected range and the values Excel recognizes as numbers. A heading accidentally included in a reference is usually ignored as text, but including a numeric total or a value from another group can change the median. If a formula returns an error, check for error cells and confirm that at least one numeric value remains after any filtering.

Finally, distinguish a display issue from a calculation issue. A result such as 10.5 may appear as 11 if the cell is formatted to show zero decimal places; the stored result can still be 10.5. Increase the displayed decimal places using Home > Increase Decimal, or change the number format, to inspect the value more closely.

Frequently Asked Questions

What is the exact formula for median in Excel?
Use =MEDIAN(range), replacing range with the cells you want to include. For values in A2:A10, the formula is =MEDIAN(A2:A10).

Does Excel need the data to be sorted first?
No. MEDIAN works out the central value without changing the order of your worksheet data. Sorting is useful if you want to inspect the ordered observations, but it is not required for the formula.

How does Excel calculate the median when there are two middle numbers?
It takes the arithmetic average of those two numbers. For example, the center of 4, 9, 12, and 20 is (9 + 12) ÷ 2, so the median is 10.5.

Does MEDIAN count zero and blank cells?
It counts zero because zero is numeric, but ignores blank cells in a referenced range. If zero means missing data in your particular worksheet, exclude it deliberately rather than assuming Excel will treat it as blank.

Can I calculate a median for numbers that are not next to each other?
Yes. Give MEDIAN multiple references separated by commas, such as =MEDIAN(A2:A10,C2:C10). Excel calculates the median across the numeric values in both ranges.

Why does the median formula show an error or an unexpected value?
Check for error values in the source range, numbers stored as text, unintended zeros, and cells included by mistake. Also make sure the result cell’s number format is not hiding decimals or displaying a date or currency format that makes the answer look unfamiliar.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.