Median Formula Examples in Excel and by Hand

The median is the middle value after a set of numbers is arranged from smallest to largest; with an odd number of values, it is the one in the center, and with an even number, it is the average of the two center values. In Excel for Microsoft 365, if your numbers are in cells A2:A10, enter =MEDIAN(A2:A10) to calculate the median.

What the median measures

The median is one of the common measures of central tendency, alongside the mean and the mode. It describes the midpoint of an ordered data set: half the values are at or below it, and half are at or above it. The values do not need to be sorted in the worksheet for Excel to find the median.

For example, consider the values 2, 4, 7, 9, and 12. The median is 7 because it is the third value when the five numbers are ordered. The count on each side is equal: two values are below 7 and two are above it. This makes the median useful when you want a representative middle point rather than a total or a value influenced strongly by unusually large or small observations.

A median is not necessarily one of the original values. When there are an even number of observations, the median is halfway between the two middle values. It may therefore be a decimal, even if every entry in the data is a whole number.

Median formula for an odd number of values

For an odd-sized list, sort the values and take the single value in the center. A quick way to locate it is to count inward from both ends until the same number of observations remains on either side. For seven observations, the fourth value is the middle one.

Suppose the values are 11, 3, 8, 5, and 14. In sorted order, they are 3, 5, 8, 11, and 14, so the median is 8. The order in which the values were originally entered does not affect the answer. The same result applies if the unsorted values are in a spreadsheet.

If the five values are in A2:A6, enter =MEDIAN(A2:A6). Excel returns 8. The MEDIAN function handles the ordering as part of its calculation, so you do not need to sort the cells first or identify the center cell yourself.

Median formula for an even number of values

For an even-sized list, there is no single central observation. First order the values, identify the two values in the middle, add them, and divide the total by two. In a list of six values, the third and fourth values are the two central observations.

For example, take 3, 8, 10, and 14. The middle pair is 8 and 10. Their average is 9, so the median is 9. It falls between the two central observations and is not itself present in the original list. This is a normal result, not a sign that the calculation is wrong.

If those four values are in A2:A5, enter =MEDIAN(A2:A5). Excel returns 9. The function automatically detects that the range has an even number of numeric entries and averages the central pair. This is usually safer and quicker than writing a separate calculation for the middle cells.

Using MEDIAN in Excel

In Excel for Microsoft 365 on Windows, type the function into an empty cell, select the data range, and press Enter. You can also begin typing the function, choose MEDIAN from Excel’s function suggestions, and select the range with the mouse. The function name is the same in Excel for Mac and Excel for the web; the examples here do not rely on a Windows-only feature.

If your values are in B2:B8, enter =MEDIAN(B2:B8). Excel calculates the median from the numeric values in that range. A range reference includes every cell between its endpoints, so check that the selected range does not accidentally include a total, a heading, or unrelated numbers.

You can also supply more than one range as arguments. If one group of values is in A2:A4 and another is in C2:C4, enter =MEDIAN(A2:A4,C2:C4). Excel considers the numeric values from both ranges together when it finds the middle. The comma separates the arguments in standard Microsoft 365 settings; some regional settings use a different list separator, which Excel applies according to the computer’s regional configuration.

To make the result easier to read, format the result cell as a number and choose an appropriate number of decimal places. Formatting changes how the value appears, not the underlying median. For instance, a result of 9.5 can be displayed with one decimal place or rounded visually to a whole number, but the stored result remains 9.5 unless you use a separate rounding calculation.

Median compared with mean and mode

The mean is the sum of the values divided by the number of values. The median is the center of the ordered list, while the mode is the most frequently occurring value. These measures answer different questions, so the most suitable one depends on the data and what you want to describe.

Consider 4, 5, 6, 7, and 100. The median is 6, while the mean is 24.4. The unusually high value of 100 pulls the mean upward, but it does not move the central observation. This is why medians are often reported for skewed data, such as income or property prices, where a small number of very high values can make the mean seem unlike the experience of a typical observation.

