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.
Here we are discussing one of important application of conditional formatting for tracking due dates
Here we have scenario for tracking the due dates of payment of the customer based on real time and we need to add some scenarios for tracking the due dates
Step-1
Add the day's option for the calculating by using data validation option.
Step-2
We also need to update the status of payment done or not by adding the data validation option in the cell range
Step-3
Select the range where we need to highlight the data, and click on the home tab and select to conditional formatting, select the new rule and select the option “Format only cell that containsâ€,
Select the Cell Value and select the criteria less than equal to “Today()+cell where we put days “
And click ok.
Step-4
Now we need to remove highlight option based on the payment status for this option again we need to apply conditional formatting by selecting the range and click on the conditional formatting and click on new rule and select “Use a formula to determine which cells to format†and here we need to write formula “E7=†Done†and click on no format option and click OK
And we need to click on the conditional formatting option and click on manage rule and click check mark on the option “Stop If True†against the rule for the statement if the rule is true no need to format the cell.
Click here for downloading the workbook file
Video Link: Watch Video