Search Contextures Sites
Excel Conditional Formatting - Data Bars
To view the steps in a short video, click here:
Excel Conditional Formatting Data Bars Video
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.
- 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.
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.
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.
- 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.
To see the steps for creating data bars in a cell, please watch this short video.
Contextures Inc., Copyright ©2014
All rights reserved.
Last updated: March 4, 2014