Median vs Average in Excel: Which Should You Use?

How-to basics · Updated November 20, 2026

In Excel, use AVERAGE to calculate the arithmetic mean and MEDIAN to find the middle value in a set of numbers. The mean is useful for totals spread across every observation; the median is often more representative when the data contains extreme values or is strongly skewed.

What average and median mean

In everyday conversation, “average” usually means the arithmetic mean: add all the values and divide by how many values there are. Excel’s AVERAGE function calculates that measure. It gives every number a role in the result, so changing even one observation can change the mean.

The median is the central value after the numbers are arranged from smallest to largest. With an odd number of observations, it is the single value in the middle. With an even number, it is the mean of the two central values. Excel’s MEDIAN function handles both cases without requiring you to sort the cells first.

These are both measures of central tendency, but they answer slightly different questions. The mean asks what value each observation would have if the total were shared equally. The median identifies the halfway point: half the observations are at or below it, and half are at or above it. Neither measure is automatically the “right” average in every situation.

How to calculate each one in Excel

Enter your numerical observations in a worksheet range, then use the corresponding function in an empty cell. For example, with the values you want to summarize in B2:B8, enter the formula for the arithmetic mean as follows:

=AVERAGE(B2:B8)

With the same values in B2:B8, calculate the midpoint using:

=MEDIAN(B2:B8)

Press Enter after typing either formula. Excel displays the result in the formula cell. You can replace B2:B8 with another range, such as A2:A50, or select the range with the mouse while entering the function. The function name is not case-sensitive.

If you want to summarize different groups, use separate formulas with the appropriate ranges. Keep the original observations in the worksheet: both functions calculate from the cells and update when those values change. If the result appears rounded, the stored result may still have more decimal places than the cell displays. Use the Home tab’s Number controls to change the displayed decimal places; formatting changes appearance, not the underlying calculation.

A worked example: why the answers can differ

Suppose B2:B8 contains seven weekly order amounts: 24, 26, 27, 28, 29, 30, and 1,100. The large final order pulls the mean upward. With those values in B2:B8, the mean is calculated by:

=AVERAGE(B2:B8)

The result is approximately 180.57: the total is 1,264, divided by seven. With the same values in B2:B8, the median is:

=MEDIAN(B2:B8)

The result is 28, the fourth value in the ordered set. Six of the seven orders are between 24 and 30, so 28 may describe a typical order better than 180.57. But if the question is how much revenue the business earned per week on average across these observations, the mean’s sensitivity to the large order is meaningful rather than a flaw.

An even-sized example shows how Excel defines the median. If D2:D5 contains 20, 30, 40, and 90, the two middle values are 30 and 40. With those four values in D2:D5, the median is:

=MEDIAN(D2:D5)

It returns 35, the mean of 30 and 40. With the same values in D2:D5, the arithmetic mean is:

=AVERAGE(D2:D5)

It returns 45 because the total, 180, is divided by four. The two results are different because the high value of 90 affects the mean more strongly than it affects the median.

When to choose the mean

Choose the mean when every value should contribute proportionally to a total, or when your analysis specifically requires an arithmetic average. It is often suitable for calculating average cost per item, average production per day, or average test score, provided the underlying data and the purpose of the calculation make that interpretation useful.

  • Use the mean for totals and rates: If a total is distributed equally among a known number of observations, the mean represents that equal share.
  • Use it when extreme values are meaningful: A large sale, unusually high expense, or exceptional measurement may be an important part of the result rather than an error to discount.
  • Use it when later calculations depend on it: Many statistical methods are built around the mean, so substituting a median would change the analysis.

The mean can be misleading as a description of a “typical” case when values are highly uneven. For example, a few very expensive transactions can make average transaction value much higher than what most customers spend. In that situation, report the mean alongside the median or explain which one answers the business question.

When to choose the median

Choose the median when you want a typical midpoint and unusually high or low observations would distort the mean. It is commonly useful for prices, incomes, waiting times, and property values, where distributions can have a long tail. A median does not mean that every observation is close to it; it only marks the middle position.

