30 Advanced Excel Tips for 2026

Excel Champs / Excel Basics / How to Add Data Bars in Excel
By Puneet Gogia (Microsoft MVP)

How to Add Data Bars in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Select the numbers, then go to Home → Conditional Formatting → Data Bars and pick a Gradient Fill or Solid Fill style. Each cell gets a bar whose length shows its value against the rest of the range.

To set the scale yourself, go to Home → Conditional Formatting → Data Bars → More Rules and change the Minimum and Maximum types. For scores out of 100, use Number with 0 and 100, so a full bar always means 100 rather than the largest value in the list. To take a limit from a formula, choose Formula and enter something like =MAX($B$2:$B$11).

Percent and Percentile work differently. Percent 80 puts the maximum 80% of the way from the lowest value to the highest. Percentile 80 puts it at the value that 80% of the data falls below, so the top 20% of cells all get a full bar.

To show the bars without the numbers, tick Show Bar Only in the same dialog. For negative numbers, click Negative Value and Axis to set the negative bar colour and where the axis sits in the cell.

If a cell gets no bar, the number is probably stored as text. Select it, click the warning icon and choose Convert to Number. A bar always reflects the cell’s own value, so to show bars for another column, add a helper column with a formula such as =B2, apply the bars there and tick Show Bar Only.

This tutorial will help you understand the complete process of adding data bars in Excel and customizing them per your needs.

What are Data Bars in Excel

In Excel, a data bar is a horizontal bar in a cell, based on the value in the same or a different cell, that adds a visual element and makes it easier to compare the values. These bars are essentially in-cell bar graphs, with the length of each bar representing the value in the cell relative to the other cells in the selected range.

In Excel, you can add data bars to a cell or multiple cells using conditional formatting. With conditional formatting, you have two options to add them:

  • Pre-Defined Data Bars
  • Custom Data Bars

We will look at both ways and learn to use them to analyze the data quickly.

Steps to Add Data Bars in Excel from Conditional Formatting

To add data bars in Excel using Conditional Formatting, follow these steps.

data-bars-in-excel-from-conditional-formatting

sample-workbook.xlsx

  1. Select the Data – First, open your Excel workbook and select the range of cells to which you want to apply the data bars. You can select a column, a row, or any range of cells containing the data you want to visualize.
  2. Go to Conditional Formatting – Go to the “Home” tab on the ribbon. Look for the “Styles” group. Click on “Conditional Formatting” to open the dropdown menu.
  3. Choose Data Bars: In the “Conditional Formatting” dropdown menu, select “Data Bars” to see the available options. It offers many styles, including gradient fills and solid fills in different colors.
  4. Select a Style – Click on the style of the data bar that you prefer. Once you click, Excel automatically applies the selected data bar style to your chosen range of cells.

And the moment you click on the data bar you want to apply to the data, you will instantly get it.

These pre-defined data bar rules are pretty easy to apply.

When you select any of them, it takes the highest value from the selected data and gives it a full data bar.

And then it uses that value to add bars to the rest of the cells.

highest-values-from-the-selected-data-conditional-formatting

There are 12 data bar styles (6 gradient fill and 6 solid fill) that you can apply quickly to the data.

Add Data Bars with Custom Settings (More Rules)

First, go to Home > Conditional Formatting > Data Bars > More Rules.

data-bars-with-custom-settings-conditional-formatting

Once you click on it, you will get a dialog box to use custom settings to add data bars.

use-custom-settings-to-add-data-bars-conditional-formatting

In this dialog box, you have many options to use to create a custom data bar rule. For example, you can change how you apply the data bars from the “Type” drop-down.

change-how-you-apply-the-data-bars-conditional-formatting

1. Lowest and Highest

With this option, Excel will take the lowest and the highest number from the data itself.

As you can see in the below example, the lowest number in the data doesn’t have a data bar, and the highest number in the data has a full data bar.

highest-number-in-the-data-conditional-formatting

2. Numbers

With the Numbers option, you can define the lowest and the highest number by yourself. And it will consider that range of numbers to add the data bar.

