How to Calculate the Median and Mode in Microsoft Excel

When summarising numerical datasets, the arithmetic mean (average) is frequently the first metric calculated. However, the mean is exceptionally sensitive to extreme statistical outliers—such as executive compensation packages in wage distributions or extreme sensor spikes in scientific observations—which skew the calculated result and paint a distorted picture of typical values. To obtain an authentic understanding of central tendency and frequency distributions, data analysts rely on the median and the mode.

Microsoft Excel provides dedicated, high-performance statistical functions designed to isolate the exact midpoint of ordered data and uncover the most frequently occurring observations across single or multiple data ranges.

Understanding the Statistical Differences: Mean vs Median vs Mode

Selecting the appropriate central tendency metric depends on the distribution characteristics of your numerical series:

• Mean: The mathematical sum of all data points divided by the count. Best suited for normally distributed data without severe outliers.
• Median: The physical numeric midpoint when values are sorted in ascending sequence. Exactly 50% of values lie above the median, and 50% lie below. Outliers at either end do not distort the median value.
• Mode: The value that appears with the highest frequency in a data collection. Useful for identifying popular inventory sizes, customer ratings, or repetitive error codes.

Calculating the Median in Excel with the MEDIAN Function

The MEDIAN function evaluates one or more ranges, arrays, or numbers, returning the exact middle value regardless of whether the source data is pre-sorted:

=MEDIAN(number1, [number2], ...)

1. Select the destination cell where you want the median figure displayed (for example, D2).
2. Enter the formula referencing your data range:

=MEDIAN(B2:B50)

3. Press Enter to evaluate.

Note on Even Data Sets: If your range contains an odd count of values (e.g., 25 rows), MEDIAN returns the exact centre value. If the dataset contains an even count (e.g., 50 rows), Excel automatically calculates the arithmetic mean of the two middle numbers.

Calculating the Single Most Frequent Value: MODE.SNGL

In legacy workbooks, Excel utilized the general MODE function. In modern versions of Excel (Microsoft 365, Excel 2013 through 2024), Microsoft replaced this with MODE.SNGL to deliver enhanced statistical clarity:

=MODE.SNGL(number1, [number2], ...)

1. Click an empty worksheet cell (such as D3).
2. Enter the formula targeting your values:

=MODE.SNGL(B2:B50)

3. Press Enter. Excel outputs the single value that occurs most frequently.

Troubleshooting #N/A Errors: If every number in your referenced range is entirely unique, MODE.SNGL returns an #N/A error because no mode exists. You can wrap the formula in IFERROR to display a friendly message:

=IFERROR(MODE.SNGL(B2:B50), "No Duplicate Values Found")

Handling Multiple Frequent Values: MODE.MULT

Real-world datasets are often multimodal, meaning two or more distinct values share the highest identical frequency count (for example, if both scores 75 and 92 appear four times). The MODE.SNGL function merely returns the first one it encounters, obscuring the secondary peak.

The MODE.MULT function leverages Excel’s dynamic array calculation engine to return all tied modes simultaneously in a vertical spill:

1. Select an empty cell with unobstructed rows below it (e.g., F2).
2. Enter the dynamic array formula:

=MODE.MULT(B2:B50)

3. Press Enter. If your data contains multiple modes, Excel automatically spills each recurring value down consecutive cells in column F.