Find the Most Frequently Occurring Value in a List in Excel

Add to Favorites
Author:

Use a formula to return the most frequently occurring word or value from a range of cells in Excel.

Sections:

The Formula

More Information

Notes

The Formula

=INDEX(A1:A6,MODE(MATCH(A1:A6,A1:A6,0)))

Array Formula - this is an array formula and so you must enter it into the cell using Ctrl + Shift + Enter.

A1:A6 is the range from which you want to find the word or value that occurs most frequently. Change it in all three locations in the formula to fit your data.

097dde5c74bef49e84d53dd8492fbf46.png

Result:

2aa5351a564029e17c60182a88f74a17.png

To learn how this formula works, read the next section, otherwise, just copy/paste the above formula into your worksheet and change the cell references so everything works.

More Information

This formula is an array formula and those can be tricky.

To analyze the formula, we start from the inside.

Here is the formula once again:

=INDEX(A1:A6,MODE(MATCH(A1:A6,A1:A6,0)))

MATCH(A1:A6,A1:A6,0) this does a neat trick of returning the relative position of each word/cell value in the list. It looks for red and finds that in position 1, so it returns 1; then it looks for green and finds it at position 2, so it returns 2; and this pattern continues for the entire list. When a value is being searched for that has appeared higher up in the list, the position of the first occurrence only is what is returned. So, when it searches for "red" from cell A4, it returns the position of the first occurrence of that value, which is at cell A1, which is the first cell in the list.

Since only the first occurrence of the value can be returned, you will end up with duplicate numbers in the list and this is how the counting is done that finds the value that occurs most frequently.

MODE is the next function and that wraps around the previous one. This function returns the most frequently occurring number that was just returned by the MATCH function; this number corresponds with the cell that contains the most frequently occurring value.

Since each number is a position in the array or range of values, relative to the start of the range, we can then use the INDEX function to return that value and see what it is.

INDEX is used to return the value/word that is the most repeated; it does this by returning a value from the input array range; if 1 is given to this function, it returns the first cell in the range; 2 means the second cell; etc.

In the end, we use MATCH to get a listing of all values; we use MODE to find out which value occurs most frequently; we use INDEX to return that value.

Notes

This is a complex formula that uses a few tricks to get things done; it's also an array formula. That said, I recommend you to bookmark this tutorial and copy/paste the formula into your worksheet and edit the ranges to work with your data; that will make your life much easier.

This formula works if there is one word in a cell, multiple words, numbers, symbols, it doesn't matter.

Make sure to download the sample spreadsheet for this tutorial to get this formula in Excel.


Excel Function: INDEX(), MATCH(), MODE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...
Find the Most Frequently Occurring Number in a List
Tutorial: There are two basic functions that return the most frequently occurring number from a lis...
Count the Occurrences of the Largest or Smallest Value in a Range
Tutorial: How to count the occurrence of the largest or smallest value in a range; basically, how m...
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
Tutorial: In Excel you can store values in Defined Names.  Often people use a Defined Name to refe...
Tutorial Details
Excel Function: INDEX(), MATCH(), MODE()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course