Software

Use conditional formatting in Excel to highlight important dates

If you don't want events slipping up on you, or past you, use Excel's conditional formatting as an alert system.

Many applications track dates-for deliveries, project management, appointments, and so on. When working with dates, you might want an alert as the date approaches or passes. Using conditional formatting you can format the date to draw your attention to the record.

Now suppose you keep a simple sheet (below) of delivery dates for your product and you want Excel to display today's delivery records. To set this formatting, you'd do the following:

  1. Select the data range. In the example, that's C4:C11.
  2. Click the Home tab.
  3. In the Styles group, click Conditional Formatting.
  4. Choose New Rule.
  5. In the resulting dialog box, choose Format Only Cells That Contain in the upper pane.
  6. In the lower pane, choose Dates Occurring from the first dropdown.
  7. In the second dropdown, choose Today.
  8. Click Format and then click the Font tab.
  9. From the Color dropdown, choose Red, and click OK twice.

As you can see, the conditional formatting displays the current date in red (assuming the current date is November 15.) Using the Dates Occurring settings, you can easily create conditions for just about any situation.

If you want to format more than the date cells, or you're using Excel 2003, you'll need a formula. In this case, you'd use the formula, =$C4=TODAY(). To highlight yesterday, you'd use the formula =$C4=TODAY()-1; to highlight tomorrow, you'd use the formula =$C4=TODAY()+1, and so on. (In Excel 2003, Conditional Formatting is on the Format menu.)

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
COFGFOR
COFGFOR

Off-topic just a bit, but why is it that Excel 2007 will not record a Conditional Formatting macro? I turn on Record Macro, conditionally format a cell with a New Rule based on a formula, turn off recording, hit Alt+F11 and there is nothing there.

danielfurze
danielfurze

I do something very similar to highlight important dates on my 'to do list' spreadsheet! Very helpful to see up and coming deadlines at a glance :)

Robiisan
Robiisan

This might make a good "weekly challenge." :-)

ssharkins
ssharkins

You said similar, but perhaps not the same -- what do you do?