Excel Tip: Using Conditional Formatting to Warn on Deadlines

Suppose that you manage a set of suppliers’ agreements which start and expire on specific dates. You need to be warned on the expiry of any agreement, say, 10 days ahead of the deadline. How can you use Microsoft Excel to do that?

Fortunately, the latest versions of Excel (2007 & 2010) allow for what’s called Conditional Formatting. This feature gives you the ability to change formatting of cells and ranges based on criteria you set. Let’s see how we can manage our agreements’ deadlines with this feature.

A scenario of three agreements having different Expiry dates, 101 (expires in less than 10 days), 102 (expires in nearly 3 months), and 103 (already expired). Notice that the current date is March 8th, 2012.

We need to use Conditional Formatting to highlight the expiry date in green background whenever the agreement is expired or expires in less than 10 days as of the current date.

From Home tab, Styles, Conditional Formatting select Manage Rules.

Hit the New Rule button.

From Select a Rule Type, select the “Use a formula to determine…” option. Then write the formula shown in the figure above. (D3 is the reference cell for the Expiry Date of the first agreement. Make sure you remove the dollar sign ($) from $D$3 using the F4 button).

Select the formatting into which the approached Expiry Date cell will turn into once the condition is met.

Hit OK buttons. The first agreement’s expiry date is highlighted in this example as it expires in less than 10 days.

You need to apply the formatting to other agreements by dragging the first cell then selecting the Fill Formatting Only option from the Paste Tip.

You’re done. Agreement 102 is not formatted as its Expiry Date is still far, whereas 103 is already expired and must be highlighted.

Test your work by changing 103’s expiry date to 25-Apr-2012, it turns un-formatted.

With this dashboard, your life is much easier now, I believe, in managing agreements’ renewal deadlines. Every time you open the workbook, Excel will give you the status of your agreements with warning lights on the ones approaching expiry.

Author: Mohammed Barakat

Mohammed Barakat is an Excellence Enabler, a Data Scientist, and a Trainer who holds several certifications. He is a Consultant Engineer in Industrial Engineering (JCE), a Project and Risk Management Professional (PMP, PMI-RMP), and a Six Sigma Black Belt (CSSBB). He is a proficient trainer accredited by Microsoft (MCT) and is also a speaker in national and international professional and business gatherings. He was named a winner of the prestigious ASQ MEA Quality Professionals Award for the year 2018 in Jordan.

One thought on “Excel Tip: Using Conditional Formatting to Warn on Deadlines”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.