How to Calculate the Median in Excel and Google Sheets

If you need the median in Excel or Google Sheets, the path is not always obvious. The function looks simple but hides traps. Type =MEDIAN(range) into any cell, replacing range with the actual cells. The spreadsheet returns the middle value. For example, =MEDIAN(A1:A10) computes the median of the ten numbers in A1 through A10. This works identically in Excel and Google Sheets. Most readers land here needing the median for a subset, not the whole column. That is where the function's real behavior differs from AVERAGE. The median in Excel and Google Sheets both ignore text and empty cells, but they handle errors differently. The conditional median requires a different approach entirely. The basic function, then the MEDIAN(IF()) and FILTER methods for conditions, then how to ignore zeros and blanks, and finally why PivotTables do not offer a one-click median are covered.

The core syntax is =MEDIAN(number1, [number2], ...). number1 is required; the rest are optional. Feed it individual numbers like =MEDIAN(1, 2, 3) or a range like =MEDIAN(B2:B100). Mix them: =MEDIAN(5, A1:A10, C1) works. Every value in the range must be a number. The function ignores text, logical values like TRUE or FALSE, and empty cells. That behavior is a double-edged sword: point MEDIAN at a column with blank rows or text headers and it still works, but if you expect it to error on a text value, it will not. Numbers stored as text, which happens when you import from CSV, are included only if typed as numbers. Text strings are ignored. The result is the middle value of the sorted list. With an odd count, it is the exact middle. With an even count, it averages the two middle values. For 100 values, the median sits between the 50th and 51st when sorted; the formula averages those two. One failure case: with no numeric values, Excel returns #NUM! and Google Sheets returns #N/A. Check your range if you see either error.

When you need the median for a group, like the median salary for engineers only, the plain MEDIAN function is not enough. The classic array formula is =MEDIAN(IF(criteria_range=criteria, median_range)). Enter it with Ctrl+Shift+Enter in older Excel versions. Excel 365 and Excel 2021 handle it as a dynamic array with just Enter. The IF function checks each cell in criteria_range. If it matches, the corresponding value in median_range is passed to MEDIAN. If not, IF returns FALSE, which MEDIAN ignores. For one condition, =MEDIAN(IF(A2:A100="Engineer", B2:B100)) gives the median of B2:B100 where A2:A100 equals Engineer. The critical detail: the ranges must be the same size. If A2:A100 has 99 cells and B2:B100 has 100, the formula returns #VALUE!. For multiple conditions, say region equals North and department equals Sales, multiply the conditions: =MEDIAN(IF((A2:A100="North")*(C2:C100="Sales"), D2:D100)). The asterisk acts as AND. This approach fails silently if you forget Ctrl+Shift+Enter in versions that need it. Test on a small range first. A cleaner alternative is the FILTER function, covered next, which Excel 365 offers and avoids the array entry.

FILTER Syntax for Conditional Medians

The FILTER function gives a more readable conditional median. In Excel 365 and Google Sheets, write =MEDIAN(FILTER(range, condition)). FILTER returns the array of values that meet the condition; MEDIAN then reduces it to a single number. For example, =MEDIAN(FILTER(B2:B100, A2:A100="East")) computes the median of B2:B100 where column A says East. This works for multiple conditions too: =MEDIAN(FILTER(D2:D100, (A2:A100="North")*(C2:C100="Sales"))) in Excel or Google Sheets, though Google Sheets uses the same syntax.

Platform Differences in Error Handling

The difference between the two platforms appears when FILTER finds no matching rows. Excel returns #CALC! if you omit the optional if_empty argument. Google Sheets returns #N/A. To handle that, add a third argument: =MEDIAN(FILTER(B2:B100, A2:A100="Zzz", 0)) returns 0 as the median if no rows match. For Google Sheets, the FILTER function is available in all current versions, so this approach is safer than the MEDIAN(IF()) array formula across versions.

The median in Excel and Google Sheets both ignore blank cells automatically. Empty cells are not numbers. The problem is zeros: a zero is a number, so =MEDIAN(A1:A10) includes it as a real value. This drags the median down if the zeros are placeholders for missing data rather than true observations. If your data has zeros that mean "no data", use the conditional median to exclude them. The FILTER approach is the cleanest: =MEDIAN(FILTER(A1:A10, A1:A10<>0)) returns the median of all non-zero values. For blanks, the existing behavior already handles it. In Google Sheets, combine conditions to exclude both zeros and blanks: =MEDIAN(FILTER(A1:A10, (A1:A10<>0)*ISNUMBER(A1:A10))). The ISNUMBER check matters if your range includes text. FILTER with A1:A10<>"" still passes text values, and MEDIAN ignores text anyway. But ISNUMBER protects against a specific failure: if your range contains an error like #DIV/0!, Google Sheets returns #N/A even if the error is in a cell you want ignored. Excel's MEDIAN handles errors by returning the error itself if any cell in the range has one. Clean errors first with IFERROR inside the FILTER or a helper column.

