by Alexander Frolov, updated on
The tutorial will teach you how to quickly add colored bars in Excel and customize them to your liking.
To compare different categories of data in your worksheet, you can plot a chart. To visually compare numbers in your cells, colored bars inside the cells are a lot more useful. Excel can show the bars along with the cell values or display only the bars and hide the numbers.
Data Bars in Excel are an inbuilt type of conditional formatting that inserts colored bars inside a cell to show how a given cell value compares to others. Longer bars represent higher values and shorter bars represent smaller values. Data bars can help you spot highest and lowers numbers in your spreadsheets at a glance, for example identify best-selling and worst-selling products in a sales report.
Conditional formatting data bars should not be confused with bar charts - kind of Excel graph that represents different categories of data in the form of rectangular bars. While a bar chart is a separate object that can be moved anywhere on the sheet, data bars always reside inside individual cells.
To insert data bars in Excel, carry out these steps:
Once you do this, colored bars will immediately appear inside the selected cells.
For example, this is how you make gradient fill blue data bars:
To add solid fill data bars in Excel, pick the color of your choice under Solid Fill:
To fine-tune the appearance and settings your data bars, select any of the formatted cells, click Conditional Formatting > Manage Rule > Edit, and then choose the desired color and other options.
Tip. To make the differences among the bars more noticeable, make the column wider than usual, especially if the values are also displayed in cells. In a wider column, the values will be positioned over the lighter part of a gradient fill bar.
There are two bar styles in Excel - Gradient Fill and Solid Fill.
Gradient Fill is the right choice when both data bars and values are displayed in cells - lighter colors at the end of the bars make it easier to read the numbers.
Solid Fill is better to be used if only the bars are visible, and the values are hidden. See how to show only data bars and hide numbers.
If none of the preset formats suits your needs, you can create a custom rule with your own data bar style. The steps are:
Below is an example of data bars with a custom gradient color. All other options are default.
When applying preset data bars, the minimum and maximum values are set automatically by Excel. Instead, you can decide how to calculate these values. For this, do the following:
If you are editing an existing rule, then click Conditional Formatting > Manage Rule. In the list of rules, select your Data Bar rule, and click Edit.
For example, you can set data bar percentage, with the minimum value equal to 0% and the maximum value equal to 100%. As a result, the highest value bar will occupy the whole cell. For the lowest value, there will be no bar visible.
Instead of defining certain values, you can calculate the MIN and MAX values using the corresponding function. For better visualization, we apply the following formulas:
For the Minimum value, the formula sets the minimum 5% below the lowest value in the referenced range. This will display a tiny bar for the lowest cell. (If you use the MIN formula in its pure form, there will be no bar visible in that cell).
=MIN($D$3:$D$12)*0.95
For the Maximum value, the formula sets the maximum 5% above the highest value in the range. This will add a small space at the end of the bar, so that it does not overlap the entire number.
=MAX($D$3:$D$12)*1.05
In case of preset conditional formatting, there is no obvious way to format given cells based on values in other cells. When using data bars of a very bright or dark color, such an option would be extremely helpful not to obscure values in cells. Luckily there is a very easy workaround.
To apply data bars based on a value in a different cell, this is what you need to do:
In our case, the numbers are in column D, so the formula in E3 copied down is =D3. As a result, we have the values in column D and data bars in column E:
If your dataset contains both positive and negative numbers, you'll be glad to know that Excel data bars work for negative numbers as well. To apply different bar colors for positive and negative numbers, this is what you do: Now, you can identify negative numbers by casting a quick look at your dataset.
Showing and hiding values in formatted cells is just a matter of a single tick mark :) If you wish to only see colored bars and no numbers, in the Formatting Rule dialog box, select the Show Bar Only check box. That's it!
Excel data bars for negative values
How to show only bars without values
This is how to add data bars in Excel. Very easy and so very useful!
Data bars in Excel - examples (.xlsx file)
Table of contents