Data Bars: Excel 2013 Conditional Formatting Tutorial

Page content

Suppose you had a spreadsheet that listed employees and their monthly sales totals. You could add data bars to quickly see how their sales compared to each other without needing to create a separate chart. If you wanted to further complicate the process, say you only cared that employees met a specific sales goal; you could then modify the data bars such that anything shy of a full bar denotes failure to meet the goal with a relative measure of how much was lacking.

1. Highlight the cells where the data bars should appear.

Although this simple list can be vetted manually, longer lists would be more time-consuming.

2. Click Conditional Formatting from the Home tab’s Styles group, point to Data Bars and select the style you prefer. In this case, the blue Gradient Fill was selected.

Notice that the bars already fill as you move the cursor over the style. However, this example has more specific needs than a simple total, so you’ll need to adjust the settings.

3. Click Conditional Formatting again and select Manage Rules.

4. Select the Data Bar rule and click Edit Rule. If only one rule appears, it is automatically selected. If you don’t see any rules, you probably clicked outside of the previously highlighted region; click the Current Selection drop-down menu and choose This Worksheet to see a list of all Conditional Formatting rules.

5. Check Show Bar Only to only display the data bars in the cells. Leave this option unchecked to show the data bars as a background with the values on top.

This example will hide the actual values, so you can see the effect of the Show Bar Only option.

6. Click the Type drop-down menu under the Minimum heading and select Lowest Value to grow the data bar beginning with the lowest value, in this case $2,843. Your other options are Number, Percent, Formula or Percentile.

This option is helpful for large numbers, because the differences in values will be more noticeable, but it also means the first value won’t have a data bar at all. In this example, that consequence easily flags the poorest performing employee.

7. Click the Type drop-down menu under the Maximum heading and select Number to specify the largest value for the data bar.

8. Enter the maximum value you want displayed in the Value field under the Maximum heading and then click OK. In this case, the sales goal was $5,000, so enter 5000 in the field.

Setting a maximum value of 5000 forces the data bar to cap out at $5,000, so anyone meeting their goal is visually identifiable, although you don’t get to see how much better they did unless you chose to display values as well.

In addition, you can adjust the colors, style and borders of the bars in the Bar Appearance section and also select a different scheme for negative values.

9. Click OK to accept the changes and close the Conditional Formatting Rules Manager.

As you can see, the full bars indicate the six employees who met their goals and the four who didn’t. It also shows David Bowles was the least productive, but that Hank Sweeney was just shy of making it. 

Also note that although the data values aren’t displayed, they are still present in the background, so you can still run calculations with the cell data. If you want to see a value, click the cell and look in the formula bar for the number.