The median is not always the best summary. If every value contributes meaningfully to a total and extreme values are genuine, the mean may better reflect the data. If repeated values are important, the mode may be useful. A careful report can include more than one measure, along with the range or a description of the sample, rather than treating one statistic as universally correct.

Median for a category or selected subset

Sometimes a data table contains values for several groups, and the question is about the median for just one group. In Microsoft 365, FILTER can select the matching values before MEDIAN calculates the result. This approach is useful for a table that grows over time, provided the formula’s ranges cover the rows you need.

Suppose category codes are in A2:A11, where 1 identifies the group of interest, and the corresponding measurements are in B2:B11. Enter =MEDIAN(FILTER(B2:B11,A2:A11=1)). Excel filters the measurements to rows with code 1 and returns their median. FILTER is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

If no rows match the selected category, FILTER returns a no-results error, so the median cannot be calculated from that selection. Check that the category code is correct and that the formula ranges include the intended records. For larger tables, an Excel Table can make the data easier to extend and maintain, although the basic MEDIAN and FILTER logic remains the same.

Blanks, zeros, text, and common errors

MEDIAN treats different cell contents differently, which matters when a worksheet has incomplete or mixed data. In a cell range, blank cells and text entries are ignored, while numeric zero is included as a value. A zero can shift the median, so confirm whether it means a genuine measurement or is being used as a placeholder for missing information.

For example, if a range contains 2, 5, 9, and a blank cell, Excel finds the median from the three numbers, giving 5. If the blank is changed to 0, the numeric set becomes 0, 2, 5, and 9; its median is 3.5, the average of the two middle values. That change can make a substantial difference, even though only one cell was edited.

Text labels such as “pending” are not numeric measurements and are ignored when they occur in referenced cells. A cell that looks like a number but is stored as text may also be excluded from the range calculation. If the result seems unexpected, check the cell contents and number formats, and look for error values in the input range. An error in a referenced cell can cause the formula to return an error rather than a median.

Confirm that the formula covers all and only the intended observations.
Decide whether zeros are valid measurements or placeholders before calculating.
Check for numbers stored as text, hidden rows, and error values.
Remember that sorting the worksheet is not required for MEDIAN to work.

Choosing the right median method

For a short list, calculating by hand is a good way to understand and verify the result: sort the numbers, locate the middle position or pair, and average the pair if the count is even. For a worksheet range, MEDIAN is usually the clearest choice because it reduces the chance of selecting the wrong center values and updates when the referenced numbers change.

For a subset of records, decide first how membership in that subset is defined. A FILTER-based formula works well when the criterion is recorded in a neighboring column and your Excel version supports FILTER. If you are using an older Excel version, you may need a different approach, such as copying the matching observations to a separate range and applying MEDIAN to that range. Avoid silently excluding observations just to obtain a preferred result; document the rule used to include or omit data.

Also distinguish a median of observations from a weighted median. MEDIAN gives each numeric entry equal weight. If each observation represents a different number of people, units, or cases, the ordinary median does not account for those weights. A weighted median requires a method that uses both the measured values and their associated weights, so the standard MEDIAN function alone is not enough.

Frequently Asked Questions

What is the median formula in Excel?
If the numbers are in A2:A10, enter =MEDIAN(A2:A10). Replace that range with the cells containing your values. MEDIAN works with unsorted numeric entries and returns the center value or the average of the two center values.

Do I have to sort numbers before using MEDIAN?
No. Sorting is useful when calculating by hand or checking the result, but Excel’s MEDIAN function finds the middle without changing the worksheet’s order.

Can the median be a decimal?
Yes. When there is an even number of numeric observations, the two central values are averaged. If their average is not a whole number, the median is a decimal even when all input values are whole numbers.

Does Excel include zero when it calculates a median?
Yes. A numeric zero in a referenced range is included. Blank cells and text entries in a range are ignored, so replacing a blank with zero can change the result.

Why is my Excel median different from the average?
The median is the central value in sorted order; the average, or mean, uses every value in the sum. An unusually high or low observation can move the mean substantially while having less effect on the median.

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.