Question Locked

Excel 2003 - Selecting a greater number from a list of numbers

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
I was wondering how this can be done
Starting from Column A Row 1 say I have a list of numbers 1,3,9,7. How can i highlight that Column A Row 3 is the correct answer. I want this highligted in any bold colour say Red for example.
Use the formula =max(A1:A4) only returns the value which 9 but i want the cell itself to be highlighted.
One way could be conditional formatting but I don't know which formula to use.
Any help would be much appreciated.
Thanks

2 replies

by mdhealy

First in the Conditional Formatting dialog box, where it says "Cell Value Is" click the arrow and pick "Formula Is."

Now, suppose the range is F13:F36 then the formula to format F13 would be:

F13 = MAX($F$13:$F$36)

Then click the Format button and pick whatever you like.

Click OK.

Copy cell F13, select the rest of the range, do Paste Special/Formats.

Note the $ signs above, they are important, the first F13 has no $ signs so it will be a relative reference to each cell's OWN value but the range has $ signs to make it absolute.

Note you can do similar things with MIN, LARGE, SMALL, etc.

I often have two conditions, one with MAX and one with MIN so the biggest value is highlighted in red and the smallest value is highlighted in blue.

Also note sometimes Excel gets confused and puts quotes around formulas in the conditional formatting box, so you may need to re-open it and edit out the quotes once or twice.

This discussion is closed to new replies.