2

I would like to use the color scale conditional format on a column in Excel. I had to change some of the numbers to text because I wanted to distinguish between when the value was <1% and actually 0%. But when I did that, it messed up the color scale: enter image description here

You can see that when there is text that reads "<1%" it removes the color formatting. How do I make it keep the formatting for those? I'd like it to be the same color as 0%.

2
  • 1
    Setup a second condition for when the cell's value is ="<1%" Dec 16, 2019 at 18:09
  • @cybernetic.nomad that will not integrate in the color scale, though.
    – teylyn
    Dec 16, 2019 at 19:49

1 Answer 1

2

Setup a conditional format with the following formula:

=AND(A1>0,A1<0.01)

And apply the following custom number format:

<1%

Then apply the second condition with the gradient.

enter image description here

You should end up with something like this:

enter image description here

(note the value of cell A1)

2
  • I did not realize you could create multiple conditional formats! Thank you!! Dec 17, 2019 at 12:56
  • If that answer works for you, then you should accept it. That way everyone will know your question has been asnwered. Dec 18, 2019 at 15:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .