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%.

  • 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


Setup a conditional format with the following formula:


And apply the following custom number format:


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)

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.