Search Contextures Sites

.

Related Links

Conditional Formatting -- Introduction

Conditional Formatting -- Based on another cell

Conditional Formatting -- Examples

Conditional Formatting -- Documentation  

.

.

Contextures
Excel news
by email

.

.

.

Learn how to create Excel dashboards.

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.

pivot table order dates

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.

    pivot table top 2

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.

pivot table top 10

Show Data Bars Only 

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

pivot table order dates

  • 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

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.

 

Related Tutorials

  1. Conditional Formatting -- Introduction
  2. Conditional Formatting -- Based on another cell
  3. Conditional Formatting -- Examples
  4. Conditional Formatting -- Documentation  
  5. Conditional Formatting -- Data Bars  

Learn how to create Excel dashboards.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: August 9, 2014