Excel Conditional Formatting - Data Bars

Adding Data Bars to Compare Values

In a table of numbers, you can use conditional formatting to add
data bars to the cells. This makes it easy to visually compare the
list of numbers, just as you would in a bar chart.

For example, in a sales report, you could quickly see which months
have the smallest sales, and which months have the largest.

To add conditional formatting with data bars, follow these steps.

On the Excel worksheet, select the value cells that you want to
format. If the Excel table has row or column totals, don't include
those cells.

On the Ribbon, click the Home tab, and then in the Styles group,
click Conditional Formatting.

In the list of conditional formatting options, click Data Bars,
and then click one of the Data Bar options. The Data Bar options
are identical, except for the color.

The selected cells now show Data Bars, representing their numeric
values.

Tip: If you find it difficult to see where the bars end, because
of the graduated coloring in the data bars, you can apply a dark fill
color to the cells, and then change the font to a light color.

Show Data Bars Only

You can modify the conditional formatting, to show only the data
bars, without the cell values.

In the table, select the cells that contain the data bars.

On the Ribbon, click the Home tab

In the Styles group, click Conditional Formating, and then click
Manage Rules.

The Conditional Formatting Rules Manager dialog box opens, where
you can see the
Data Bar rule, and any other conditional formatting rules you created.

In the list of rules, click your Data Bar rule.

Click the Edit Rule button, to open the Edit Formatting Rule dialog
box.

The first section of the Edit Formatting Rule dialog box, Select
a Rule Type, shows the type of conditional formatting rule that was
applied. No changes are required in the first section.

In the second section, Edit the Rule Description, you can see the
current settings for the Data Bar rule, and this is where you'll edit
the rule.

Add a check mark to Show Bar Only

Click OK, twice, to close the dialog boxes.

To ensure the amounts are accurately represented in the data bars,
so you change the settings for the Shortest Bar. Instead of using
the lowest value in the range of cells, you use zero as the setting
for the Shortest Bar.

Data Bar Scale Distortion

In Excel 2007, the conditional formatting data bars are not zero-based;
the shortest data bar represents the lowest value in the formatted
data, and the longest bar represents the highest value in the formatted
data.

In the table shown below, June has the lowest sales, at 621. January
sales are about twice that amount, at 1277. However, the January data
bar isn't twice the length of the June data bar -- it's about 8 times
the length of the June data bar.

If the lowest value is zero, and the highest value is 100, the data
bars might look the same as the data bars for 500 to 5000, or values
from -600 to -100.

Even if the lowest value is zero, or a negative number, it is represented
by a small data bar.

Change the Shortest Bar Setting

To ensure the amounts are more accurately represented in the data
bars, so you change the settings for the Shortest Bar. Instead of
using the lowest value in the range of cells, you use zero as the
setting for the Shortest Bar.

To change the Shortest Bar setting, follow these steps:

Select the cells that contain data bats, then open the Edit Formatting
Rule dialog box.

In the Edit the Rule Description section, under Shortest Bar,
click the Type drop down arrow.

Click Number, and a zero will automatically appear in the Value
box, for the Shortest Bar.
This is the setting you want, so leave the Value as zero.

Click OK twice, to close the dialog boxes.

The data bars now use a scale from zero to the highest number, to
show a more accurate representation of the numbers.

The scaling isn't perfect though; setting the shortest bar value
to zero still scales the data as if zero has a short bar. This problem
with inaccurate data bar lengths has been fixed in Excel 2010, along
with the problem of decreased visibility of the ends of the bars.

Video: Data Bars

To see the steps for creating data bars in a cell, please watch this
short video.