- slide 1 of 11
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.
- slide 4 of 11
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.
- slide 6 of 11
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.
- slide 7 of 11
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.
- slide 9 of 11
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.