Create your own conditional formatting rules in Excel 2007

Conditional formatting offers one of the best ways to make key data stand out in your Excel worksheets -- but you don't have to settle for the predefined rules. See how you can set up a custom rule based on a formula you specify.

Even though Excel 2007 comes with its own built-in conditional formatting rules, there are times when you will want to create your own. For example, say you want to highlight the sales figures that occur on Sundays in the worksheet shown in Figure A.

Figure A

sales sheet

Follow these steps:

  1. Select A1:E21
  2. On the Home tab, click Conditional Formatting and then click New Rule.
  3. In the Select A Rule Type box, click Use A Formula To Determine Which Cells To Format.
  4. Click in the Format Values Where The Formula Is True box and enter the formula:
  5. =WEEKDAY($A2)=1
  6. Click the Format button, select Yellow under Background Color in the Fill tab, and click OK.

    Figure B shows the dialog box at this point. Just click OK twice to return to your worksheet.

    Figure B

    conditional formatting rule

    As you can see in Figure C, Excel highlights all rows pertaining to Sunday sales.

    Figure C

    sunday sales

    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.