Conditional Formatting with Arrow Icon Sets

Question

Hello,

Can someone help me figure out the correct parameters for Conditional Formatting Arrows.  I need the arrows to point UP if the difference between to data points is > +0.5%,  and DOWN, if the delta is <-0.5%.  If the value difference is between the two (>=-0.5% and <=+0.5%) I want the arrow to be STRAIGHT.

I'm having a hard figuring out how to EDIT the RULE.  The default percentages are 67% and 33%.

I pasted a picture below.  If you can see it, I need C2 to have a DOWN arrow since the delta is below 0.5%, DOWN in C3 since the delta is below 0.5%,

UP in C4 since the delta is above 0.5%, DOWN in C5 since the delta is below a 0.5% change and STRAIGHT in C6 since the delta is less than 0.5%.

Current	Previous	Difference
98.4%	99.1%	-0.7%
96.5%	97.8%	-1.3%
91.2%	89.2%	2.0%
90.0%	92.5%	-2.5%
94.3%	94.4%	-0.1%

How can I adjust the parameters from 67% and 33%?

Thanks!

Answer 1

Hi,

Setting up the arrow icon based on the percentage you preferred can do by editing the rule that you’ve created. To do this, follow these steps:

1. High-light the Difference and select Conditional Formatting under Home tab in Styles section at the top.
2. Go to Edit Rule tab and under Rule Type, choose Format all cells based on their values.
3. Choose Icon Sets for Format Style and choose the down, straight and up arrow for the Icon Style.
4. Under Type, choose Number for both type boxes and under Value, type 0.005 for the first box and -0.005 for the second box.
5. Click OK and select Apply and then OK. Here’s the screenshot below:

 

Let us know if the solution above works for you.

Answer 2

Thank you very much, it worked fine.  I just had to adjust the numbers to 0.0055 and -0.0055.

Can I reformat the arrows to appear in different colors?

Answer 3

Changing the color of an icon style in worksheet will make it more presentable and personalize. It can be a great idea of innovation since it’s unavailable yet as an Excel feature. Regarding this, we highly suggest that you submit this as a suggestion on our Excel UserVoice feedback platform. This way you can help our engineer and developers to improve our product and have better visibility on what features are most important to you.

Should you have any other concerns, don’t hesitate to let us know.