Excel Data Analysis For Dummies. Paul McFedries

Скачать книгу

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

      Removing conditional-formatting rules

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.

Summarizing Data with Subtotals

      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.

      Follow these steps to summarize your data with subtotals:

      1 Select a cell within the range you want to subtotal.

      2 Choose Data ⇒ Subtotal.If you don’t see the Subtotal command, choose Outline ⇒ Subtotal. The Subtotal dialog box appears.

      3 In the At Each Change In list, select the column you want to use to group the subtotals.

      4 In the Use Function list, select Sum.

      5 In the Add Subtotal To list, select the check box for the column you want to summarize.In Figure 1-8, for example, each change in the Customer field displays the sum of that customer’s Total cells.

      6 Click OK.Excel calculates the subtotals and adds them into the range. Note, too, that Excel also adds outline symbols to the range. I talk about outlining in a bit more detail in the next section.

Скачать книгу