Conditional formatting is an excellent tool to utilize in your excel spreadsheets to visually indicate when a due date is upcoming or overdue. You can easily add this formatting to your Excel spreadsheet by following the steps below.
1. Insert a cell with “Today’s Date:” and use the following formula so the date always stays current:
=today()
This is shown in the screen capture below in cell C2.
2. Highlight the cells you would like to conditionally format. Under the Home tab select the Conditional Formatting menu and then select New Rule. The selection is shown in the screen capture below.
3. A New Formatting Rule box will appear where you can select the conditions and the formatting of the cell. To conditionally format for dates that are overdue select the following:
- Under “Select a Rule Type:” select Format only cells that contain
-
Under “Format only cells with:” select
- Cell Value
- Less than or equal to
- $C$2 or the cell that indicates the current date [=today()]
- Cell Value
Click the Format button to bring up a menu to change the cell text, color, font and fill color. In this case we have selected the fill color to be red if the date is overdue.
These selections are shown in the screen capture below.
4. Next continue with the same due date cells highlighted and add an additional rule. This rule will highlight cells yellow if the due date is within 5 days. Similar to the previous selections, select the following:
- Under “Select a Rule Type:” select Format only cells that contain
-
Under “Format only cells with:” select
- Cell Value
- Less than or equal to
- =$C$2 + 5 (Days)
- Cell Value
Click the Format button to bring up a menu to change the cell text, color, font and fill color. In this case we have selected the fill color to be yellow if the date is within 5 days.
These selections are shown in the screen capture below.
5. Now that both conditional formatting rules are set the rules can be managed by selecting the following:
- Select Manage Rules from the Conditional Formatting Menu.
- The rules will be executed first by checking for overdue dates and then checking for dates due within 5 days. If the rules find an overdue date the conditional formatting will stop. To complete this check the box under “Stop if True”.
6. The due date cells are now formatted to indicate if they are overdue or due within 5 days. The cells formatting will change automatically with the current data and any changes of the due dates. The final product is shown in the screen capture below.
Conditional formatting can be utilized in other ways to indicate cells that are above below or equal to a certain value. Now that you’ve learned the basics play around with the different conditional formatting options and see how you can further customize your spreadsheet with conditional formatting.
You may download this sample spreadsheet used on this example. Additionally if you are in Miami or Ft. Lauderdale, you can come to our Live Excel Course.
