Home > Format > ConditionalFormatting > Data Bars

Excel Conditional Formatting Data Bars

Use conditional formatting to add data bars to cells with numbers. This makes it easy to visually compare numbers, as you would in a bar chart. Go beyond the basic data bar settings, and customize your Excel data bars.

Data Bars Intro

If cells contain numbers, you can add conditional formatting data bars, to show the differences among the amounts. Watch this short video to see how to set up data bars in a cell, and the written instructions are below.

How to Add Data Bars 

In the screen shot below, conditional formatting data bars have been added to a sales report. You can quickly see that June had the smallest sales, and January and March have the largest.

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

• On the Excel worksheet, select the cells with numbers that you want to format.
  • Do NOT include any row or column totals in the selected data range.
• 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, from the Gradient Fill section or the Solid Fill section.
• NOTE: You can easily change the data bar colours later.

Data Bars on Worksheet

The selected cells now show Data Bars, along with the original numbers.

In the example shown below, I selected the Green data bars, from the Gradient Fill section.

The cells with data bars look like a bar graph, with values at the outer edge

Change Data Bar Appearance

After you add Data Bars to your worksheet, you can adjust the Data Bar Appearance settings, to get the best results for your data visualization.

Note: These changes are optional -- you can leave the data bars as they are, if you're satisfied with the default settings for their appearance.

Open Data Bar Appearance Settings

To change any of the data bar Appearance settings, follow these steps, to get started:

• Select at least one of the cells with data bars -- this makes it easier to apply the new settings
• On the Ribbon, click the Home tab
• In the Styles group, click Conditional Formatting, and then click Manage Rules.
• In the Conditional Formatting Rules Manager, the list of rules, click your Data Bar rule.
• Click the Edit Rule button, to open the Edit Formatting Rule dialog box.
• The Bar Appearance settings are in the bottom section of the dialog box

Data Bar Fill

There are two options in the Fill drop down:

1. Solid Fill: The default option, where data bars are displayed as solid-filled rectangles.
2. Gradient Fill: Data bars are displayed with a gradient fill, transitioning from one color to another

Which fill setting should you choose?

1. Solid Fill is a better choice for the data bars if the numbers will be hidden in the cells (instructions below)
2. Gradient Fill is better if the numbers will be visible in the cells -- the lighter colours at the end of the gradient make it easier to read the numbers.

Data Bar Color

There are multiple options in the Color drop down:

• Theme Colors
• Standard Colors
• More Colors, which opens the color palette window

Which colours should you choose?

You can choose colours based on your company's guidelines, or your personal preferences.

My worksheets are usually white background (no fill), so I prefer data bars in a medium to dark colour, and avoid the lighter colours.

Data Bar Border

There are 2 options in the data bar Border setting drop down:

• No Border
• Solid Border

If you select the Solid Border option, you can choose from a Color drop down, which has the same options as the Fill Color setting -- Theme Colors, Standard Colors, and More Colors.

Which Border settings should you choose?

• For data bars with Solid Fill, I useually select the No Border option, because the solid shape doesn't need any outline.
• For Gradient Fill data bars, I select the Solid Border option. For its colour, I select something a few shades lighter than the Fill colour.
  • That subtle outline makes it easy to see where the gradient fill ends, and doesn't add "clutter" to the set of data bars.
  • The Preview section lets you see the selected data bar appearance settings, before you apply them to the worksheet.

Negative Value and Axis

Some data can have both negative and positive values, and those are formatted with different colours, in the default data bars.

For example, in the data bar screen shot above:

• I listed 6 months, and the average temperature for each month. January and February are cold, here in Canada, so those months have negative numbers.
• When adding the data bars with conditional formatting, I selected Solid Fill - Orange.
  • Excel used orange for the positive numbers' data bars
  • Negative numbers were automatically shown as red-coloured bars.
• There is an Axis line at the zero point
  • Negative number bars are at the left of the axis
  • Positive number bars are at the right of the axis

Negative Value and Axis Settings Dialog Box

To adjust these settings, follow these steps:

• In the Edit Formatting Rule dialog box, click the Negative Value and Axis button

When the Negative Value and Axis Settings dialog box opens, you can see the current settings, in 3 sections:

1. Negative Bar Fill Color
2. Negative Bar Border Color
3. Axis Settings

There are details about each section below.

1) Negative Bar Fill Color

By default, the negative bar fill color is red, no matter whick data bar colour you select -- even if you choose red data bars!

For this setting, you can:

• Choose any fill colour of your choice, from the drop-down colour palette.
• OR, select the Apply same fill color as positive bar setting

Why Change Fill Color?

For most data, I leave the negative bars in the default red colour. However, for the temperature data, I chose blue for the negative numbers, because blue is often associated with cold.

Or, in some data, such as error report tracking, it's a good thing if there are loser numbers this month, than in the previous month. For that data, you could make positive bars red, and negative bars green.

2) Negative Bar Border Color

The negative bar border color settings are only available if the positive bars have Solid Border selected. For example, in the screen shot above, the border options are dimmed, because the positive bars have No Border selected.

For this setting, you can:

