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