How to Create a New Conditional Formatting Rule in Microsoft Excel 2007

How to Create a New Conditional Formatting Rule in Microsoft Excel 2007
Page content

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

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

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

This post is part of the series: 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