How to Use Conditional Formatting
To make conditional formatting easier, Excel supports pre-set options that cover commonly used situations, such as:
Dates Duplicate data Values above or below the average value in a range of cells
In the case of dates, the pre-set options simplify the process of checking your data for dates close to the current date such as yesterday, tomorrow, last week, or next month. If you want to check for dates that fall outside of the listed options, however, customize the conditional formatting by adding your own formula using one or more of Excel’s date functions. Excel applies conditional formatting in top-to-bottom order as they appear in the Conditional Formatting Rules Manager dialog box. Even though several rules may apply to some cells, the first rule that meets the condition is applied to the cells.
Enter the current date in cell C1. The cell changes to the default white background with black text since none of the conditional formatting rules apply.Enter the following formula in cell C2:=TODAY()-40This formula determines which date occurs 40 days before the current date. The cell is filled with the color you selected for the conditional formatting rule for dates more than 30 days past due.Enter the following formula in cell C3:=TODAY()-70This formula determines which date occurs 70 days before the current date. The cell is filled with the color you selected for the conditional formatting rule for dates more than 60 days past due.Enter the following formula in cell C4:=TODAY()-100This formula determines which date occurs 100 days before the current date. The cell color changes to the color you selected for the conditional formatting rule for dates more than 90 days past due.
