How to Add Data Bars in Excel

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

What is Data Bars in Excel

In Excel, data bars are a form of conditional formatting that allows you to add a visual element to your cells, making it easier to compare the values visually. 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:

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.

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.

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

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

There are 12 data bars 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 Rule.

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

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.

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.

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.

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.

The total sum of the values is 14733, and 20% is 2946.6. Now if you see the lowest two values, 1161 and 1201, which is 2362, this covers most of the lower 20% of the part. That’s why you don’t have any bar in these two cells.

When you use 80% for the maximum, this also means to consider the top 20% values. And top two values are 1874 and 1651, and the total for both is 3525, and 3525 is greater than 20% of the sum of the values.

That’s why you have a full bar on the cell with 1874, and the cell with the value 1651 has a considerably wider bar than other values.

4. Percentile

With the percentile, you can create a group of top and low values and have an exact data bar. Don’

Let’s if you specify the 70th percentile for the top, which means the top 30 percentile values. Our data have 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 with means 30 percentile of the lower values. And as we have ten values in the data, the last 30 percentile values will be 3. 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. Usually, you don’t need to do that most of the time, but there would be some specific situations where you need to do this.

You can see in the above example; we have used 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

We use the same method while applying data bars with the pre-defined rules.

Custom Formatting for Data Bar

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

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 somehow you only want to show the bars and hide the numbers, you can tick-mark the “Show Bars Only”.

Warp 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.