Using Conditional Formatting Data Set Arrows - Indicate Decrease as Good

[cradmac]

Hi All,

I'm having trouble with my conditional formatting data set icons. I want to use the Icon Sets to indicate that a decrease in value is good. However I want to use the arrow icon set (see pic below). Is there a way to make the green arrow point downward and the red arrow point upward. Your help would be much appreciated. Thanks

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

WHen you are in the Icon Sets menu, click on "More Rules" at the bottom.

You should be able to set up a rule with downwards green arrows in there.

Regards,

 

[cradmac]

Unforunately, I did click on More Rules but they don't have any pre-set icon sets that include a green downward arrow.

I'm using Excel 2010.

 

[Tyler Hilton]

Insert the letters p or q into a cell in an adjacent column and then change the font to wingdings3. You could then use conditional formatting on this to color the appropriate symbol green. Other than that, I don't know if there is a way to point the green arrow down within the conditional formatting window. There are gray arrows or colored circles that you could potentially use.

Best,

Tyler Hilton

 

[Tyler Hilton]

Also,

Another option would be to use custom formatting on the cell. In a separate cell, click insert a symbol and find the up and down arrows within the SimSun font family under geometric shapes grouping. Insert these into the cell. You can then copy and paste these symbols into your custom formatting.

Best,

Tyler Hilton

 

[mole999]

Unforunately, I did click on More Rules but they don't have any pre-set icon sets that include a green downward arrow.

I'm using Excel 2010.

You have to select your options
Format all cells based on theoir values
Format style Icon Sets

and at the bottom
Icon Style

adjust the values to suit your needs

 

[RickXL]

Unforunately, I did click on More Rules but they don't have any pre-set icon sets that include a green downward arrow.

I'm using Excel 2010.

Apologies, looking again, I don't think that even using More Rules you can invert the arrows - even in 2013.

However, plan B ...

Add an IF statement similar to the following that includes your condition.
=IF(AY5<=AZ5,CHAR(233),CHAR(234))

My condition was AY5<=AZ5.
<az5.
CHAR(233) is an up arrow in Wingdings and CHAR(234) is a down arrow in Wingdings.

Use conditional formatting to set the font colour according to your desired rules. You will need two conditions: one to set green font and one to set red font. I used:
=AY5>AZ5
for green and
=AY5<=AZ5
for red.

Set the font of all the arrow cells to Wingdings.

Regards,</az5.