To get the middle value of a numeric list in Excel, use =MEDIAN(A2:A7) when the values are in A2:A7. For the example values 9, 3, 21, 15, 12, and 18, the result is 13.5: Excel sorts the six numbers conceptually and averages the two central values, 12 and 15. If you need an actual value from the list rather than the statistical median, sort the values and return one of the central positions instead.
What “middle value” means in Excel
The phrase “middle value extraction” can refer to two different tasks. The first is finding the median, a measure of central tendency. The second is extracting the observation occupying the central position after a list has been sorted. These answers match for an odd number of numeric observations, but can differ for an even number.
For example, after sorting 3, 9, 12, 15, 18, and 21, there are two central observations: 12 and 15. The median is their average, 13.5, which is not an item in the original list. If a report, lookup, or later calculation requires an actual listed value, you must decide whether to take the lower or upper of those two central observations.
That distinction matters in inventory lists, ranked scores, and other data where a result must correspond to a real record. A median is usually the right choice for summarizing numeric data; extracting a central observation is right when you specifically need a value or record from the sorted list.
Use MEDIAN for the statistical middle
MEDIAN is Excel’s standard function for finding the center of numeric data. With values in A2:A7, the formula =MEDIAN(A2:A7) returns 13.5 for the example list. For an odd number of observations, it returns the single central number; for an even number, it returns the arithmetic mean of the two central numbers.
MEDIAN does not require the source cells to be sorted. Excel determines the middle from the numbers in the range, so you can leave the original order intact. This is useful when the order has meaning, such as dates of entry or the sequence in which measurements were recorded.
When a range contains blank cells or text labels, MEDIAN ignores them. It does use numeric values, including dates stored by Excel as numbers. Error values in the referenced range can cause the formula to return an error, so check for errors if the result is unexpected. A cell that looks numeric but is stored as text is not treated as a numeric observation by MEDIAN.
How odd and even lists change the answer
With an odd number of numeric values, one observation sits exactly in the center. For five sorted values—3, 9, 12, 15, and 21—the third value, 12, is both the median and the actual middle observation. The formula =MEDIAN(A2:A6) returns 12 when those values are in A2:A6.
With an even number of values, there is no single central observation. For six values, positions three and four are central. MEDIAN averages their values, while an extraction formula must choose one position or use some other stated rule. Do not assume that the median must appear in the source data; a result between two observations is normal.
- Use MEDIAN when you want the statistical midpoint of numeric values.
- Choose the lower or upper central observation when you need a value that actually occurs in an even-sized list.
- State the rule in a report or workbook note so another person can reproduce the result.
Extract an actual central value from a sorted list
In Microsoft 365 or Excel 2021 for Windows, SORT can arrange the numbers within a formula and INDEX can return a position from the sorted result. With six numeric values in A2:A7, the formula =INDEX(SORT(A2:A7),ROUNDUP(ROWS(A2:A7)/2,0)) returns 12, the lower central value. ROUNDUP makes the selected position the center for odd-sized lists and the lower of the two central positions for even-sized lists.
To return the upper central value instead, with the same values in A2:A7, use =INDEX(SORT(A2:A7),INT(ROWS(A2:A7)/2)+1). It returns 15 for the six-number example and still returns the one central observation when the list has an odd number of values. Neither formula averages the two central values; that is what MEDIAN does.
These formulas return a value, not its original row or the other fields from that row. If you need the complete record associated with a central score, first define how ties should be handled, then look up the selected score or sort the full table by that score. Duplicate scores can correspond to more than one record, so the score alone may not identify a unique row.
Exclude blanks and nonnumeric entries deliberately
MEDIAN ignores text and blanks in a referenced range, but a sorting formula may need more deliberate filtering if the range includes headings, notes, or mixed data. FILTER can keep numeric cells before sorting. It is available in Microsoft 365 and Excel 2021, as is LET. With numeric values or other entries in A2:A20, the formula =LET(x,FILTER(A2:A20,ISNUMBER(A2:A20)),INDEX(SORT(x),ROUNDUP(ROWS(x)/2,0))) returns the lower central numeric observation.
For example, if A2:A8 contains 9, a blank, the text note “review,” 3, 21, 15, and 12, FILTER keeps 9, 3, 21, 15, and 12. The sorted numeric values are 3, 9, 12, 15, and 21, so the formula returns 12. It excludes text and blanks instead of accidentally treating them as observations.
This formula assumes at least one numeric value exists. If none do, FILTER returns an error because there is no matching result. You can add error handling if an empty range is a normal possibility, but decide what the workbook should display in that case—such as a message or a blank—rather than hiding the problem without explanation.
Find a middle value within a category
Sometimes the middle value is needed for just one group, such as the median sales amount for a region. In Microsoft 365 or Excel 2021, MEDIAN can be combined with FILTER. Suppose amounts are in B2:B8 and their region labels are in C2:C8, with the rows containing East amounts 10, 20, 30, and 40. The formula =MEDIAN(FILTER(B2:B8,C2:C8="East")) returns 25, the average of the two central amounts, 20 and 30.
FILTER selects rows meeting the category test, and MEDIAN calculates the center of the resulting numbers. Check that the category text matches the source values and that the selected amounts are numeric. If no rows match the category, FILTER returns an error unless you provide an alternative result or handle that case separately.
For an actual central observation within a category, first filter the values and then sort and index that filtered array. Because the formula becomes longer, verify the chosen lower-or-upper rule and test it with a small example whose sorted values you can inspect.
Older Excel versions and a manual method
SORT and FILTER are not available in every older Excel release. MEDIAN has broad availability and is the simplest option if the desired answer is the statistical median. To extract an actual central observation without a dynamic-array formula, copy the numeric values to a clean range, sort them, count the entries, and calculate the central position.
- Select the data and choose Data > Sort Smallest to Largest. If the values are part of a table, sort the whole table by the value column so each row stays together.
- Count the numeric observations, excluding headings and nonnumeric notes.
- For an odd count, use the value at position (count + 1) / 2. For an even count, decide whether you need position count / 2 or position count / 2 + 1.
Sorting changes the displayed order of the selected data, so preserve the original order if it matters. In the sort dialog, make sure Excel expands the selection when sorting one column that belongs to a multi-column table; otherwise, values can become detached from their records. On Excel for Mac, the Data tab also provides sorting commands, though ribbon layout can vary by release. Excel for the web supports sorting and many current formulas, but the exact controls can differ from the Windows desktop interface.
Common problems and how to check the result
A result that seems wrong often comes from the data type or the definition of “middle,” rather than a faulty function. Before changing a formula, inspect the source cells and confirm what should count as an observation.
- Numbers stored as text: They may look like numbers but be ignored by MEDIAN and by an ISNUMBER filter. Convert them to numeric values before calculating.
- Unexpected decimal result: For an even-sized list, MEDIAN averages the central pair. That decimal may not appear in the source data.
- Wrong central item: An extraction formula must use a stated lower- or upper-middle convention for even counts.
- Headers included: Reference only the data rows, not a column heading. A text heading may be ignored by MEDIAN, but it can complicate sorting or filtering.
- Errors in the source: Find and resolve error cells, or handle them explicitly before computing a result.
A useful check is to sort a copy of a small sample and identify the central position by eye. Compare that with the formula result. For a median with an even count, add the two center values and divide by two; for a central-item extraction, check that the returned number is one of those two observations.
Frequently Asked Questions
What is the formula for the middle value in Excel?
Use =MEDIAN(A2:A7) when the numeric data is in A2:A7 and you want the statistical median. With an even number of values, it averages the two central numbers rather than selecting one of them.
How do I get the middle number without sorting the original data?
Use MEDIAN for a statistical midpoint. For an actual central observation in Microsoft 365 or Excel 2021, use SORT with INDEX; the formula can sort its array internally without rearranging the source cells.
Why is my Excel median not in the list?
If the list has an even number of numeric values, the median is the average of the two central values. For example, the median of 12 and 15 is 13.5, even though 13.5 is not an input.
Does MEDIAN ignore blank cells and text?
Yes, MEDIAN ignores blank cells and text in a referenced range. It uses numeric entries, including Excel date values, and an error in the range can make the formula return an error.
Can Excel return the row containing the median?
MEDIAN returns a number, not a row. To return a record, identify the target score and look it up in the original table; decide how to handle duplicate scores because one score may match multiple rows.
Choose the result that fits the question
For most requests to find a list’s “middle,” start with MEDIAN: it is concise, does not require sorting, and handles odd and even counts according to the standard statistical definition. If the requirement is to extract a real observation, use a sorted position and specify how an even-sized list is treated. Filtering first is helpful when the range contains mixed content or when the calculation applies only to a category. Being explicit about the data, the counting rule, and the intended meaning of “middle” makes the result easier to verify and reuse.
Related
- Excel Attendance File to Print: Set Up and Print It
- Excel Timesheet Format: Build a Reliable Weekly Sheet
- Excel Timesheet for Multiple Employees: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel to PowerPoint Linked Cells: How to Link and UpdateTo link Excel cells to a PowerPoint slide in Microsoft 365 for Windows, select and copy the cells…
- Conditional Formatting to Compare Two Sheets in ExcelYes. In Excel for Microsoft 365, you can highlight differences between two sheets with a conditional-formatting formula that…
- Ctrl+E in Excel: Flash Fill Shortcut and How It WorksIn Excel for Microsoft 365 on Windows, Ctrl+E runs Flash Fill: it detects a pattern in your examples…
- Group Rows in Excel: Keyboard Shortcut and StepsTo group rows in Excel for Microsoft 365 on Windows, select the row headers you want to group…
- Excel Merge Cells Command: Shortcuts, Options, and LimitsIn Excel for Microsoft 365 on Windows, select the cells you want to combine, then choose Home >…
- Small Excel Table: Create, Format, and Use OneTo make a small Excel table in Microsoft 365 for Windows, put your headings and records in adjacent…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.