Excel Tutorial: Conditional Formatting to Indicate Upcoming or Overdue Payments

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()]

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)

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.

Hector Garcia CPA and QuickBooks/Excel Trainer

Hector Garcia CPA and QuickBooks/Excel Trainer

Share on linkedin
Share on facebook
Share on twitter
Share on whatsapp
Share on email
Share on print

Use our link to get 30% off for a year, valid through 07/31/2022

Popular Courses

Private QuickBooks Training onsite

Customized Training

Accounting & Bookkeeping Services

Video-Based Virtual QuickBooks Course

Share

Share on linkedin
Share on facebook
Share on twitter
Share on whatsapp
Share on email
Share on print

30% Off QuickBooks

Use our link to get 30% off for a year, valid through 07/31/2022