While working with Excel, we can easily determine the most frequently occuring number in a data set using the MODE function. This step by step tutorial will assist all levels of Excel users in identifying the value that is repeated the most times in a range.
Figure 1. Final result: Most frequently occuring number
Syntax of the MODE function
MODE function returns the mode, or the number which appears most often in a set of numbers
The parameters are:
- number1 – any value for which we want to determine the mode; could be an array or reference to cells containing numbers
- Only number1 is required; succeeding numbers are optional
Setting up Our Data
Our table contains three rows, Rows A to C, containing a set of numbers from column C to column J. We want to determine mode of each row in cells L3:L5.
Figure 2. Sample data for most frequently occuring number
Determine the mode in Row A
In order to determine the mode, or the number occurring most frequently in Row A, we follow these steps:
Step 1. Select cell L3
Step 2. Enter the formula:
Step 3: Press ENTER
Figure 3. Entering the formula to determine the mode
Our array for the mode of Row A is the range C3:J3. The final result in cell L3 is 2, which is the number repeated most often (6 times) in Row A.
More than 1 mode in a data set
There can be more than one mode in a data set. However, the MODE function is limited to show only one mode, which is the mode that first occurs in the data set.
Let us take note of Rows B and C. Both rows have numbers 2 and 4, each number occurring four times in each row. To determine the mode for Rows B and C, enter the following formulas:
In cell L4, enter the formula:
In cell L5, enter the formula:
As shown below, the mode of Row B is 2, because the number 2 occurs before the number 4 in Row B. On the other hand, the mode in Row C is 4.
Figure 4. Output when there are two modes: MODE function returning the first mode
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.