You can use conditional formatting in Excel to highlight cells based on multiple criteria. For example, you might want to highlight cells that are:
- Due in the next 7 days
- Overdue
- Completed
To do this, you'll need to set up 3 separate conditional formatting rules. Here's how:
- Select the cells you want to format.
For this example, we'll select cells A2:E9.
- Create your first rule.
Click the Conditional Formatting button on the Home tab, then choose New Rule. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. In the "Format values where this formula is true:", enter the following formula:
=AND($D2="No",A2-TODAY()<7) - [Format cells]
Click the [Format...]. In the [Format Cells][Fill] [OK] - (Optional) Create your second rule.
Repeat steps 2-4, but use this formula instead: =AND($D2="No",A2-TODAY()>7) - (Optional) Create your third rule.
Repeat steps 2-4, but use this formula instead: =$D2="Yes"