Conditional Formatting (3 Arrows)

[audrey]

Hello,

I have 2010 and 2011 rankings listed next to eachother, I want to use the 3 arrow conditinal formatting to reflect

if ranking hasnt changed use yelow
if ranking vs last changed use green
if ranking went down use red

can someone help me on this ?

 

[Desu Nota from Columbus]

Three conditions would just be:<a1 set="" to="" down="" arrow<a1="" arrow
<a1 set="" to="" down="" arrow


for some reason it isn't letting me post a less than sign without screwing up the page...

=b1>a1 up

=b1 (less than) a1 down<a1 down


=b1=a1 sideways



Apply these three conditions to B1, copy/paste special > Formats to the rest of the column
</a1></a1></a1>

 

[Desu Nota from Columbus]

Which version of Excel are you using?

 

[Desu Nota from Columbus]

I just did a quick google of your question and found a how to.


I am on Excel 2003 so i haven't done this myself, but.....


1. Highlight the data in Column B
2. Click on Conditional Formatting
3. Select Icon Sets > 3 Arrows
4. Add the three rules from above (b1>a1, b1 (less than) a1, b1=a1)<a1, b1="a1)"
.
5. Should be working now</a1,>

 

[audrey]

I am using office 2007, but it didnt work for me

 

[Akashwani]

Hi Audrey,

Can you add another column?

Excel Workbook
	A	B	C
1	Top Rank	*	2010
2	1	0	1
3	2	0	2
4	3	1	9
5	4	-1	3
6	5	0	5
7	6	0	6
8	7	1	14
9	8	-1	7
10	9	1	16
11	10	1	15
12	11	1	23
13	12	1	17
14	13	-1	12
15	14	-1	10
16	15	1	60
17	16	1	22
18	17	-1	11
19	18	1	292
20	19	1	21
21	20	1	24
Sheet1

Copy the formula in B2 down.
Highlight column B.
Conditional Formatting-Icon Sets-Choose the Arrows-Ok

To hide the -1, 0, 1 go to Conditional Formatting - Manage Rules - Edit - check Show Icon Only - Ok - Apply - Ok.

I hope that works for you.

Ak

 

[audrey]

thanks Akashwani, it worked

 

[Akashwani]

hi Audrey,

Thanks for the feed back, I knew it would work, it came from MrExcel himself

http://www.youtube.com/user/bjele123#p/search/15/3YxIiyPAlkc

Ak

 

[Desu Nota from Columbus]

Sorry for the confusion, but I also learned something today.

Akashwani, is it possible to add this Conditional formatting without adding the column? For instance, the rule/condition you make is a formula that generates the +x, -x, or 0?

And is this type of conditional formatting only available on versions 2007+?

 

[Akashwani]

Hi Desa Nota,

As far as I know you have to have the extra column.

In versions of Excel pre 2007 you would need to use a Formula and a Wingdings font as well as Conditional Formatting

Check out the video....
http://www.youtube.com/user/bjele123#p/search/15/3YxIiyPAlkc


Ak