Format / Highlight top 3 values at row level

kr3geyn

New Member
Joined
Jul 10, 2013
Messages
5
Hi,

I designed a pivot table and have the datas in percentage. So for each row, I have to highlight top 3 values (highest by individual row level) in different colors. Can someone help me with it?

SUBJECT
MARK1
MARK2
MARK3
MARK4
SUB1
86
67
12
76
SUB2
45
56
35
97
SUB3
49
34
13
94
SUB4
46
78
73
95
SUB5
94
39
23
94

<tbody>
</tbody>

Take for example this sample table. For sub 1, the highest values are MARK1, MARK4 and MARK2

I want only these cells to be highlighted. Similarly for SUB2, SUB3 and so on.

I will be very much happy if it can be done in different colors
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
Assuming your table starts in A1 and the first value is in b2....

highlight the table of values then create a new conditional formatting rule with

=B2>=LARGE($B2:$E2,3)

Essentially this will highlight the 3 largest values in the row.
you could do create the same formula for each row individually if you wanted different colors per row
but if you want different colors in each row you'd need to create seperate formulas for each color....

kind of time intensive I think unless someone has another suggestion maybe VBA.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
What are the highest values for SUB5 and SUB6?

SUBJECT
MARK1
MARK2
MARK3
MARK4
SUB5
94
39
23
94
SUB6
23
23
50
70

<tbody>

</tbody>

Markmzz
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top