I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color.

The problem I'm having has to do with grades of Zero. I have this formula for the other grades, with 90% being the passing mark, so anything above that isn't marked

=and($E:$E<90%, $E:$E>0%)

The problem is that apparently the value of an empty cell is considered to be 0 by the spreadsheet. If I did a conditional format with the following:


the spreadsheet lights up.


You can use isblank() to format blank cells the way you want to. For example

=or(and($E:$E<90%, $E:$E>0%), isblank($E:$E))


=or(and($E:$E<90%, $E:$E>0%), not(isblank($E:$E)))

By the way, you can simply enter $E1 here instead of $E:$E - the conditional formatting formula can be written as it applies to the upper left corner of the range.

  • Here's a link to what the table will look like: Example Table - as you can see, one of the problems is that the Grades column will contain four different classes of values. There will be some blank cells, some that contain o% that I want to highlight that row in red, from 1%-89% that will be highlighted yellow, and then 90% and above that won't have highlighting. – draconix Apr 1 '16 at 23:13
  • 1
    The root cause here is poor data organization. The comments should have been placed in one cell (line breaks can be created with Ctrl-Enter) instead of spanning across several rows. – user79865 Apr 1 '16 at 23:17

Finally I got it to work. Here's the formula I used:


The organization of the cells is because there is another layer to the data that I forgot to include in the example. Each grade is composed of different sections that are graded separately. For example

Creative Writing - Pass
Grammar - Fail

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.