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

In this tutorial, 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

- First, select the data you want to add to the data bar.
- Go to the Home and click on the Conditional Formatting drop-down.
- From there, go to the Data bar option.
- Finally, click on the data bar style you want to apply.

And the moment you click on the new 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 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.

- You can change the color type from solid to gradient and gradient to solid.
- There’s an option to add a border around the bar.
- 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”.

## Get the Excel File

Download## More on Conditional Formatting

- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel

- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel