Go Back

Most frequently occurring number

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

Syntax: =MODE(number1,[number2],...])

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: =MODE(C3:J3)

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.  

Example:

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:  =MODE(C4:J4)

In cell L5, enter the formula:  =MODE(C5:J5)

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to count the most frequently lended in states, per lender (ref: Lender Loan Tape Summary, cell B4), for #1-3 most frequently lended states. (cells G9:G11 of "Lender Loan Tape Summary" tab)
Solved by F. S. in 29 mins
I have a column with multiple entries, for example row 1 has A,B,C row 2 has A,B row 3 B,C row 4 A, C and row 5 A, B, C. I need to get the mode for the most frequently occurring text in the column which is from a drop down list
Solved by G. Q. in 29 mins
IF I HAVE A LIST OF NAMES IN A COLUMN AND SOME NAMES ARE OCCURRING AGAIN AND AGAIN. I WANT TO PUT A FORMULA IN OTHER COLUMN THAT WHICH WORD IS OCCURRING HOW MUCH TIME
Solved by E. W. in 23 mins

Leave a Comment

avatar