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.
You can modify the conditional formatting, to show only the data bars, without the cell values.
The Conditional Formatting Rules Manager dialog box opens, where
you can see the
Data Bar rule, and any other conditional formatting rules you created.
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.
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.
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.
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.
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.
To see the steps for creating data bars in a cell, please watch this short video.
Last updated: November 12, 2015 7:19 PM