Software

Let Excel's conditional formatting keep you abreast of upcoming due dates

You can easily set up your worksheets to provide a handy reminder that deadlines are getting close. Here's a look at how to use conditional formatting to flag items in a specified timeframe.

Excel 2002/2003's conditional formatting feature lets you see at a glance which accounts or projects are coming due. For example, suppose you listed 10 projects and their corresponding due dates in an Excel worksheet. When you open the sheet, all projects due next month should be marked accordingly. To set this up, follow these steps:

  1. Next to your project data, enter the labels and formulas as shown in Figure A.

Figure A

conditional formatting

  1. Select the range that refers to the Project due dates.
  2. Go to Format | Conditional Formatting.
  3. Click the second drop-down arrow and select Between.
  4. Click in the next box and enter =B4+1
  5. Click in the next box and enter =B3.
  6. Click the Format button and on the Font tab, select red from the Color drop-down list. Figure B shows how your selections will appear in the Conditional Formatting dialog box.
  7. Click OK twice.

Figure B

formatting options

Excel changes the font of next month's project due dates to red (Figure C).

Figure C

red dates

Note that Excel automatically updates the rule based on the Today function, which gets its value from the system clock. So when you open the workbook a month from now, Excel formats new dates based on the rule's conditions.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks