# Finding the Mode in Excel for Statistical Analysis

The MODE function is a premade function in Excel, which is used to find the number seen most times.

Note: The mode is a type of average value, which describes where most of the data is located. You can read more about mode in our: Statistics Mode Tutorial.

Lets have a look at an example where we help the Pokemon trainers to calculate the mode of numbers of Pokeballs.

It returns the number `1` which is seen 7x times. The closest number to it is `3` and `4` which are seen 3x times each.

The mode is one of several key statistical values that provides insight into a dataset. Calculating the mode in Excel is easy with the MODE function. In this comprehensive guide I’ll explain what the mode is why it’s useful, and walk through steps to find it using Excel formulas.

## What is the Mode in Statistics?

The mode refers to the value that occurs most frequently in a dataset. For example, in the dataset [5, 7, 7, 7, 8, 9], the mode is 7 because it appears the most times.

Knowing the mode helps you identify patterns and tendencies within numerical data. Along with the mean (average) and median, the mode is a core part of descriptive statistics.

Here are some examples of when calculating the mode is useful

• Identifying the most popular product sold by a company
• Finding the most common grade on a test
• Seeing which size is ordered most frequently in clothing sales
• Determining the predominant weather pattern in a region

Unlike the mean and median which must be a single numeric value, the mode may be multiple values if two or more numbers are tied for the highest frequency.

Now let’s look at how to find the mode in Excel using formulas.

## Calculating the Mode with Excel Formulas

Excel has a built-in MODE function that makes finding the mode easy. Here are the steps to use it:

1. Input your dataset into an Excel spreadsheet. Each data value should be in its own cell.

2. Click on the cell where you want the mode result to display.

3. Type the formula `=MODE(number1,number2,...)` using actual cell references instead of generic numbers.

4. Press Enter to see the mode calculated automatically.

For example, if your data is in cells A2 to A11, you would use:

`=MODE(A2:A11)`

This finds the mode within this cell range.

## Handling Multiple Modes in Excel

If there are multiple modes with the same frequency, the MODE formula will return the smallest mode value by default.

To list all values with the highest frequency, you can use the MODE.MULT function:

`=MODE.MULT(A2:A11)`

This will return an array with all repeated mode values. To show them separated on one line, combine it with TEXTJOIN:

`=TEXTJOIN(", ",TRUE,MODE.MULT(A2:A11))`

The comma and space inside the TEXTJOIN formula is used to delimit the values.

## Finding the Mode Frequency in Excel

To return the number of times the mode occurs (the mode’s frequency), combine MODE with COUNTIF:

`=COUNTIF(A2:A11,MODE(A2:A11))`

This counts the values equal to the mode. Change the range to your dataset.

You can also get the mode frequency along with the value using an array formula:

`{=MODE(A2:A11),COUNTIF(A2:A11,MODE(A2:A11))}`

Confirm this array formula by pressing Ctrl + Shift + Enter.

## Examples Calculating the Mode in Excel

Let’s look at some examples of finding the mode with sample datasets:

<div class=”table-wrapper” markdown=”block”>

Data Formula Mode Result
5, 7, 7, 7, 8, 9 `=MODE(A2:A7)` 7
6, 7, 7, 8, 8, 9 `=MODE.MULT(A2:A7)` {7,8}
1, 3, 6, 6, 6, 8 `=MODE(A1:A6)` 6
5, 5, 5, 8, 9, 9 `=MODE(A1:A6)` 5

</div>

The MODE.MULT formula is useful when there are multiple modes with equal frequency.

## Finding the Mode of Non-Contiguous Data

The MODE formula requires a contiguous range of cells. To find the mode across multiple ranges or non-adjacent cells, you can use this pattern with MODE.SNGL:

`=MODE.SNGL((range1,range2,...))`

For example:

`=MODE.SNGL((A2:A6,C2:C5,E1:E3))`

This allows combining different cell ranges. MODE.SNGL will ignore text values and only consider numbers.

## Calculations Using the Mode

Once you have the mode value, you can use it in other Excel formulas to gain further insights. Here are some examples:

• Percentage of values equal to the mode:`=COUNTIF(data,mode)/COUNT(data)`
• Difference between mean and mode: `=AVERAGE(data)-MODE(data)`
• Percentage that the mode occurs out of total observations: `=COUNTIF(data,mode)/COUNT(data)`

Analyzing these statistics helps you interpret what the mode indicates about your data.

## Charting the Mode in Excel

Visualizing your dataset makes it easier to see the mode. Steps to create a chart showing the mode:

1. Select your data range and insert a column or bar chart.
2. Right click the horizontal axis and select Format Axis.
3. Under Axis Options, change the Axis Type to Text Axis.
4. Right click the chart and choose Select Data.
5. Edit the horizontal axis labels to show each unique data value.

This transforms the chart into a frequency distribution with a column or bar representing each value. The mode will be clearly visible as the tallest column or bar.

## Limitations of Using the Mode

While useful, the mode also has some limitations to be aware of:

• The mode is sensitive to outliers that occur once or a few times. These outliers distort what is most “typical” in the data.
• For symmetrical, bell-shaped distributions, the mean, median, and mode are all similar. Highly skewed distributions have a mode far from the median.
• The mode doesn’t consider all the data – only the most frequent value(s). It ignores the overall distribution.

So the mode should not be used in isolation. Combine it with mean, median, standard deviation, and visualization to properly analyze data.

Finding the mode quickly reveals valuable information about tendencies and patterns within data samples. Excel’s MODE and MODE.MULT functions calculate the mode with ease. The steps covered in this guide enable you to:

• Use MODE on a dataset to get the single most common value
• Handle multiple modes using MODE.MULT
• Get the frequency along with the mode itself
• Combine non-contiguous ranges using MODE.SNGL
• Visualize the mode in a frequency distribution chart

Being able to derive the mode expands your statistical analysis skills in Excel. Combined with averages, spread, and visualization, the mode provides a comprehensive picture of your data’s central tendency and shape.

## Tutorials Tutorials filter input

W3Schools offers a wide range of services and products for beginners and professionals, helping millions of people everyday to learn and master new skills.

## How to Calculate the Mode in Microsoft Excel

How do I calculate the mode of a group of numbers?

To calculate the mode of a group of numbers, use the MODE function. MODE returns the most frequently occurring, or repetitive, value in an array or range of data. Important: This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage.

How to use mode function in Excel?

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data.

How to calculate mode in Excel?

In Microsoft Excel, you can calculate a mode by using the function of the same name, the MODE function. For our sample data set, the formula goes as follows: In situations when there are two or more modes in your data set, the Excel MODE function will return the lowest mode. Mean vs. median: which is better?

How to find multiple modes in Excel?

Use MODE.MULT to find multiple modes. 1. The MODE function below returns the most frequently occurring number (8). 2. The new MODE.SNGL function (SNGL stands for single) produces the exact same result. 3. Change the value in cell A2 to 5. In this example, there are multiple modes (5 and 8).