Home > Format > ConditionalFormatting > Data Bars Excel Conditional Formatting Data BarsUse 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. |
Change Data Bar AppearanceAfter 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 SettingsTo change any of the data bar Appearance settings, follow these steps, to get started:
|
Data Bar FillThere are two options in the Fill drop down:
Which fill setting should you choose?
|
Data Bar ColorThere 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 BorderThere 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 AxisSome 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 BoxTo 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 ColorBy 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 ColorThe 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 SettingsBy 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 DirectionThe 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. Change DirectionHere 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 TipsHere are a couple of tips for data bar setup. Fill TypeThere 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. Column WidthIf possible, make the column wider than usual, if you are adding data bars, especially if the numbers will also be visible.
|
Pivot Table Data BarsFor 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 FileTo follow along with this tutorial, get the Conditional Formatting Data Bars sample file. The file is in xlsx format, and does not contain macros. |
Conditional Formatting Introduction
Conditional Formatting Based on another cell
Conditional Formatting Examples
Conditional Formatting Documentation
Last updated: June 30, 2023 4:24 PM