The Limitation of PivotTable Aggregation

Build a PivotTable in Excel and open the Value Field Settings. The Summarize by list offers Sum, Count, Average, Max, Min, and Product, but not Median. The default aggregation methods are designed for additive measures, things you can sum across rows and columns. The median is not additive: the median of a group is not computable from the medians of its subgroups. The PivotTable engine cannot roll it up efficiently. Google Sheets PivotTables have the same limitation. Neither offers a median as a built-in summarize option.

Workarounds for a Median in PivotTables

The workaround is a DAX measure in Excel's Power Pivot or Data Model. Add your data to the Data Model, create a measure using =MEDIAN(Table1[Column]), then drag that measure into the PivotTable's Values area. The key action: select the PivotTable, check the Add this data to the Data Model box, then use the Power Pivot tab to write the measure. Power Pivot ships with Excel 2016 and later but is off by default. If you do not have it, use a helper column: add a column that assigns each row a rank within its group, then use an array formula to find the middle rank. This is fragile. A simpler alternative is to skip the PivotTable and use a MEDIAN(IF()) or FILTER formula in a regular cell, referencing the same group labels from a helper column. If your PivotTable must update automatically when the source data changes, the DAX approach is the only robust route.

The Google Sheets median function looks identical to Excel's. For basic use, it is. Both use the same interpolation method for quartiles: Hyndman-Fan type 7, the default in R and numpy.percentile. Both return the middle value for an odd count and the average of the two middle values for an even count. The differences are in error handling and compatibility. Excel's MEDIAN returns #NUM! when no numeric values are present. Google Sheets returns #N/A. If your range contains any error value like #DIV/0! or #VALUE!, Excel propagates that error to the result. Google Sheets returns #N/A for the entire formula. A single bad cell in a large range silently turns your median into an error in Google Sheets. That is a useful alert. Excel gives you the wrong median if the error is in a cell you assumed was numeric. A second difference: Google Sheets MEDIAN includes numbers stored as text only if they were entered as numbers. Text strings are ignored, matching Excel. For compatibility across both, use QUARTILE.INC and QUARTILE.EXC, which map to types 7 and 6 respectively. Excel's QUARTILE.EXC was introduced in Excel 2010, so older versions do not have it. That type 6 versus type 7 difference explains why two calculators give different quartiles for the same data. For the median itself, both methods agree, so the 50th percentile is identical.

When your data comes as a frequency table, like 5 people with 1 sibling, 8 with 2, and 3 with 3, you cannot type the raw numbers into =MEDIAN because you do not have them. The median in Excel has no direct frequency table argument. Reconstruct the raw data or interpolate. For a grouped frequency table with class intervals, the standard formula is median = L + (n/2 - F) * h / f. L is the lower boundary of the median class. n is the total frequency. F is the cumulative frequency before the median class. h is the class width. f is the frequency of the median class. In a spreadsheet, compute the cumulative frequency in a helper column. Find the class where cumulative frequency crosses n/2. Then apply the formula. For ungrouped data with small counts, the fastest method is to expand the table: put each value in a cell repeated its frequency times, using a formula like =IF(ROW()<=SUM($B$2:B2), A2, "") across a row, then run =MEDIAN on the expanded column. That works but is ugly for large tables. It fails if you have open-ended classes like "100 or more", because the median formula needs an upper bound for the median class. In that case, make an assumption, typically that the upper bound is the lower bound plus the width of the previous class, and state it plainly. The NCSCERT Class 10 Mathematics textbook teaches this formula. It is the only method that handles grouped data correctly without expansion.

