What are Data Bars in Excel?

Data bars in Excel belong to the conditional formatting functionsConditional Formatting FunctionsConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more that allow us to insert a bar chart. Still, the main thing that makes data bars different from bar charts is that the data bars are inserted inside the cells instead of in a different location. The bar charts are inserted in a new location. They are an object to Excel, but data bars reside in the cell and do not object to Excel.

Data bars insert a bar chart inside Excel. It helps to visualize the values of the cells. The larger value will have a large bar line, and the lesser value will have a small bar line. This way, the data bar helps users visualize the numbers and allows them to save time. Data bars also enable a user to save the worksheet area as the data bars reside in the cell only.

How to Add Data Bars in Excel?

Below are the examples of adding data bars in Excel.

Example #1 – Data Bars along With the Values

Example #2 – Data Bars Without the Values

In this method, we can hide the values in the cell.

  • We must first select the range where we want to insert the data bars. Next, go to the “Home” tab and select “Conditional Formatting.” We must select the “Data Bars” option from the “Conditional Formatting” options. Now, we must choose anyone as per the required color theme from the available default data bar charts. After the data bars are inserted, we will get the result like the one below.

First, we will insert the data bars as per the above steps and follow the below steps.

Step 1: First, we need to select the data bar chart.

Step 2: Then, go to the “Conditional Formatting” and choose “Manage Rules.”

Step 3: From the “Manages Rules” tab,” choose “Edit Rules.”

Step 4: Now, we need to select the option of “Show Bar Only” so that the value in the cells is not visible.

Step 5:  After the above steps, we will get the below result.

Example #3 – Negative and Positive Data Bars

In this case, we need some negative and positive values.

To create a data bar chart with a negative and positive value, we must select the range of cells and follow the steps of method 1.

After the above steps, we will get the below result.

Example #4 – Data Bars Above a Certain Value

In this case, we need to add a condition that only those cells will be formatted that meet the prescribed requirement.

Step 1: From the “Manage Rules” option of conditional formatting, we must choose “Edit Rule.”

Step 2: From the “Edit Rule” window, we need to change the condition as per our requirement.

Step 3: Now, it will format only those cells with a value greater than 30.

Explanation of Data Bars in Excel

  • Data bars allow us to save time and create a visualization effect in just a few minutes. Data bars are the same as a bar chart. But the difference is that the bar charts are objects to Excel, but data bars are only the interior of cells, and they reside in the cell.Data bars work when we have quantitative data. It is not possible to use the data bars on qualitative data. When we insert data bars in Excel, the complete range of cells on which we have inserted the data bar is treated as one set. The Excel allocates the length of the bar based on the whole data.Excel will first analyze the data that we use, and then it will identify the minimum and maximum values in Excel. Based on the minimum and maximum values, the bar length is decided by Excel.We have many other features in the data bars that we can also use. We can choose to format cells with a value above a certain limit. We can choose to format cells only with a value between given conditions.So in this way, we can also choose to give some conditions to the data bars and make them more dynamic. Since we can add conditions to the data bars, this function belongs to the conditional formatting class.Like we have the positive and negative axis in a bar chart, we can also choose to show the negative and positive values in the same data chart. The only difference will be that there will only be one axis instead of two axes, which makes the data bars more useful in cases where we also have negative values.

Things to Remember About Data Bars in Excel

  • Data bars only work with quantitative data.Data bars are not an object to Excel.We can also use data bars for negative numbers.There is only one axis in the data bars.Data bars work best when there is less variation in the data values.

This article has been a guide to Data Bars in Excel. Here, we discuss adding data bars in Excel using conditional formatting, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • How to Create Clustered Bar Chart Excel?Conditional Formatting Based On Another CellHeaders in ExcelVLOOKUP Partial Match