Paul McFedries - Excel Data Analysis For Dummies

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 IFfunction, often combined with another logical function such as ANDor 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)=6would 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, rangeis an absolute reference to the range of cells you want to work with, and cellis 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

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

3 Select the rule you want to modify.If you don’t see the rule, click the Show Formatting Rules For drop-down list and then select This Worksheet. The list that appears displays every conditional-formatting rule that you’ve applied in the current worksheet.

4 Choose Edit Rule.The Edit Formatting Rule dialog box appears.

5 Make your changes to the rule.

6 Click OK.Excel returns you to the Conditional Formatting Rules Manager dialog box.

7 Select OK.Excel updates the conditional formatting.

If you have multiple conditional-formatting rules applied to a range, the visualization is affected by the order in which Excel applies the rules. Specifically, if a cell already has a conditional format applied, Excel does not overwrite that format with a new one. For example, suppose that you have two conditional-formatting rules applied to a list of student grades: one for grades over 90 and one for grades over 80. If you apply the over-80 conditional format first, Excel will never apply the over-90 format because those values are already covered by the over-80 format. The solution is to change the order of the rule. In the Conditional Formatting Rules Manager dialog box, select the rule that you want to modify and then click the Move Up and Move Down button to set the order you want. If you want Excel to stop processing the rest of the rules after it has applied a particular rule, select that rule’s Stop If True check box.

Conditional-formatting rules are useful critters, but they don’t work in all scenarios. For example, if your data is essentially random, conditional-formatting rules won’t magically produce patterns in that data. You might also find that conditional formatting isn’t helpful for certain collections of data or certain types of data. Or you might find conditional formatting useful for getting a handle on your data set but then prefer to remove the formatting.

Similarly, although the data-visualization aspect of conditional-formatting rules is part of the appeal of this Excel feature, as with all things visual, you can overdo it. That is, you might end up with a worksheet that has multiple conditional-formatting rules and therefore some unattractive and confusing combinations of highlighted cells, data bars, color scales, and icon sets.

If, for whatever reason, you find that a range’s conditional formatting isn’t helpful or is no longer required, you can remove the conditional formatting from that range by following these steps:

1 Select a cell in the range that includes the conditional-formatting rule you want to trash.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.

3 Select the rule you want to remove.If you don’t see the rule, use the Show Formatting Rules For list to select This Worksheet, which tells Excel to display every conditional-formatting rule that you’ve applied in the current worksheet.

4 Choose Delete Rule.Excel removes the rule from the range.

5 Click OK.

If you have multiple rules defined and want to remove them all, click the Home tab, choose Conditional Formatting, choose Clear Rules, and then select either Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

Although you can use formulas and worksheet functions to summarize your data in various ways — including sums, averages, counts, maximums, and minimums — if you’re in a hurry, or if you just need a quick summary of your data, you can get Excel to do the work for you. The secret here is a feature called automatic subtotals, which are formulas that Excel adds to a worksheet automatically.

Excel sets up automatic subtotals based on data groupings in a selected field. For example, if you ask for subtotals based on the Customer field, Excel runs down the Customer column and creates a new subtotal each time the name changes. To get useful summaries, you should sort the range on the field containing the data groupings you’re interested in.