Excel's Conditional Formatting feature is incredibly powerful. It allows you to apply formatting to cells or ranges of cells based on criteria that you can choose. You can even use the value of other cells in the worksheet as part of your conditional formatting criteria.

This lesson uses a Project Checklist example to demonstrate how to use Conditional Formatting. The lesson is in two parts.

• First, it shows you how to apply a format to a cell containing a date where that date is in the past - this allows us to see which of the tasks in our project are overdue.
• Second, it extends the criteria for the formatting rule so that only those tasks where the due date is in the past AND the task is still open will be highlighted - at a glance, we can tell which tasks are overdue and need attention.

Note that Excel's Conditional Formatting feature includes an option to highlight dates according to a range of pre-configured conditional formatting rules. These are simple and easy to apply but the problem for our scenario is that there isn't a built-in conditional formatting rule that applies to all dates in the past - the best you can do with the built-in rules is to format cells containing dates that are in the last month. So in this lesson, we'll look at how to apply the formatting to cells containing all dates in the past, not just those in the last month.

Applying Conditional Formatting to Dates

Conditional Formatting in Excel allows you to format one or more cells based on the values in those cells. Excel offers a set of standard conditional formatting options. You can also use custom formulas to decide whether to apply a specific formatting rule to a range of cells. Using a formula as part of your conditional formatting rule is incredibly powerful, since you can incorporate cells from anywhere in your spreadsheet as part of the formula in your conditional formatting rule.

Let's start by looking at the spreadsheet that we're going to work with. This is a simple Project Checklist spreadsheet that contains the tasks that need to be completed. Each task has a status and a due date.

Format our cells by comparing the Due Date to the current date

In this section, we'll look at how to create a conditional formatting rule that formats the cells in the Due Date column if the task was due on a date that is in the past.

• First, select the cells you want to format:
  
• Next, click the Conditional Formatting button on the toolbar:
  
• Choose the Highlight Cells Rules option, and then select Less Than ....
  
  You may also notice another option in the list of Highlight Cells Rules, A Date Occurring. This is sometimes a useful option (e.g. highilight dates in the last 7 days), but it doesn't work in this scenario because it doesn't give us an option to choose all past dates.
• You'll now see a dialog box like the one below in which Excel has entered a date as the LESS THAN rule (note that Excel has entered a date automatically:
  
• At the same time, you may notice the selected cells have been formatted as a preview of the current rule, as shown here:
  
• We're nearly there. As you can see, this conditional formatting rule will format all cells (i.e. all the cells we have selected) and format any cells that match our criteria with red text and light red shading. However, our rule is not quite complete - we haven't finished configuring our criteria. Remember that we want only want this rule to format cells that are in the past, i.e. before today. To do this, we'll replace the date shown with the TODAY() function which, conveniently, returns today's date.
• Here's how your completed rule should look once you enter the TODAY() function. Note that you must put the = sign at the beginning of the function so that Excel knows you're entering a formula rather than just a text value:
  
• Once you've done this, you can click OK and the rule will be saved and applied as shown here:
  

We've now completed the first part of this lesson - formatting a range of the dates in our spreadsheet so that cells containing Due Dates that are in the past will be formatted in red, as shown above. The use of the TODAY() function means that the spreadsheet will update automatically each day when you open or edit the spreadsheet.

Format our cells based on the Due Date AND the task Status

In this section, we're going to extend our conditional formatting rule so that cells with past dates will only be formatted in red if the related task is still outstanding.

To do this, we need to edit the rule we created in the previous section (rather than creating a new rule). To do this, follow these steps:

• Select the cells you want to format (i.e. the cells we formatted in the previous section)
• Click the Conditional Formatting button and choose Manage Rules. The Conditional Formatting Rules Manager will appear:
  
• Select the rule that needs to be edited, then click the Edit Rule ... button. As you'll see from the picture below, this looks like a version of the rule we created earlier, albeit with more information:
  
• At this point, we need to change our formatting rule to incorporate both of our criteria. Unfortunately, the Less Than rule we chose earlier will not longer work for us since it can't handle multiple criteria. Instead, we'll change the Rule Type to Use a formula to determine which cells to format. When you do this, the rule description will change, as shown here:
  
• You may also notice that I've already entered the formula we need:
  • =AND(D5<>"Done",C5<TODAY()
• Here's how this formula works:
  • The AND function looks at the two arguments inside the brackets and checks that both of them are true. If they are, then the AND function returns TRUE, the criteria for the rule are met and the formatting will be applied. If one or both of them are not true, then AND returns FALSE instead and the formatting is not applied. You can learn more about the AND function and its companion, the OR function, in our lesson Using logical operators and functions in Excel.
  • The first argument in the AND function is D5<>"Done". This checks the Status value in column D to see if it is set to Done. If it isn't, this argument is TRUE. Note that this part of the formula uses relative references. When evaluating the status in row 5 (the first row in our table), Excel will look in D5. When evaluating row 6, Excel will look in D6, and so on. It's worth noting that I could have written the formula as D5="Open", to check if the status was set to Open. However, my projects usually have a number of status values that can be applied while a task is being worked on (Open, Pending, Waiting, Deferred) but only one status when it is done.
  • The second argument in the AND function is C5<TODAY(). This is an alternative version of the rule we created in the first section - it simply looks at the value in the Due Date column to see if it is less than today's date. If it is, this argument returns TRUE.
• Once you have completed writing your formula, you can click OK to return to the Rules Manager, which now looks like this:
  
• Finally, you can click OK to see the results of this formula on the cells we selected. As you can see, the only row where our red formatting has been applied is row 7, where the Due Date is in the past, and the Status is Open rather than Done:
  

Conditional Formatting Lesson Summary

​In this lesson we looked at how to create a conditional formatting rule that formats a cell containing a date if that date was prior today. We also looked at how to extend our conditional formatting rule to include criteria based on cells other than the cell that is to be formatted. In this example we only wanted to apply the formatting if the Status column was not set to Done, indicating that the task was overdue.

Conditional Formatting is a very powerful feature of Excel which can be used in a number of different ways to format cells and data automatically based on both simple and complex criteria. For another example of conditional formatting in action, check out our lesson Shade alternate rows in an Excel spreadsheet on a Mac. Even if you don't use Excel on a Mac, you'll recognise the same elements of Conditional Formatting rules, and will be able to apply the same concepts in Excel for Windows. It's also worth noting that Google Spreadsheets also support a Conditional Formatting feature; the formula I used in this example can be copied and pasted into Google's Conditional Formatting rules to achieve the same result.