Excel tutorials

In the last post we saw how we could find the most frequently occurring number. Here, we are going to find the most frequently occurring word.

This is a little bit more tricky. There is no in-built function for this. We are going to use a combination of formulas.

Below we have a list in column C – in E4 we have the most occurring word.

First we can use the MATCH function, this will give us the location of each fruit. Enter the following formula:

=MATCH(C3:C7;C3:C7;0)

Here we can see that number 3 occur 2 times – which represent “Apple”.

Next we can use MODE to get the most frequent number (refer to last tutorial).

Enter the following formula:

=MODE(MATCH(C3:C7;C3:C7;0))

We are expecting 3 here – as number 3 occur 2 times above.

The thing that remains is to get the actual value – this we can do with INDEX.

Enter the below in E4:

=INDEX(C3:C7;MODE(MATCH(C3:C7;C3:C7;0)))

This can now be read as =INDEX(C3:C7;3)