Conditional Formatting in Excel

Microsoft Excel has a "conditional formatting" feature that allows one to change the formatting of cells based on their contents. E.g., you can specify that the background color for a cell or the font color be changed based on the current value of a cell. For example, suppose I have a list of work requests in a spreadsheet. Each row of the spreadsheet holds information on a particular work request. There is a column named "approved", which contains the date on which a work request was approved. If I want to change the color of the cell holding the approval date to green, if the request was approved within the last 7 days. I can use the formula =(TODAY()-F16)<7 for conditional formatting for cell F16 (row 16 column F), if that cell holds an approval date. I.e., if today is Tuesday, December 8, 2009 and I only want the cell color to change if a request was approved since the prior Tuesday, December 1, 2009, I can take the following steps in Excel (note these steps were written for Excel 2007, but should work for versions of Excel since Excel 95, which supported conditional formatting):
  1. Click on a cell that holds an approval date to select it.
  2. Choose Format.
  3. Choose Conditional Formatting.
  4. For Condition 1, select "Formula Is".
  5. Type =(TODAY()-F16)<7 in the formula field.
  6. Click on the Format button.
  7. Select the color you wish to use, e.g. green in this case.
  8. Click on OK.

    Excel conditional formatting

  9. Click on OK again.

I want the conditional formatting to apply to all cells holding approval dates in a spreadsheet, not just one particular cell. To apply the same conditional formatting to other cells in column F holding approval dates, I would select the cell holding the conditional formatting by clicking on it, then select Edit and Copy. I can then highlight other cells and apply the same conditional formatting to them. E.g., I could highlight the column for all rows except for the column heading. Once I've highlighted the cells to which the conditional formatting should apply, I can click on Edit then select Paste Special. I can then click on the Formats radio button to select Formats, then click on OK.

Excel conditional formatting

References:

  1. Excel: Today Function
    Tech on the Net
  2. Conditional Formatting to Make Due Dates Stand Out
    Microsoft Excel Tips
  3. Calculating the Number of Days Between Two Dates in Excel
    MoonPoint Support

Valid HTML 4.01 Transitional