In the below example, the lowest number is 0, and the highest number is 4000, which is why all the data bars are considerably small compared to the cell’s width.

numbers-data-bars-conditional-formatting

3. Percentage

With percentage, you can define the lowest and highest value equivalent of a percentage. In the below example, we have 20% for the minimum and 80% for the maximum.

percentage-data-bars-conditional-formatting

Here, the percentage is a share of the range between the lowest and the highest value, not of the total. The lowest value is 1161 and the highest is 1874, so the range is 713. 20% of it is about 143, so the minimum point is 1161 + 143 = 1304. The two lowest values, 1161 and 1201, are below that point, and that’s why you don’t have any bar in these two cells.

When you use 80% for the maximum, the maximum point is 1161 + 80% of 713, which is about 1731. Any value at or above 1731 gets a full bar. The top two values are 1874 and 1651; only 1874 is above that point.

That’s why you have a full bar on the cell with 1874, and the cell with the value 1651 has a long bar, but not a full one.

4. Percentile

With the percentile, you can create a group of top and low values that get the same full or empty data bar.

percentile-data-bars-conditional-formatting

Let’s say you specify the 70th percentile for the top, which means the top 30 percent of the values. Our data has ten values, and the top 30 percentile will be the top 3. And that’s why you can see in the data the cells with the top three values have full-width data bars.

For the minimum, we have used 30, which means the lowest 30 percent of the values. And as we have ten values in the data, the lowest 30 percent will be 3 values. And that’s why the lower three values don’t have any data bar.

And other cells have data bars according to the values.

5. Formula

You can also use a formula to define the minimum and maximum numbers of the data bars. You don’t need to do that most of the time, but there are some situations where you need it, like when the minimum and maximum come from another column or another sheet.

formula-data-bars-conditional-formatting

You can see in the above example that we have used the MIN and MAX functions to create formulas to get minimum and maximum values from column B to apply the data bars on column A.

6. Automatic

This is what Excel uses for the pre-defined data bars. With Automatic, the minimum is 0 (or the lowest value if the data has negative numbers), and the maximum is the highest value. So, with only positive numbers, even the lowest value gets a short bar, which is different from the Lowest Value option.

Custom Formatting for Data Bar

You also have options to change the formatting of the data bars as per your needs.

custom-formatting-data-bars-conditional-formatting
  1. You can change the color type from solid to gradient and gradient to solid.
  2. There’s an option to add a border around the bar.
  3. And you can change the direction of the bar from left to right or right to left.

And if you only want to show the bars and hide the numbers, you can tick “Show Bar Only”.

hide-the-numbers-data-bars-conditional-formatting

Data Bars with In-Cell Charts

3 Different Ways to Create In-Cell Charts in Excel | Excel Intermediate Tutorials

Data Bars with Negative Values

When your data has negative numbers, Excel draws the negative bars in red on the other side of an axis. To change this, open the rule in More Rules (or Manage Rules > Edit Rule) and click Negative Value and Axis. From there, you can change the fill and border color of the negative bars and move the axis to the cell midpoint or hide it.

Show the Number and the Bar in Separate Columns

If the number gets hard to read on top of the bar, put a copy of the values in the next column and show only the bars there. For example, if the values are in A2:A11, enter this formula in B2 and drag it down to B11:

=A2

After that, apply the data bars to B2:B11 and tick “Show Bar Only” in the rule. Now column A shows the numbers and column B shows only the bars.

Wrap Up

Data bars are useful for financial analysis, project tracking, inventory management, and any scenario where you need to quickly identify trends or outliers within a data set.

Frequently Asked Questions

How do I remove data bars?

Select the cells and go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. The values in the cells stay as they are.

Why do all my data bars look almost the same?

With Automatic, the bars start from 0. If all your values are close to each other and far from 0 (like 1161 to 1874), the bars look similar. Change the minimum type to Lowest Value to show the differences better.

Do data bars work on text or blank cells?

No. Data bars only show for numbers. Text, blank cells and numbers stored as text don’t get a bar, so convert numbers stored as text to real numbers first.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel