Conditional Formatting with Formula Rules
There are several pre-defined options for highlighting ranges, Greater than, Less Than, Between… however if we want to define more than one criteria for highlighting cells, then you may want to define a Formatting Rule.
For example you may have a spreadsheet of students which you would like to highlight who have not submitted their assignments yet (X in Column E) and also whether the due date has passed(Due date in column E is less than < today); this is a task that will require the comparison of two columns and can be accomplished using a Formatting Rule using a Formula.
To create a new formatting rule:
- Highlight the range of cells you would like to apply your conditional formatting to.
- Click on the home tab
- Under style, click conditional formatting then click on New Rule.
- A list of options will appear click “Use a formula to determine which cells to format”
- A valid formula for the scenario described above would be
About the formula
There are three parts to this statement:$D2 < NOW(), $E2<>”X”, AND()
- $D2 < NOW()
The first part of the statement $D2 < NOW() checks if the date in column D is before today, NOW() returns today’s date, so the first part reads if the date in column D is before today, then return True.
The $ in front of the columns means that for whichever cell you are running the formula for, the comparison must be made in column D. There is no $ in front of the 2 because the formula will need to iterate over the rows and the row index will need to change.
The second part of the formula checks whether the value in column E is not equal to “X”, <> is used in excel as a not equal to operator.
- AND() compares two or more criteria, all of the criteria must be TRUE for the AND function to return TRUE. For exampleThe standard syntax for an AND() statement is the below:
After our two criteria are wrapped in this logical function we get the original formula:
If the criteria had of required only one condition to be true we could have used the OR() conditional.
This uses the same syntax as AND:
For example if we wanted to highlight the students that had an assignment due date after the current day OR have already submitted their project, the following then OR() could have been used:
=OR($D2>NOW(), $E2=”X” )
- After the formula is set click Format and add fill effects or change the formatting to make the formula data stand out.
- Click apply and your formula should take effect.