Showing Data Bars for a Subset of Cells in Excel 2007:-

In the data bars examples given in the previous sections, every cell in the range receives a data bar. But what if you just want some of the values (for example, the top 20%) to have data bars? The process for making this happen isn’t intuitive, but it is possible. Basically, you apply the data bar to the entire range. Then you add a new conditional format (a very boring format) to all the cells that you don’t want to have data bars. For example, you might tell Excel to use a white background on all cells with values in the lowest 80th percentile.

The final important step is to manage the rules and tell Excel to stop processing more rules if the white background rule is met. This requires clever thinking. If you want to apply data bars to cells in the top 20%, you first tell Excel to make all the cells in the bottom 80% look like every other cell in Excel. Turning on Stop if True (in the Conditional Formatting Rules Manager dialog) is the key to getting Excel to not apply the data bar to cells with values in the lower 80%.

The first Figure (given below) shows data bars applied to only the top 20% of states.



(Using Stop if True after formatting the lower 80% with no special formatting allows the data bars to appear only on the top states.)

In the first Figure (given above), the goal is to have the data bars appear only on the top 20% of states. You can use the following steps to create an analysis similar to this:

1. Apply a data bar to the range of cells.

2. With the range of cells selected, from the Home ribbon, select Conditional Formatting, Manage Rules. Excel displays the Conditional Formatting Rules Manager dialog.

3. Click New Rule. Excel displays the New Formatting Rule dialog.

4. In the top of the New Formatting Rule dialog, choose Format Only Top or Bottom Ranked Values. In the bottom of the dialog, change Top to Bottom. Enter 80 in the text box and select the % of the Selected Range check box. Leave the Preview box as No Format Set. Look into the second Figure (given below). Click OK to create the rule.



(You can choose to highlight the bottom 80% with no special formatting.)

5. In the Conditional Formatting Rules Manager dialog, make sure that the Stop if True box is checked for the Bottom 80% rule. Note that new rules are initially added to the top of the list. Look into the Figure (given below). Click Close to dismiss the dialog.



(Using these settings, Excel applies no special formatting to the bottom 80% and then stops processing more rules.)