Using relative references in conditional formatting

Question

I am a teacher.  I need all scores below 50% of the total score highlighted.  Different columns have different total scores.  I renamed each cell containing the total score: Quiz1, Quiz2, Quiz3 etc.  But how do you use the formula in conditional formatting to achieve this.  For example, I used the formula =$E$4<(Quiz1/50).  The formula works.  Only when I drag it downwards so that the remaining cells in the column would have the same formatting, the other cells still refers to E4, which will create an error, because each cell has a different score.  How do I automate this?  Is there something else that I need to do.

I cannot imagine formatting each and every cell.  That would be a waste of time.

Answer 1

Select the range you wish to conditionally format, for instance E4:E100, then enter a formula in conditional formatting that does not have the absolute modifiers (the $). Like =E4<(Quiz1/50).