The median is the middle value of ordered data. The mean is the arithmetic average. The difference is stark when data is skewed. Take household incomes: a small number of billionaires pulls the mean up. The median stays at the middle household's income. The US Census Bureau reports median household income rather than the mean. The Census Bureau's technical documentation explains that the median is resistant to outliers. A single extreme value changes the mean but not the median, unless that value crosses the middle. The mode is the most frequent value. It can be far from the median, especially in bimodal data where two peaks sit on either side of a low-frequency gap. For a right-skewed distribution, where the tail stretches right, the mean is greater than the median. For left-skewed, the mean is less. If mean equals median, the distribution is roughly symmetric. When you choose a summary, ask what you need. For total income across a population, use the mean and multiply by the count. For a typical person's income, use the median. In a spreadsheet, =AVERAGE(range) and =MEDIAN(range) on the same data can give answers that differ by 20% or more. The median is almost always the better description of the center for money, home prices, or any measure with a few high or low values. The failure case is when you need the sum, like total payroll. The median tells you nothing about the total.

Function or Method	What It Computes	Ignores Text	Ignores Errors	Interpolation Method
=MEDIAN(range) in Excel	Middle value; averages two middle values for even count	Yes	No, returns #NUM! or #VALUE!	Not applicable (exact value)
=MEDIAN(range) in Google Sheets	Middle value; averages two middle values for even count	Yes	No, returns #N/A	Not applicable (exact value)
=QUARTILE.INC(range, 2)	50th percentile	Yes	No, returns #NUM!	Hyndman-Fan type 7 (inclusive)
=QUARTILE.EXC(range, 2)	50th percentile	Yes	No, returns #NUM!	Hyndman-Fan type 6 (exclusive)
Manual hand calculation	Middle value after sorting	N/A	N/A	Depends on convention; type 7 common

The median is not a universal answer. For nominal data, like eye color or zip code, there is no order. The median is meaningless; only the mode works. For ordinal data where the gaps between values are unknown, the median is valid as a position but the mean is not, because you cannot average categories. For bimodal data, the median might fall in a low-frequency valley between two peaks, giving a value that no observation approximates. For example, in a distribution of exam scores with clusters at 40 and 90, the median could be 65, which no student scored near. The mode or a histogram serves better. The median of a sample is an estimate of the population median, and it has sampling error. Without a confidence interval, you cannot claim certainty. If you need that interval, use a bootstrap or the formula for the standard error of the median, which is about 1.253 times the standard error of the mean for normal data. Quartiles and the IQR belong with the interquartile range guide. The step-by-step sorting and counting rules for hand calculation and the weighted median are separate topics.

Three Common Failure Modes

Three failures trip up most spreadsheet users. First, the range mismatch: in =MEDIAN(IF(A2:A100="x", B2:B99)), the two ranges are different sizes. The formula returns #VALUE! or a wrong result without warning. Fix it by selecting both ranges together and checking the row counts match. Second, the array entry mistake: in Excel 2019 and earlier, if you type =MEDIAN(IF(...)) and press Enter instead of Ctrl+Shift+Enter, you get #VALUE! or the median of only the first row. Use FILTER if you have Excel 365 or Google Sheets, or remember the three-key entry. Third, the hidden error: a cell in your range contains #DIV/0! from a division formula, and your median becomes an error. Excel's MEDIAN propagates that error, so you silently lose the result. Google Sheets gives #N/A.

How to Fix Each Failure

Wrap the source range in IFERROR: =MEDIAN(IFERROR(A1:A10, "")) in Excel, which converts errors to blanks that MEDIAN ignores. For Google Sheets, IFERROR returns an empty string which MEDIAN ignores, but the outer MEDIAN still returns #N/A if ALL values are errors. Leave at least one valid number. Test on a small range before applying to a column of many rows. A silent mistake in the median for a financial report is worse than an obvious error.

After you compute a median, verify it against a sort. In Excel, sort the column in ascending order. For an odd count, the median is the value in the middle row. For an even count, it is the average of the two middle rows. This catches the most common errors: referencing the wrong column, including a header row, or using AVERAGE instead of MEDIAN. In Google Sheets, the sort is the same, but the median function ignores text. If your header is in row 1 and your range starts at A1, the header is ignored, giving the same result as A2:A100. A second check: if your data has outliers, the median should be roughly in the middle of the sorted list, not near the maximum or minimum. If it is not, your condition in MEDIAN(IF()) or FILTER is filtering to the wrong group. A third check is to compute the mean alongside, =AVERAGE(range). If the mean is far from the median, your data is skewed, which is expected for income or price data. But if the median is outside the interquartile range, something is wrong. The median can fall below Q1 or above Q3 only if your dataset is tiny, like 3 values, where Q1 is the first value and Q3 is the third value, making the median equal to the second value. For any dataset larger than 5, trust but verify: copy a sample of 20 values into a blank column, sort by hand, and compute the median manually.