Adding Conditional Formatting Rules in Excel 2007

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.)

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.

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.

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.

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

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.

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.

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.