• Choose any border colour of your choice, from the drop-down colour palette.
• OR, select the Apply same border color as positive bar setting

Why Change Border Color?

For most data, if using a border, I choose a lighter shade of the bar colour. By default, the negative bar border has the same border colour applied.

In that case, I would change the negative border colour, so it's similar to the negative bar fill colour. IN the screen shot below, the fill color and border color are both blue.

3) Axis Settings

By default, the axis is automatically positioned in the cell, based on the negative values. The default axis colour is black.

For the axis position setting, you can choose from these 3 options, to change the appearance of bars for negative values:

• Automatic (display at variable position based on negative values)
• Cell midpoint
• None (show negative value bars in same direction as positive)

By default, the Axis colour is black, and you can select a different colour from the drop-down palette, if needed.

Note: There is no setting to adjust the axis line thickness.

Why Change Axis Position Setting?

In the screen shot below, you can see my temperature data, with the 3 different axis settings.

1. Automatic: I usually use this setting, which makes full use of the entire cell width. Bar with minimum value starts at the left edge of the cell, and data bar for the largest value ends at the right. Location of the axis is set at zero.
2. Centered: Occasionally, I use this setting, when comparing data in side-by-side sets of data bars. The axis doesn't more, so it's easier to spot the differences
3. No Axis: I never use this setting, because it uses the lowest number as the "axis" point, and that's not ideal, in the data that I've worked with

Data Bar Direction

The final data bar appearance setting is Direction, and it has 3 options, which you can choose from the drop-down list:

1. Context (default) -- switch between left/right and right/left direction, as needed
2. Left-to-Right -- data bars always start from the left side, and end at the right side
3. Right-to-Left -- data bars always start from the right side, and end at the left side

In the screen shot below, there are data bars showing each of the direction options.

Change Direction

Here are a couple of notes on the data bars direction options.

A) What causes the "Context" data bars to change directions, after you apply the direction settings?

• Regional settings with languages that use a right-to-left setting, could affect the data bars, when Direction is set as Context.

B) Does cell formatting affect the data bar direction setting?

No, the cell's horizontal alignment setting does not affect the data bars.

• In the screen shot above, I formatted the temperature data bars, with the 3 different bar directions applied.
• In the lower set of data bars, I changed the cell formatting, to left alignment, instead of general alignment.
• The cells' numbers moved to the left side of the cells, but none of the data bars changed direction.

Data Bars Tips

Here are a couple of tips for data bar setup.

Fill Type

There are two sets of Data Bar options -- Gradient Fill and Solid Fill.

• Solid Fill is a better choice if the data bars if the numbers will be hidden in the cells (instructions below)
• Gradient Fill is better if the numbers will be visible in the cells -- the lighter colours at the end of the gradient make it easier to read the numbers.

NOTE: Change the numbers to Bold font, so they are easier to see.

Column Width

If possible, make the column wider than usual, if you are adding data bars, especially if the numbers will also be visible.

• A wider column makes it easier to see the differences among the bars
• In a wider column, the numbers at the right will be positioned over the lighter part of a bar with gradient fill.

Show Data Bars Only 

After you add Data Bars, you can change the conditional formatting settings, so only the data bars are shown in the cell, without the numbers.

Follow these steps to hide the numbers:

• Select the cells that contain the data bars.
• On the Ribbon, click the Home tab
• In the Styles group, click Conditional Formatting, and then click Manage Rules.
• In the list of rules, click your Data Bar rule.
• Click the Edit Rule button, to open the Edit Formatting Rule dialog box.



• In the second section -- Edit the Rule Description -- add a check mark to Show Bar Only



• Click OK, twice, to close the dialog boxes.

Set Data Bar Minimum

When you add Data Bars, their minimum and maximum values are automatically set, usually with a minimum of zero, and a maximum that matches your highest value.

To "zoom in" on the differences between the amounts, follow these steps to change the Minimum setting:

• 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.
• In the list of rules, click your Data Bar rule, then click the Edit Rule button
• In the "Edit the Rule Description" section, the default settings are shown for Minimum and Maximum
• Click the arrow for Type, under Minimum, to see the other options.



• For this example, select Formula
• Then, in the value box, type the following formula.

  =MIN($G$4:$G$9)*0.9

  The formula finds the lowest value in the group of cells, and sets the minimum 10% below that. (If you just choose Lowest Value, there is no bar visible for that value, which can be confusing).

• (optional) Change the Maximum settings, if required. (see note below)
• Click OK, twice, to close the dialog boxes.

NOTE: In the sample file, a formula is also used to set the Maximum: =MAX($G$4:$G$9)*1.1. That formula finds the highest value, and sets the maximum 10% above that. This creates a small space at the end of the bar, instead of going to the cell border. Use a similar formula if you want extra room to show numbers, without the bars overlapping them.

Pivot Table Data Bars

For pivot tables with a single column of values, conditional formatting data bars can be an effective way to show the results.

This short video shows the steps for setting up a pivot table with data bars, and there are written steps on the Pivot Table Conditional Formatting page.

Get the Sample File

To follow along with this tutorial, get the Conditional Formatting Data Bars sample file. The file is in xlsx format, and does not contain macros.