in a range is often useful. For example, you might want to know whether a range has many low values and just a few high values. Color scales can help you analyze your data in this way. A color scale compares the relative values in a range by applying shading to each cell, where the color reflects each cell’s value.
Color scales can also tell you whether your data includes outliers: values that are much higher or lower than the others. Similarly, color scales can help you make value judgments about your data. For example, high sales and low numbers of product defects are good, whereas low margins and high employee turnover rates are bad.
To apply a color scale to a range of values, do the following:
1 Select the range you want to format.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Color Scales and then select the color scale that has the color scheme you want to apply.Color scales come in two varieties: three-color scales and two-color scales. If your goal is to look for outliers, go with a three-color scale because it helps the outliers stand out more. A three-color scale is also useful if you want to make value judgments about your data, because you can assign your own values to the colors (such as positive, neutral, and negative). Use a two-color scale when you want to look for patterns in the data, because a two-color scale offers less contrast.Excel applies the color scale to each cell in your selected range.
Analyzing cell values with icon sets
Symbols that have common or well-known associations are often useful for analyzing large amounts of data. For example, a check mark usually means that something is good or finished or acceptable, whereas an X means that something is bad or unfinished or unacceptable. Similarly, a green circle is positive, whereas a red circle is negative (think traffic lights). Excel puts these and other symbolic associations to good use with the icon sets feature. You use icon sets to visualize the relative values of cells in a range.
Here’s how you apply an icon set to a range:
1 Select the range you want to format with an icon set.
2 Choose Home ⇒ Conditional Formatting.
3 Choose Icon Sets and then select the type of icon set you want to apply.Icon sets come in four categories:Directional: Indicates trends and data movementShapes: Points out the high (green) and low (red) values in the rangeIndicators: Adds value judgmentsRatings: Shows where each cell resides in the overall range of data valuesExcel applies the icons to each cell in the range, as shown in Figure 1-5.
FIGURE 1-5: Excel applies an icon based on each cell’s value.
Creating a custom conditional-formatting rule
The conditional-formatting rules in Excel — highlight cells rules, top/bottom rules, data bars, color scales, and icon sets — offer an easy way to analyze data through visualization. However, you can tailor your formatting-based data analysis also by creating a custom conditional-formatting rule that suits how you want to analyze and present the data.
To get the type of data analysis you prefer, follow these steps to create a custom conditional-formatting rule and apply it to your range:
1 Select the range you want to analyze with a custom conditional-formatting rule.
2 Choose Home ⇒ Conditional Formatting ⇒ New Rule.The New Formatting Rule dialog box appears.
3 In the Select a Rule Type box, select the type of rule you want to create.
4 Use the controls in the Edit the Rule Description box to edit the rule’s style and formatting.The controls you see depend on the rule type you selected in Step 3. For example, if you select Icon Sets, you see the controls shown in Figure 1-6. With Icon Sets, select Reverse Icon Order (as shown in the figure) if you want to reverse the normal icon assignments.
5 Click OK.Excel applies the conditional formatting to each cell in the range.
FIGURE 1-6: Use the New Formatting Rule dialog box to create a custom rule.
You can apply conditional formatting based on the results of a formula. That is, you set up a logical formula as the conditional-formatting criteria. For each cell in which that formula returns TRUE, Excel applies the formatting you specify; for all the other cells, Excel doesn't apply the formatting.
In most cases, you use a comparison formula, or you use an IF function, often combined with another logical function such as AND or OR. In each case, your formula's comparison value must reference only the first value in the range. For example, if the range you are working with is a set of dates in A2:A100, the comparison formula =WEEKDAY(A2)=6 would apply conditional formatting to every cell in the range that occurs on a Friday.
The following steps show you how to apply conditional formatting based on the results of a formula:
1 Select the range you want to work with.
2 Choose Home ⇒ Conditional Formatting ⇒ New Rule.The New Formatting Rule dialog box appears.
3 Select Use a Formula to Determine Which Cells to Format.
4 In the Format Values Where this Formula Is True text box, type the logical formula.The figure shows an example of using a formula to apply conditional formatting.
5 Choose Format, use the Format Cells dialog box to define the rule’s style and formatting, and then click OK.
6 Click OK.Excel applies the conditional formatting to each cell in the range in which the logical formula returns TRUE.
When you're messing around with formula-based rules, one useful technique is to apply a conditional format based on a formula that compares all the cells
