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 8^th, 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.