Conditional Formatting to highlight cells with highest frequency

Question

I have an Excel table like the following:

1  2  3  4  5  6  7  8
----------------------
3  3  3  3  3  1  1  1
4  4  5  4  4  4  4  3

and I need a conditional style to highlight the items with the highest frequency in each row.

For example, the cells with an * would be highlighted:

1  2  3  4  5  6  7  8
----------------------
3* 3* 3* 3* 3* 1  1  1
4* 4* 5  4* 4* 4* 4* 3

I know that there are formatting rules for highest and lowest values or percentiles, but that's obviously not what I need.

Any ideas?

Here's part of my spreadsheet:

Answer 1

You can create a conditional formatting rule that uses a formula. Select the top left cell of your data (in your example this is B4). Choose Conditional Formatting on the ribbon then New Rule:

You then need to choose the Use a formula to determine which cells to format option:

Input the following formula:

=B4=MODE($B4:$I4)

This uses the MODE function of Excel to calculate the modal value between B4 and I4 then if B4 equals that value, apply some formatting. You can use the Format button to define the formatting you wish to be applied to the cell whenever its value happens to be the most frequently occurring on that row.

Finally back on the spreadsheet you can right-click-drag B4 across to I4 and select Fill Formatting Only. This applies the conditional formatting to the entire row of data. Then drag it downwards to fill your entire table with this conditional formatting.