conditiona formatting using formula

[lezawang]

Hi
I have a column called Average. I want to highlight any value which is >50 to green. I know I can use heiglight cells rules but I thought of creating my own rule. I am trying to use formula. so I clicked on New Rule--> Use a formula to determine which cells to format, then I typed this inside format box >50 then clicked on Format and changed the color. But I did not work. Any idea why? or how can I get that working

[TABLE="width: 500"]
<tbody>[TR]
[TD]Average[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]55[/TD]
[/TR]
[TR]
[TD]77[/TD]
[/TR]
[TR]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

When using the Formula option, you right the formula as it pertains to the first cell in your range selection (that you wish to apply the formatting to).
So if you have selected column B, for example, the first selected cell in that range is B1.
So your formula would be:

=B1 > 50

Excel will automatically adjust the formula for the other cells in your selected range.

 

[lezawang]

wow what a great hint. Thank you so much.

 

[Joe4]

You are welcome!

One trap be careful to not fall into.
I have often seen people highlight a whole column, but they do not want the Conditional Formatting to apply to row 1 (title/header row).
So they will write a formula like:

=B2 > 50

That won't work correctly!
You have to write the formula as it pertains to the very first cell in your selection, not necessarily the first cell you want it applied to.
If you want to exclude the first row, there are a few different ways to go about it:
- don't select the whole column, start with row 2 and select just the cells you want to apply it to
- select the whole column, write the formula as it pertains to row 1, and then afterwards, highlight the cell in row 1 and remove the Conditional Formatting from just that cell
- add another condition to your Conditional Formatting rule that looks like:
=AND(your formula,ROW() > 1)