Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
@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. –  8088 Mar 31 '11 at 12:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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