Conditional Formatting Gradient

[phenley]

Hello,

I have a table with data in columns C and D. In Column E, I have the difference between columns C and D, and then F is the percentage difference. I would like to have a two color gradient to color the high percentages, but only if the difference in column F is greater than 1. This is what the table looks like.

[TABLE="width: 500"]
<tbody>[TR]
[TD]12/31/2016[/TD]
[TD]3/31/2017[/TD]
[TD]Difference[/TD]
[TD]Percentage Difference[/TD]
[/TR]
[TR]
[TD]174[/TD]
[TD]173[/TD]
[TD]1[/TD]
[TD]0.5747%[/TD]
[/TR]
[TR]
[TD]770[/TD]
[TD]765[/TD]
[TD]5[/TD]
[TD]0.6494%[/TD]
[/TR]
[TR]
[TD]684[/TD]
[TD]679[/TD]
[TD]5[/TD]
[TD]0.7310%[/TD]
[/TR]
[TR]
[TD]494[/TD]
[TD]491[/TD]
[TD]3[/TD]
[TD]0.6073%[/TD]
[/TR]
[TR]
[TD]240[/TD]
[TD]240[/TD]
[TD]0[/TD]
[TD]0.0000%[/TD]
[/TR]
</tbody>[/TABLE]

So, I would like the gradient to go across the cells in the percentage difference column only if the difference is greater than 1.

Is there a way to do this?

 

[inactiveuserps07]

@phenley, what are you considering "the high percentages"? Anything over .... what?

 

[phenley]

Well, 2.5% would be a high percentage in my case, however I wanted to know if I can do a two color gradient across the entire column given the difference was greater than 1.

 

[inactiveuserps07]

@phenley, do you mean across the entire row?

You really should limit the number of cells formatted to only the ones in which you actually have data, in any case.

And as for the condition itself, you want to format if Column E for that row is >1 (regardless of the value in Column F)?

 

[phenley]

@ErikTyler,

I'm not sure about you meaning across the entire row, but I want the conditional formatting in column F.

But yes, I want it to format column F if column E is greater than 1.

 

[inactiveuserps07]

@phenley ...

1. Select Column F (all)

2. From the Home tab, choose "Conditional Formatting" > "New Rule" > "Use a formula to determine which cells to format."

3. Enter this formula in the field below "Format values where this formula is true": =AND(ISNUMBER(E1),E1>1)

4. Click the "Format..." button.

5. Open the "Fill" tab.

6. Click the "Fill Effects..." button below the color chooser.

7. Click the radio button beside "Two colors" and select your two colors for the gradient, under "Color 1" and "Color 2."

8. Under "Shading Styles," choose an option (probably "Diagonal Up" or "Diagonal Down").

9. Click "OK" ... "OK" ... "OK" to accept and apply the CF rule.