Have you started the New Year with some new good habits, maybe you are tracking your To Do or Task List in Excel (why wouldn’t you?). Here is a great way to highlight overdue tasks, using conditional formatting. We can conditionally format one cell based on another cells value, such as highlighting a Task in the Task Name column, if the number of days in the Days Overdue Column is over -6 days.
There are two parts to this procedure.
1. Write the formatting rule logic we want Excel to apply.
2. Select the type of formatting we want applied if our task in the Task column is overdue by over 7 days.
Let’s set the logic rule.
- Home Tab – Styles Group – Conditional Formatting – New Rule
- Select Use a formula to determine which cells to format
- In the Edit Rules Description box type the following formula
=$E3<-6
- Note that we have removed the row restriction from the formula by leaving the $ sign only on the column reference, so the formula is applied down the rows.
- Hit Ok.
Let’s set the formatting.
- Now we need to set the formatting, I have chosen the Red Fill option for simplicity.
- Hit Ok
- The last part is to set the cells we want the formatting to apply to, use the data selector to select the Task Name Column.
The formatting will be applied, and any tasks that are over 6 days overdue will be highlighted.
Job Done!.
Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
