# colour top 3 cells in a column in different colours

#### Geosele

##### New Member
How can make top 3 cell value in an excel column have different colours e.g. RYB say?
I am not talking about colour scales, just for those 3 cells. Thx

are these just numbers in the column OR are you using any criteria
What version of excel ?? - as there are new functions in later version that will do this - BUT not work on older version

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

for 1st
=LARGE(\$A\$2:\$A\$30,1)=A2
for 2nd
=LARGE(\$A\$2:\$A\$30,2)=A2
for 3rd
=LARGE(\$A\$2:\$A\$30,3)=A2

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:A100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=LARGE(\$A\$2:\$A\$30,1)=A2

Format [Number, Font, Border, Fill] - Fill with a colour
choose the format you would like to apply when the condition is true
OK >> OK

repeat for the other 2 formulas - to get 1st,2nd,3rd MAX value

#### Attachments

• top3-condFrmt-ETAF.xlsx
9 KB · Views: 3
Last edited: