How to Calculate Mean, Median, and Mode in Excel

Mean, median, and mode are also called central tendency for a probability distribution. They are used to measure the central position of the data set, also known as summary statistics. In this article, we will outline step by step how to calculate mean, median, and mode using Excel software. The knowledge of using Excel is vital for both students and professionals alike. Expertise in Excel software helps students with their day-to-day Excel assignments, whereas professionals use it to automate most of their work. The data being used for demonstration in this article can be accessed by clicking the link below.

The data is the entry for 100 customers who spent in a random shop in the United States.

1. Calculating the Mean (Average)

The mean (or average) is calculated by summing up all the numbers in a dataset and dividing by the total number of data points.

Steps to Calculate the Mean in Excel

• Click on an empty cell where you want to display the mean
• Then enter this formula {=AVERAGE()}
• After entering the formula and picking the average function, you will be required to highlight the data under the column expenditure(Column B) using SHIFT+CTRL+DOWN ARROW. After, you should see the following.

• Then press enter on your keyboard, and you will have your mean calculated.

2. Calculating the Median

The median is the middle value in a dataset when the values are sorted in ascending or descending order. If there is an even number of values, the median is the average of the two middle numbers.

Steps to Calculate Median

• Click on an empty cell where you want to display the median
• Then enter this formula {=Median()}
• After entering the formula and picking the median function, you will be required to highlight the data under the column expenditure(Column B) using SHIFT+CTRL+DOWN ARROW. After, you should see the following.

• At this point, you will close the bracket and then press enter. The Excel will calculate the median and present it as shown below.

3. Calculating the Mode

The mode is the most frequently occurring number in a dataset. There can be:

More than two modes (multimodal). If there is no repeating number, there is no mode.

• One mode (unimodal),
• Two modes (bimodal), or
• More than two modes (multimodal). If there is no repeating number, there is no mode.

Steps to Calculate the Mode:

• Click on an empty cell where you want to display the Mode
• If you only want to find a single mode, use the following formula: {=MODE.SNGL(B2:B101)}
• If your data has multiple modes and you want to find all of them, use: {=MODE.MULT(B2:B101)}
• In our case, we will use a single-mode formula: {=MODE.SNGL(B2:B101)}
• After entering the formula and picking the Singe Mode function, you will be required to highlight the data under the column expenditure (Column B) using SHIFT+CTRL+DOWN ARROW. After, you should see the following.

• The step is to close the bracket and then press enter. The Excel spreadsheet will calculate the mode and present it as shown below.

Additional Notes:

• If there is no mode (no repeating numbers), Excel will return a #N/A error.
• If you are using an older version of Excel (before Excel 2010), use = MODE(A1:A10) instead of = MODE.SNGL(A1:A10).

Frequently Asked Questions