Calculate Mode in Excel

To calculate the Mode in Excel you need to use the Mode Formula.  This short guide will show you how to calculate Mode in Excel and also help you understand why you should use the Mode…

What is the Mode in Excel?

Mode is a statistical calculation you can apply in Excel in order to find out what the most frequently occurring value is within a range of cells that you specify.

For example if you had a list of three values (10, 10, 30) then the Mode would equal 10 because within your list of three values you have 10 occurring twice and 30 only occurs once.

Mode Formula in Excel

The Mode formula, or Mode function, takes the format:

=MODE (number1, number2, number3…)

This is where “number1, 2, 3” etc. represent the range of cells that you want in your Mode calculation.

Below is an example shown in Microsoft Excel, here we have the height in centimeters of five people:

As shown in the example there are three people out of the five who have a height of 180cm, this is why the Mode is calculated as 180cm, remember it is the most frequent value in your range.

Why use Mode in Excel?

In a similar way to the Average and the Median the Mode forms one of the basic statistical descriptors that can be useful when analysing a dataset.  That said it is probably one of the weaker descriptors and arguably the only main use is for nominal data, i.e. non-numerical data like surnames.

For example it might be useful to know that from 1000 people surveyed the most common surname is SMITH, that sort of information can provide a useful piece of insight for a report.  However consider a business analysing their daily sales over a year and there is not much value in knowing their most common daily sales amount, it would be a great deal more useful to know what the average daily sales are.

What happens if you have only unique values in your range?

This can occur if you apply the Mode calculation in Excel to a list of numerical values that are completely distinct.  For example if you had two values, 10 and 20, then there is no most frequently occurring number as both 10 and 20 occur one time each.  In those cases Excel would return the #N/A error.  The reality is that most statistical analysis would be applied against a larger dataset than two values so you should rarely come across this issue.

Hopefully you now understand how to calculate Mode in Excel and its main use for statistical analysis.

Keep Excelling,