Here is how the MODE function in Excel works:
You can download the file here and follow along.
The MODE function in Excel “returns the most frequent occurring or repetitive value in an array or range of data”. If you start entering the MODE function, you will see three variations:
We will be focusing on MODE.SNGL. The MODE option is for 2007 or earlier, and MODE.MULT returns a vertical array, which will be for another time.
I’ve created a large data range with 210 numbers from 1 through 5. I want to find which is the most frequently occurring:
In cell N2 all I need to do is enter the following formula:
And Excel determines that the number “3” is the most frequent in the range. To confirm this, I will use the COUNTIF function to see how many of each number are in the range:
You can see that “3” has the most of any of the numbers.
And that’s the MODE function!