Help with Conditional Formatting (Increase or decrease in next cell)

**iNoob** · 01-25-2012, 12:49 PM

Hi Everyone,

Here is an example of the data I'm working with:

Sales

Company $1 $2 $3 $1 $2
Company $3 $5 $3 $6 $1
Etc.

So what I want to do is format the data so that if sales increases from the prior year then the cell is filled green and if sales declined, then the cell is filled red.

I've been trying to get this to work with conditional formatting tab (using formulas like: A1<B1) but I don't think I'm doing it correctly. If anyone can help me out, it would be greatly appreciated.

Please note that sometimes more years/company will be added therefore I need to learn how to do this.

Really appreciate it this guys.

Thanks,

**Fidd$** · 01-25-2012, 01:06 PM

Hi there,

Please see attached workbook and look at the conditional formatting rules.

hope this helps

**iNoob** · 01-25-2012, 01:17 PM

Thanks it works! Was wondering if there is another way to do this as If i have many companies, this method might take awhile.

Really appreciate it Fidd$>

**Fidd$** · 01-25-2012, 01:26 PM

Ace, glad it works!

You can easily copy the formatting to the range of cells you want the formatting for.
I think the reason why yours didn't work was maybe because you had anchors '$' in the range of cells. This anchor basically means that if you copy the formula/formatting, it will not change when you paste it onto another range.

For instance, if you had

=IF($B$2>$A$2,TRUE,FALSE)

as your formula for the conditional formatting, no matter where you copy the formatting, the formula will still test if B2 is greater than A2. By not using the anchors, Excel is (and should be!) smart enough to know where you have pasted the formatting and amend the formulas automatically. Give it a try yourself by adding in anchors and removing them and then see the difference

hope this helps