How to apply Conditional Formatting with only one icon showing next to the highest value in a range of cells?

0
votes

I am using Excel 2016 and I need to apply a conditional formatting that will highlight (with a Green circle) the cell with the highest value from a a range of cells. Say, I have values in Cell A1, B1 and C1 as shown below:

         A    B    C
 Row 1   50   60   55

I need a conditional formatting that would put a green circle next to the value in the cell B1 since it is the highest value in the range of cells A1:C1

How can achieve this (without using VBA)?

1
excelconditional-formattingexcel-2016
The easiest way would be if you're okay with the circle being above or below the largest value instead of on it, because then you wouldn't need conditional formatting either. =IF(A1=MAX($A$1:$C$1),"🔴","")‎ - ashleedawg
It's the first time I see a circle in a formula!. Also, Excel does not accept it as a formula. Can you elaborate on your solution? - user3115933

1 Answers

1
votes

You can create a new custom conditional formatting from the Home menu, by selecting your range (ensure that the active cell--the one lighter-coloured-- is B2), clicking on 'Conditional Formatting' menu and clicking on "New Rule...".

Select "Icon Sets", set the yellow and orange icons to "No Cell Icon" and enter the formula as depicted below:

enter image description here

Result:

enter image description here

The formula is:

=max($B$2:$D$2)

If you have many rows and you need to add the icon to one item in each row, then after selecting the whole table, you will have to use this formula:

=max($B2:$D2)

(which will adapt the row number to the conditional formatting)