The median is resistant to extreme values in a way the mean is not. In the order example, replacing 1,100 with 11,000 would greatly increase the mean, but the median would remain 28 as long as the central ordering did not change. This property makes the median helpful for summarizing skewed data, but it also means it may not reflect how much value is in the extremes.

For a reader-facing report, consider showing both measures when each provides useful context. A median salary can describe the midpoint employee while the mean shows the impact of very high salaries on the overall total. State which measure you report; the word “average” alone can be ambiguous.

How blanks, zeros, and text affect the result

For a range reference such as B2:B8, AVERAGE and MEDIAN use numeric cells and ignore blank cells and text entries. A numeric zero is not blank: both functions include zero as an observation. That distinction matters when zero means a real value, such as no sales on a particular day, versus when a cell was left empty because data was not collected.

For example, if a range contains 10, 20, and 0, the zero lowers the mean and becomes part of the ordered data used to find the median. If the zero actually means “unknown,” including it can give a misleading summary. Decide whether those cells represent real measurements before calculating; do not replace missing values with zero merely to fill gaps.

Text typed into a referenced cell is not treated as a numeric observation by these functions. This can conceal data-entry problems: a value that looks like a number may be stored as text and therefore not contribute. Check suspicious cells, especially if a result does not match a manual calculation. Error values in the referenced range can also cause a formula error, so find and resolve errors rather than assuming Excel silently skips them.

Filtered rows, outliers, and other common pitfalls

Filtering a list hides rows on screen; it does not automatically make AVERAGE or MEDIAN exclude those rows. A formula that refers to an ordinary range still calculates from the numeric cells in that range, including values in filtered-out rows. If the goal is to summarize only a filtered subset, verify that the chosen method actually excludes hidden records. Excel’s SUBTOTAL function can calculate certain summaries such as an average for visible rows, but it does not provide a median operation.

Do not delete an outlier simply because it changes the mean. First check whether it is a valid observation, a data-entry mistake, or a value that belongs to a different population. Correct documented errors; retain valid extremes when they matter to the question. If you exclude records, make the rule clear so a reader can understand how the result was produced.

Another common mistake is treating the median as a promise that half the values are strictly below it and half strictly above it. Ties can occur, and when there are an even number of values, the median may be a value between observations rather than an observed value itself. The precise interpretation is that it divides the ordered data into two halves, allowing for ties at the midpoint.

Other Excel summaries and how they compare

Mean and median are not the only ways to describe a dataset. The mode is the most frequently occurring value, which can be more informative when repeated categories or common sizes matter. The minimum and maximum show the range’s endpoints, while quartiles describe additional points in the ordered distribution. A single center value cannot show how tightly or widely observations are spread.

For example, two groups can have the same median but very different ranges: one might cluster closely around the median, while the other contains both very low and very high values. Pairing a center with a measure of spread, such as the range or interquartile range, gives readers more context. For a straightforward worksheet summary, a useful presentation might include the mean, median, count, minimum, and maximum, as long as each measure serves the reader’s purpose.

There is also a difference between the mean of individual observations and an average of precomputed group averages. If groups contain different numbers of observations, simply averaging their averages gives each group equal weight, not each original observation. When the intended result is the overall mean across all records, calculate AVERAGE over the original numeric observations, or use an appropriately weighted calculation.

Frequently Asked Questions

Is median the same as average in Excel?
No. In Excel, “average” usually refers to the arithmetic mean calculated by AVERAGE, while MEDIAN returns the center of the ordered values. They can match, but they often differ when data is skewed or contains extreme values.

Which Excel formula finds the median?
Use MEDIAN with the cells or range you want to summarize. For example, if the observations are in C2:C20, enter the formula in another cell: =MEDIAN(C2:C20).

Does Excel MEDIAN ignore zeros?
No. A zero in a referenced range is a numeric value, so MEDIAN includes it when finding the middle. Blank cells and text entries in the range are treated differently and are not counted as numeric observations.

Does AVERAGE include hidden or filtered rows?
Yes, an ordinary AVERAGE formula over a range includes numeric values in rows hidden by a filter. Filtering changes what is displayed, not which cells that formula references.

Why do my median and average match?
They can match when the values are balanced around the center, or simply by coincidence. Equality does not mean the formulas are interchangeable: adding or changing observations can affect the mean and median differently.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.