Pin Me

Adding Conditional Formatting Rules in Excel 2007

written by: •edited by: Tricia Goss•updated: 12/29/2009

Microsoft Excel’s conditional formatting tools are powerful features that can be used for both data analysis and making a spreadsheet more visually appealing. In this segment, we’ll show how to create a new user-defined formatting rule.

  • slide 1 of 2

    Creating New Conditional Formatting Rules

    As discussed in Part 1 of this series, Excel’s conditional formatting feature can be used to both analyze data and make that analysis visibly prominent on the spreadsheet. In this segment, we’ll describe how to create a conditional formatting rule to use for this type of in-depth study. To illustrate the directions given in this guide, we’ll use a collection of data that contains quarterly and yearly sales summary information.

    Step 1: Select the group of cells to which you want the conditional formatting rule to apply. (Click image to enlarge.)

    Select Cells 

    Note: When applying conditional formatting to a group of cells, it’s best to make sure that all the cells in that collection contain similar information. For instance, in our example, we wouldn’t want to compare yearly sales figures to quarterly sales figures since the annual numbers will always be larger than the quarterly ones.

    Step 2: On the Home tab of the Excel ribbon, click on Conditional Formatting in the Styles section. Then, select New Rule.

    Select New Rule 

    Step 3: In the New Formatting Rule window that appears on your screen, first select what kind of rule type you want to create. In our example, we want to apply a type of conditional formatting that will allow the viewer to glance at each value in the Total Yearly Sales column and see how it compares with all the other figures. So, we’ll choose Format all cells based on their values.

    Select a Rule Type 

    Step 4: Next, input the information for the rule description. For our particular example, we’ll use the 2-Color Scale style and keep all the default values except those related to color. Here, we want to start out with assigning a blue shade to the lowest value in the data grouping and have that color fade out to white for the highest value in the group.

    Change Color 

    Step 5: Click OK when done and the resulting rule will be applied to the selected cells.

    Final Table 

  • slide 2 of 2

    Modifying an Existing Conditional Formatting Rule

    If you decide that you don’t like the appearance of some aspect of the conditional formatting, and you just want to make minor changes, you don’t have to start all over again. You can make changes to any existing rule by clicking on Conditional Formatting in the Home tab and choosing Manage Rules.

    Manage Rules 

    In the Conditional Formatting Rules Manager window, select This Worksheet from the Show Formatting Rules dropdown list. This will bring up a listing of all rules for that particular sheet.

    Conditinal Formatting Rules Manager 

    From here, select the rule you want to modify and click Edit Rule. You’ll be returned to the Editing Formatting Rule window where you can make any desired changes.

    Edit Formatting Rule 

Microsoft Excel 2007: Conditional Formatting

This series of articles explores the many different ways that conditional formatting can be used to analyze data in Excel spreadsheets.
  1. Introduction to Conditional Formatting in Microsoft Excel 2007
  2. Adding Conditional Formatting Rules in Excel 2007
  3. Sorting Spreadsheets with Conditional Formatting in Excel 2007