This article is not for beginners, but serves as a memory aid for conditional formatting driven by formulas.
- For the basic stuff, go to http://www.contextures.com/xlcondFormat01.html
- For some examples of using conditional formatting, go to Excel: Alternate Row Colors / Color Banding, Column Colors and Checker Patterns
- 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