Excel - conditional formatting

[sathyan]

I have a table of data in Excel 2007. Column labels are months going sequentially left to right (Jan, Feb, Mar, etc); Rows labels are product names; the data cells are units of sales (number format). I want to use the Conditional Formatting Icon Sets (colored arrows), to compare sales to the previous month within a row (product name).

I see how to set the conditions to compare to a specified number but I need to compare to a reference. What I want is basically this:
B2 > C2: down arrow
B2 = C2: straight arrow
B2 < C2: up arrow

Any idea how to do this with Conditional Formatting Icon Sets

 

[Cogman]

=IF(A1 < A2; "&#8593;"; IF(A1 = A2; "&#8594;"; "&#8595;"))

This is OO code, but it should be similar to excel (if not identical)

 

[JJChicken]

Select on cell B2.

Go Conditional Formatting > New Rule > Format all cells based on their values

Set Format Style to "Icon Sets". Change Icon Style to "3 Arrows (Colored)". Tick "Reverse Icon Order".

For the 1st value field, enter in > $C$2 (or by nagivating to C2).
For the second valued field, enter in >= $C$2.

That should do the trick. Worse come to worse, if this is really important you can send me the spreadsheet and I will try to help you out.

 

[Oyster]

Can you guys please share your solution? Thanks.

 

[JJChicken]

Can you guys please share your solution? Thanks.

Can you elaborate what you mean by share? Would you like a copy of the spreadsheet/screenshots of how to do it?

 

[Cogman]

Can you elaborate what you mean by share? Would you like a copy of the spreadsheet/screenshots of how to do it?

aye, I was pretty sure I did share!

 

[Oyster]

Can you elaborate what you mean by share? Would you like a copy of the spreadsheet/screenshots of how to do it?

I mean share the solution here for other people's perusal. Screenshots would be nice - although a textual description is more preferable, since it can stay within this thread forever.

Thanks.

 

[Cogman]

I mean share the solution here for other people's perusal. Screenshots would be nice - although a textual description is more preferable, since it can stay within this thread forever.

Thanks.

Again, pretty sure both Barrak and I have shared fairly good textual descriptions. I don't know what more you want.

 

[Oyster]

My bad, guys. I guess I was stuck on this:

Worse come to worse, if this is really important you can send me the spreadsheet and I will try to help you out.

I should have clarified - I was suggesting that if something else came out of this (i.e. if spreadsheets were exchanged), then sharing that additional piece of info would be nice.

 

[sathyan]

Thanks for your suggestions. I was unable to get it working that way but did find an alternative method:
1. I inserted a column between Jan.(B) and Feb. (was C, now D)
2. Defined the new column C as D2/B2. Filled down.
3. Added conditional formatting to column C (Icon Sets > 3 Arrows> Numbers: Up arrow for > 1.0001; Straight for >= 1; Down for the else condition)
4. Set the column width for C such that only the arrows were showing.

 

[JJChicken]

Thanks for your suggestions. I was unable to get it working that way but did find an alternative method:
1. I inserted a column between Jan.(B) and Feb. (was C, now D)
2. Defined the new column C as D2/B2. Filled down.
3. Added conditional formatting to column C (Icon Sets > 3 Arrows> Numbers: Up arrow for > 1.0001; Straight for >= 1; Down for the else condition)
4. Set the column width for C such that only the arrows were showing.

There's an option to show icon sets only. Google it, if you can't find it.

 

[GaryJohnson]

=IF(A1 < A2; "&#8593;"; IF(A1 = A2; "&#8594;"; "&#8595;"))

This is OO code, but it should be similar to excel (if not identical)

For excel, if anyone is wondering, you just have to change the semicolons to commas:

=IF(A1 < A2, "&#8593;", IF(A1 = A2, "&#8594;", "&#8595;"))

If you want the fancy icons then you could use:

=IF(A1 < A2, 1, IF(A1 = A2, 0, -1))

And use conditional formatting with icon style "3 arrows colored" & "show icon only" selected:
up arrow when value is >= 1 number
right arrow when value is >= 0 number
down arrow when < 0