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.
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.
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.
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:
Data Bar Fill
There are two options in the Fill drop down:
Which fill setting should you choose?
Data Bar Color
There are multiple options in the Color drop down:
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:
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?
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:
Negative Value and Axis Settings Dialog Box
To adjust these settings, follow these steps:
When the Negative Value and Axis Settings dialog box opens, you can see the current settings, in 3 sections:
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:
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:
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:
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.
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:
In the screen shot below, there are data bars showing each of the direction options.
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?
B) Does cell formatting affect the data bar direction setting?
No, the cell's horizontal alignment setting does not affect the data bars.
Data Bars Tips
Here are a couple of tips for data bar setup.
There are two sets of Data Bar options -- Gradient Fill and Solid Fill.
NOTE: Change the numbers to Bold font, so they are easier to see.
If possible, make the column wider than usual, if you are adding data bars, especially if the numbers will also be visible.
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:
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:
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.
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.
Last updated: February 11, 2024 3:39 PM