Excel Data Analysis For Dummies. Paul McFedries
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 in a range to one value in that range. The simplest case is a formula that applies conditional formatting to those range cells that are equal to a cell value in the range. Here’s the logical formula to use for such a comparison:
=range=cell
Here, range is an absolute reference to the range of cells you want to work with, and cell is a relative reference to the comparison cell. For example, to apply a conditional format to those cells in the range A1:A50 that are equal to the value in cell A1, you would use the following logical formula:
=$A$1:$A$50=A1
Editing a conditional-formatting rule
Conditional-formatting rules are excellent data-visualization tools that can make analyzing your data easier and faster. Whether you're highlighting cells based on criteria, showing cells in the top or bottom of a range, or using features such as data bars, color scales, and icon sets, conditional formatting enables you to interpret your data quickly.
But it doesn't follow that all your conditional-formatting experiments will be successful ones. For example, you might find that the conditional formatting you used isn’t working out because it doesn’t let you visualize your data the way you’d hoped. Similarly, a change in data might require a change in the condition you used. Whatever the reason, you can edit your conditional-formatting rules to ensure that you get the best visualization for your data. Here’s how:
1 Select a cell in the range that includes the conditional-formatting rule you want to edit.You can select a single cell, multiple cells, or the entire range.
2 Choose Home ⇒ Conditional Formatting ⇒ Manage Rules.The Conditional Formatting Rules Manager dialog box appears, as shown in Figure 1-7.FIGURE 1-7: Use the Conditional Formatting Rules Manager to edit