Abstract
Transcript
Data bars are one of the key types of conditional formatting in Excel. They're great way to show the relative magnitude of values in a set of data.
Let's take a look.
Here we have a simple table that tracks the progress of a group of people towards a fundraising goal. Column C shows that each person has a goal of $3,000. And Column D shows the current funds raised per person.
Let's use conditional formatting to display progress as a data bar. As always, the first step is to select the cells we'd like to format.
In the Conditional Formatting menu, data bars are a main category. There are six presets for data bars with gradient fills, and six presets for data bars with solid fills. Except for the fill, these data bar presets are the same.
Excel builds a live preview on the worksheet as we hover over each option.
When we select a preset, the data bar formatting is applied immediately.
Like other conditional formatting, data bar rules can be edited at any time using Manage Rules.
When editing a data bar rule, you can specify a minimum and maximum, alter the bar appearance, the location of the axis (which is useful when some values are negative) and control the bar direction.
With positive values, Excel will use zero for the minimum and the largest value for the maximum. However, when the selection contains extra large values, like we see in row 11, the scale will be distorted, and make other values look more similar. In this case, a maximum number of 3000 makes more sense.
Now the scale is more meaningful.
You may not want the data bars overlapping numbers. One solution is to display bars in another column. To do this, we'll add a formula in column E that simply refers back to the values column D. Now, we'll apply data bars to this column.
As before, we set the maximum to the number 3000. Then, we check "Show bar only" to hide the numbers.
Finally, we delete the original rule. Now the numbers and bars are separated, but the bars are still linked to the data.
