How to use data bars with conditional formatting

Abstract

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 opt for cells we’d like to format.

In the Conditional Formatting menu, data bars are a main category.

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.

With positive values, Excel will use zero for the minimum and the largest value for the maximum.

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.

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.