This guide will discuss how to add and customize data bars in Excel using conditional formatting.
Data bars are one of the conditional formatting options in Excel. And data bars allow us to insert or place a bar chart inside a cell. So the main difference between a data bar and a bar chart is that data bars are inserted inside of a cell instead of in a different location like a bar chart.
And bar charts are objects in Excel that are displayed in different locations. So data bars are inside a cell and do not object to Excel. Furthermore, data bars help to visually present values inside the cells.
So the larger the value inside a cell, the longer the bar line will be. And the lesser the value inside the cells, the shorter the bar will be. Then, it will make it easier to visualize the values and compares them with one another.
Additionally, it saves space inside the worksheet since data bars are inside a cell rather than in a different location.
Let’s take a sample scenario wherein we need to add a data bar in Excel.
Suppose you are a teacher who wants to keep track of the student’s scores on a test. Since you are handling many classes, you want to easily take a look and visualize each student’s scores. And that is why you want to add a data bar for the student’s scores.
So you went to the conditions formatting tool and simply added a data bar to represent each student’s scores. Also, you were able to customize the data bars by managing the rules for the data bars.
Great! Now let’s dive into a real example of how to add and customize data bars in Excel using conditional formatting.
A Real Example of Adding and Customizing Data Bars in Excel
Let’s say we have a class record containing the students’ names and their corresponding scores on a test. Since we want to visualize their scores better, we want to add data bars for each student. So our initial data set would look like this:
Fortunately, we can easily add data bars in Excel. So we can simply go to conditional formatting and add a data bar. But, we may also want to customize the data bars for different situations.
For instance, we can hide the numerical values of the cells when inserting the data bars. Additionally, we can also add data bars to represent negative and positive values. And another example would be adding data bars only for specific cells that meet a certain condition.
So data bars allow us to choose which cells to format with given a certain condition. Furthermore, there are many customizations we can choose in terms of the colors of our data bars. Aside from the data bars’ colors, we can also choose between a gradient fill or solid fill for our data bars.
But, how do data bars work? When we use data bars, the range of cells that we selected is treated as one set. Then, Excel will analyze the data by identifying the minimum and maximum values within the set. After, it will decide the bar length based on the whole data’s minimum and maximum values.
Additionally, data bars only support quantitative data. And there are dynamic since we can add conditions to the data bars. Finally, our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Finally, let’s move on and learn the steps on how to add and customize data bars in Excel using conditional formatting.
How to Add and Customize Data Bars in Excel
In this section, we will discuss the step-by-step process of how to add and customize data bars in Excel using conditional formatting.
1. Firstly, we need to select the range of cells where we want to insert the data bars. In this case, we will select the range B2:B10. Next, we will go to the Home tab and select Conditional Formatting.
2. Secondly, we must select Data Bars in the dropdown menu. Then, we will choose a color and fill style. In this case, we will select a blue gradient fill.
3. Afterward, the data bars will be inserted into the selected range of cells.
4. Now, let’s try to hide the numerical values and only display the data bars in the cells. To do this, we must select the range of cells containing the data bars. Then, go to the Home tab and click Conditional Formatting. Lastly, click Manage Rules in the dropdown menu.
5. In the Conditional Formatting Rules Manager window, click on Edit Rule.
6. Next, we will check the option to Show Bar Only. Lastly, click OK to apply the changes.
7. And tada! We have successfully hidden the values in our data bars.
8. Additionally, we can also display some negative values. For instance, we have negative values in our data set. So simply follow the same steps. Firstly, select the range of cells. Then, go to the Home tab and select Conditional Formatting.
Then, click Data Bars in the dropdown menu. Lastly, choose a color and style.
9. And tada! We have displayed negative and positive data bars.
10. Lastly, let’s learn how to add data bars on cells that meet a certain condition. For instance, we want to only add data bars on scores above 50. So simply select the range of cells and go to the Home tab. Then, select Conditional Formatting and click Manage Rules in the dropdown menu.
11. In the Conditional Formatting Rules Manager window, we will select Edit Rule.
12. Next, we will change the condition. Under the Minimum options, we will select Number under the Type dropdown menu. Then, we will type “50” as our Value. Lastly, click OK to apply the condition.
13. And tada! We have inserted data bars only on cells containing values larger than 50.
And that’s pretty much it! We have discussed how to add and customize data bars in Excel using conditional formatting.
Furthermore, we have explained the different ways we can customize data bars, such as hiding the values, negative data bars, and applying conditions. Now you can add and customize data bars in your work.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.