Create accurate data bars in Excel 2007

Data Bars are new to Excel 2007; by altering the highest and lowest value, you can change the story the bars tell.

With little effort you can display a small comparison chart right in your sheet—similar to the REPT() technique in Add a quick comparison chart to a worksheet. Data bars are an extension of the conditional formatting feature (and new to Access 2007).

First, I'll show you how to use conditional formatting to display data bars. Then, I'll show you how to adjust the settings to adjust the size of the bars, without changing the actual values the bars represent. The following sheet displays a small set of values in column B. To display data bars for these values, complete the following steps:

  1. Select the values, B2..B9.
  2. On the Home tab, click the Conditional Formatting dropdown in the Styles group and choose Data Bars.
  3. Select an option from the galley.

The result, a series of comparison bars, one for each value, looks good. Each bar reasonably represents the relationship it shares with the others. For example, the highest value, in B2, consumes the entire cell. The lowest value, in B3, is about 20% of the value in cell B2 and consumes about one-fifth of the cell.

That relationship is subjective. You can change the bars and thereby change the story the bars tell. Let's change the rules a bit. (I'm working with the same data so you can compare the changes side by side, but you can work with the same set of values.)

After applying the data bars, choose Manage Rules from the Conditional Formatting dropdown. In the resulting dialog box, click Edit Rule. The default rule is Format All Cells Based On Their Values and that's the rule we want to change. In the Edit Rule Description section, the Minimum and Maximum values are set to Automatic. Change the Minimum Type setting to Number and Excel will set the Value to 0. Click OK twice.

Not much has changed for the higher values. However, the bar for the lowest value has completely disappeared. In addition, the bars for the lower numbers are smaller. That's because we changed the ratio between the set of values by decreasing the lower value to 0. Changing the minimum value without changing the maximum value isn't a balanced approach. (I didn't expect the bar for the lowest value to completely disappear.)

Now, let's try one more rule change. This time, select Number from the Type dropdowns for both the Minimum and Maximum values. In the Minimum Value control, enter 0; enter 1000 for the Maximum value.

This time, all the bars seem to adjust just a bit, but the most dramatic change is in the bar for the highest value. As you can see, it no longer consumes the entire cell. That's because the bars represent the values from 1 through 1000 and not just the highest and lowest values in the data set. The differences are subtle, but sometimes, you need that kind of precision.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks