Can I Create a Color Gradient Between Two Values in an Excel Pivot Table?

Question

I need to conditionally format all values between 2 numbers in a pivot table using a color gradient to accentuate the scale of values between that range.

I'd like to not have any color for the rest of the cells that do not fall between these particular values.

I tried doing this using the formatting from the above picture but as you can see values outside of those indicated are still being highlighted (.48 is highlighted even though the maximum is supposed to be -.05).

I also tried using formulas in the three-color gradient to use different colors to highlight each range I want to differentiate. But an error comes up saying "You cannot use relative references in Conditional Formatting criteria for color scales, data bars and icon sets".

Any ideas? Thanks.

Excellor · Accepted Answer · 2025-08-21 13:34:37Z

1

Adding 2 rules for conditional formatting before your 'main' conditional formatting, I got this result.

Cell Value < lower limit - no formatting;

Cell Value > upper limit - no formatting;

Make sure to select 'stop if true' on these first 2 rules.

answered Aug 21 at 13:34

Excellor

9891 gold badge3 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kxmoosekx Aug 21 at 14:07

Thanks but that didn't work. When I do that it does color the selected range but it is not a gradient. I'm guessing because the column I'm using is a lot more than 17 rows so those 31 values I'm looking for are all in the same 'gradient zone'? I need the formatting to apply only to those values between the two values, not the whole range of the column. This is a refreshable sheet also so I can't just highlight a smaller set of cells.

Excellor Aug 22 at 7:11

I suppose you still need the non-coloured data? Otherwise, you could apply a filter and show the data somewhere else.

kxmoosekx Aug 25 at 16:25

I am going to make three distinct gradient zones based on % ranges (ex. 0-10, 10-20, 20-30), the smallest being red gradient, the second being yellow gradient, the third being green gradient. But all information would have to be included, yes. Thanks.

kxmoosekx Aug 21 at 14:08

file:///C:/Users/greeleym/OneDrive%20-%20Legrand%20France/Desktop/Screenshot%202025-08-21%20100053.png

Excellor Aug 21 at 14:39

You can't upload screenshots to comments, if you think it's important for future readers to understand your problem, you can edit it into your original question. I'm sorry the solution didn't work, I'll try to see if I can come up with something else. Are you okay with adding a helper column or even VBA?

kxmoosekx Aug 21 at 14:46

I know I tried to chuck that photo into an additional reply as it was not allowing me to post in the comment.... No problem I appreciate you! I'd accept a helper column and definitely VBA. Anything that gets to the answer. I would eventually like to get to the point where I can have different colored gradients correspond to different range numbers...For example: 1-100: red gradient 100-200: yellow gradient 200-300: green gradient But that might be a different question. Thanks.

Collectives™ on Stack Overflow

Can I Create a Color Gradient Between Two Values in an Excel Pivot Table?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related