Color scale conditional formatting for mix of text and number in Excel

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:

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 Answer

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.

You should end up with something like this:

(note the value of cell A1)