How to Calculate Mean, Median and Mode in Excel

“What’s the average?” is usually the wrong question.

In this video we work with real shipping data — days in transit for overseas
deliveries — and see what happens when one shipment takes 75 days. The mean
jumps. The median barely moves. Knowing which to quote is the difference between
a useful number and a misleading one.

Watch: mean, median and mode in 7 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

The first thing to do with a new data set is find its centre. The example lists the number of days shipments took to arrive from overseas (A2:A12), from 23 to 38 days.

1. Mean (average)

The mean is the sum of the values divided by how many there are. In D2:

=AVERAGE(A2:A12)

2. Median (the middle value)

Sort the values from smallest to largest and the median is the one in the middle: with 11 values, the 6th. In D4:

=MEDIAN(A2:A12)

In the example it’s 28. With an even number of values, Excel averages the two in the middle.

3. Compare the two

When the mean and median are close, the data is fairly evenly spread. Change one shipment to a terrible 75 days and the mean jumps, but the median stays at 28. The median isn’t pulled around by outliers, which is why it’s often the better summary for skewed data.

4. Mode (the most common value)

In D6:

=MODE.SNGL(A2:A12)

It returns 28, which appears three times. If two values are tied, MODE.SNGL returns the one that appears first in the list.

5. All the modes when there’s a tie

To see every value that ties for most common, use:

=MODE.MULT(A2:A12)

In Excel 365 the results spill into the cells below automatically, for example 27 and 28 if both appear the same number of times.

Go further

My Excel Statistics & Data Analysis: Zero to Analyst course goes much further into what your numbers mean.