Remove Conditional Formating for Zeros

stuart_bradford

I am using conditional formatting to highlight the scores received in a survey, with scores ranging from 1-5, so I have set a 3 color scale with 0, 2.5 and 5 as the min, mid and max points. However when there has been no response the table shows red, I would like no response to be white

stuart_bradford

If you base the conditional formatting of the response scores on a new line item you have more flexibility. Use this flexibility to make any no responses equal the mid point of the 3 colour scale so that it appears white, see example below

stuart_bradford

Then change the conditional formatting to be based on the score format line item – see how the no response line for R2 is now white