Conditional Formatting for Multiple Cells – Quick Memory Aid

This article is not for beginners, but serves as a memory aid for conditional formatting driven by formulas.

  • In the conditional format, the formula will work as long as it returns true or false
  • For example,
    • =A1<100
    • or even more complex =AND(A1<100,A1>20)
    • Not locking in the reference means the condition will shift the cell being evaluated accordingly
    • Locking the cell is possible – all the conditions will be based on the locked cell
    • Locking only column / row has similar effect – column / row will stay the same while the other will shift accordingly
  • Alternating rows, columns, and checker pattern:
  • =MOD(ROW(),2)=0,MOD(COLUMN(),2)=0)  <- selects the intersecting cell
    =MOD(ROW(),2)=1  <- selects odd row
    =MOD(COLUMN(),2)=1 <- selects odd columns



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>