3

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: enter image description here

3

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:

Conditional Formatting on the ribbon

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

Choosing to input a formula for conditional formatting

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.

Filling the formatting across multiple cells

  • @slhck I edited my answer slightly to fit your revised question a bit better. Does that make the approach clearer? Happy to make further edits if needed. – Gaff Mar 31 '11 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.