Data Bars in Excel: how to add and customize

What are Data Bars in Excel?

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.

How to add data bars in Excel

To insert data bars in Excel, carry out these steps:

1. Select the range of cells.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Point to Data Bars and choose the style you want - Gradient Fill or Solid Fill.

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.

Which Data Bar fill type is better to choose?

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.

How to create custom data bars in Excel

If none of the preset formats suits your needs, you can create a custom rule with your own data bar style. The steps are:

1. Select the cells where you want to apply data bars.
2. Click Conditional Formatting > Data Bars > More Rules.
3. In the New Formatting Rule dialog box, configure these options:
   • Choose the data type for Minimum and Maximum values. The default (Automatic) works fine in most cases. If you want more control over how the lowest and highest values are calculated, then choose Percent, Number, Formula, etc.
   • Experiment with the Fill and Border colors until you are happy with the preview.
   • Determine the Bar direction: context (default), left-to-right or right-to-left.
   • If needed, tick the Show Bar Only checkbox to hide the cells values and only show colored bars.
4. When done, click OK.

Below is an example of data bars with a custom gradient color. All other options are default.

How to define minimum and maximum data bars value in Excel

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:

1. If you are creating a new rule, click Conditional Formatting > Data Bars > More Rules.

   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.

2. In the rule dialog window, under the Edit the Rule Description section, choose the options you want for Minimum and Maximum values.
3. When done, click OK.

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.

Create Excel data bar based on formula

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

Excel data bars for negative values

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:

1. Select the cells you want to format.
2. Click Conditional Formatting > Data Bars > More Rules.
3. In the New Formatting Rule window, under Bar Appearance, choose the color for positive data bars.
4. Click the Nagative Value and Axis button.
5. In the Nagative Value and Axis Settings dialog box, pick the fill and border colors for negative values. Also, define the axis position and color. If you want no axis, then select the white color, so the axis will be invisible in cells.
6. Click OK as many times as needed to close all open windows.

Now, you can identify negative numbers by casting a quick look at your dataset.

How to show only bars without values

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!

Practice workbook for download

Data bars in Excel - examples (.xlsx file)