Find the Most Frequently Occurring Value in a List in Excel
Use a formula to return the most frequently occurring word or value from a range of cells in Excel.
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.
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.
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:
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.
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.