To calculate a median in Excel, use the MEDIAN function. If your numbers are in B2:B10, enter =MEDIAN(B2:B10); Excel returns the middle value after the numbers are ordered, or the average of the two middle values when there is an even number of them.
What the median tells you
The median is a measure of the center of a group of numbers. Half the numeric values are at or below it, and half are at or above it. Unlike the arithmetic mean, which adds every value and divides by the count, the median depends on the values in the middle of the ordered set.
That difference is useful when a data set contains unusually large or small values. For example, a few very high home prices can pull the mean upward, while the median price may better describe a typical sale. The median is not always the best summary: if the size of every value matters and the data has no extreme skew, the mean may be useful too. The right measure depends on what you want to communicate.
Excel’s MEDIAN function does the ordering calculation for you. You do not need to sort the worksheet first, and sorting is not required to make the result correct.
Use MEDIAN with a range
For numbers stored in a continuous range, the simplest method is to select an empty cell, type the function, and press Enter. With the example numbers in B2:B10, the formula is =MEDIAN(B2:B10). Excel 365 for Windows calculates the result and displays it in the selected cell.
For a concrete example, suppose B2:B10 contains 12, 7, 19, 4, 11, 15, 8, 20, and 9. The sorted sequence is 4, 7, 8, 9, 11, 12, 15, 19, 20. There are nine values, so the fifth value is the middle one: the formula returns 11. The order of the values in the worksheet does not affect the answer.
The range reference can also be made larger or smaller to match your data. You can select the cells with the mouse while entering the formula, or type the reference directly. If you add more observations later, update the range so the new values are included. For a table that grows over time, a structured table reference can be more convenient than repeatedly changing a fixed range.
How MEDIAN handles odd and even counts
With an odd number of numeric values, the median is the single value in the center of the sorted list. With an even number, there is no single central observation, so Excel averages the two values in the middle. The answer may therefore be a number that does not appear anywhere in the source data.
For example, with six values in B2:B7—4, 7, 8, 12, 15, and 20—the two middle values are 8 and 12. The median is (8 + 12) / 2, or 10. With those six values in B2:B7, enter =MEDIAN(B2:B7); Excel returns 10. This is correct even though 10 is not one of the six observations.
When explaining the result, distinguish the median from the middle row in the original worksheet. Excel determines the middle by numerical order, not by row position. If the original values are unsorted, the row that happens to sit halfway down the range has no special role.
Blanks, zeros, text, and errors
MEDIAN treats blank cells in a referenced range differently from zeros. Blank cells are ignored, but zero is a numeric value and is included. This matters when a spreadsheet uses zero to mean “no measurement”: Excel will include those zeros in the calculation unless you exclude them deliberately. Check what a zero means in your data before deciding whether it should count.
Text and logical values in referenced cells are ignored by MEDIAN, while numeric entries are used. A value that looks like a number but is stored as text may therefore be skipped. If a result seems too high or low, check whether some entries are left-aligned or show a warning indicator, and convert text-stored numbers to actual numbers as appropriate. Also check for error values such as #N/A or #VALUE! in the referenced range; an error in the data can cause the formula to return an error rather than a median.
Dates and times are stored by Excel as numbers, so MEDIAN can calculate their midpoint too. If the range contains dates, format the result cell as a date so Excel displays a readable date rather than a serial number. For times, apply a time format. A median date may fall between two recorded dates; it represents the midpoint of the underlying date values, not necessarily an observed event.
Calculate a median for matching rows
Sometimes you need the median for one category rather than for every row—for example, the median sales amount for one region. In Microsoft 365 or Excel 2021, FILTER can return only the matching values and MEDIAN can calculate their center. Suppose A2:A7 contains region names, and B2:B7 contains the corresponding sales values. To find the median for North, use =MEDIAN(FILTER(B2:B7,A2:A7="North")). If the North sales are 10, 14, 18, and 22, the result is 16.
FILTER is available in Microsoft 365 and Excel 2021; it is not available in every older Excel version. If no rows match the condition, FILTER returns an error instead of a median. You can choose to show a message for that case. With the same region names in A2:A7 and sales in B2:B7, use =IFERROR(MEDIAN(FILTER(B2:B7,A2:A7="North")),"No matching rows"). This displays the text message when the filter finds no matching records. Confirm that the criteria text matches the values in the category column.
For multiple criteria, FILTER can combine tests, but the logic should be checked carefully. For example, multiplying two true/false tests applies an AND condition: both conditions must match. If you need a formula for an older Excel version, the available approach depends on that version and whether array formulas are supported; avoid copying a modern FILTER formula into an older workbook without checking compatibility.
Compare median with average and other summaries
Excel’s AVERAGE function calculates the arithmetic mean, while MEDIAN calculates the center by rank. Consider the values 10, 12, 13, 15, and 100. The median is 13, but the average is 30. The single value 100 pulls the mean upward, while it has less effect on the median because it remains at one end of the ordered list.
Neither measure is inherently more accurate. The median can describe a typical value in skewed data, but it ignores how far values lie from the center. The mean uses every value’s magnitude and can be helpful for totals or balanced distributions, but extreme observations can change it substantially. When the shape of the data matters, consider reporting both measures or showing the underlying distribution as well.
MEDIAN is also not a weighted median. If each observation represents a different number of people, units, or importance, the ordinary function gives every numeric cell equal weight. A weighted median requires a method that accounts for weights, so do not assume MEDIAN automatically incorporates a separate weight column.
Common problems and checks
- The result is unexpectedly high or low: Verify the range, confirm that all intended observations are included, and check whether zeros or text-stored numbers are changing the set Excel counts.
- The result is an error: Look for error values in the referenced cells. For a filtered calculation, check whether the criteria match any rows and whether the Excel version supports FILTER.
- The result displays as a date or a large serial number: The result cell may have an inappropriate number format. Choose a suitable number, date, or time format from the Home tab’s Number group.
- You sorted the data but the answer changed: Sorting alone should not change the median if the formula still refers to the same complete set of values. Check whether sorting separated related columns, changed the selected range, or excluded rows.
- You expected a value from the source list: With an even count, Excel averages the two middle values, so the median can be a value not present in the data.
In Excel for Mac, the MEDIAN formula and its basic behavior are the same. Excel for the web also supports the function, although the interface and available features can differ slightly from the Windows desktop app. If a workbook is shared with people using older Excel releases, check that any newer functions used alongside MEDIAN are supported by their version.
Frequently Asked Questions
Do I need to sort the numbers before using MEDIAN?
No. MEDIAN evaluates the numeric values and finds the center without changing their order on the worksheet. Sorting can help you inspect the data, but it is not required for the calculation.
Does MEDIAN count blank cells as zero?
No. Blank cells in a referenced range are ignored. A cell that contains the numeric value 0 is counted, so make sure the difference between a blank and a zero matches the meaning of your data.
Can Excel calculate the median of two numbers?
Yes. With two numeric values, both are central, so the median is their arithmetic average. The result might not equal either input value.
Can MEDIAN return a date?
Yes. Excel stores dates as numeric serial values, so MEDIAN can find the midpoint of a date range. Format the result cell as a date to display it in date form.
Why does MEDIAN ignore a number that looks valid?
The entry may be stored as text rather than as a number, or it may sit outside the formula’s range. Check the cell’s contents and the range reference, then convert the entry to a numeric value if appropriate.
Choosing the right approach
For a straightforward list of numbers, MEDIAN followed by a cell range is usually all you need. Use a filtered calculation when the question is about a particular category, and check how blanks, zeros, text, and errors should be treated before interpreting the result. When presenting a summary, explain whether you chose the median because the data is skewed, and consider showing the mean too if comparing both gives readers useful context.
Related
- Audit Links in Excel: Find and Check Workbook Links
- Audit Checklist Excel: Build and Use a Reliable Template
- Audit an Excel Sheet: A Practical Review Checklist
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Filter Excel for Empty Cells: Blanks and FormulasTo filter for empty cells in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel SUMIF Between Dates: Formula and ExamplesTo sum values between two dates in Excel, use SUMIFS with a start-date criterion and an end-date criterion.…
- COUNTIF in Excel: Count Yes or NoYes. Microsoft Excel has a COUNTIF function, and you can use it to count cells that contain “Yes,”…
- Excel Date Plus Weeks: Formulas, Dates, and ExamplesTo add weeks to a date in Excel, multiply the number of weeks by 7 and add the…
- Excel Return Index of Match? Use MATCH or XMATCHTo return the index (position) of a match in Excel, use the MATCH function for most cases, or…
- Using IF in Excel for Text: Compare, Match, and Return ResultsYes. In Excel, IF can test whether a cell contains particular text and return one result when it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.