Tanjim Reza
Excel bar charts are a convenient tool for data interpretation. You can summarize and visualize vast datasets using this chart. Another cool thing about bar graphs is that you can change the graph color using conditional formatting. In this article, I will show you 3 practical examples of Excel bar graph color using conditional formatting.
Download Practice Workbook
You can download our practice workbook from here for free!
3 Examples of Excel Bar Graph Color with Conditional Formatting
Suppose, you have a mark sheet of 12 students. Now, you want to visualize those in a bar graph. Here, upon conditioning, you can customize the Excel bar graph color. Follow the 3 useful examples described below.
Note:
Whenever we will use the term conditional formatting in this article, we won’t mean the built-in Excel feature. Rather, we will refer to formatting the graphs by applying a set of conditions.
1. Change Excel Bar Graph Color by Applying a Set of Conditions
You can set the bar graph color according to the mark criteria using the following steps.
📌 Steps:
- At the very beginning, create 3 columns named Bad, Medium, and Good where the following marks will be inserted according to the mark criteria.
- Now, select the D5 cell and insert the following formula which involves the IF function. Subsequently, press the Enter button.
=IF(C5<60,C5,"")
- At this time, place your cursor in the bottom right position of your cell and drag the fill handle downward upon its appearance.
- As a result, the same formula will be copied to all the cells below, and the marks that are less than 60 will be shown in this column.
- Similarly, select the E5 cell and insert the following formula using the IF and AND functions. Next, press the Enter button.
=IF(AND(C5>=60,C5<80),C5,"")
🔎 Formula Breakdown:
=IF(AND(C5>=60,C5<80),C5,””)
It checks if the value of the C5 cell is less than 80, but greater than or equal to 60. If the test is true, it will show the value of the C5 cell. Or else, it will show a blank.
Result: Blank Cell
- After this, place your cursor in the bottom right position of the cell. Following, a fill handle will appear. Now, drag it down to copy the formula for all the cells below.
- As a result, all the marks between 60 and 80 will be inserted in this column.
- Last but not least, to find the good criteria, click on the F5 cell and write the formula below. Following, press the Enter button.
=IF(C5>=80,C5,"")
- Just like before, place your cursor in the bottom right position of this cell and drag the fill handle downward when it appears.
- Consequently, you will get all the good marks in this column.
- Now, select the D5:F16 cells >> go to the Insert tab >> Insert Column or Bar Chart tool >> Clustered Bar option.
- As a result, you can see a bar chart has appeared. But it is not in the right format. That is why right-click on the chart and choose the Select Data… option from the context menu.
- As a result, the Select Data Source window will appear. Remove the present legend entry. To do this, click on the 49 78 entry and click on the Remove button.
- Following, click on the Add button from the same window.
- Consequently, the Edit Series window will appear. Here, write Bad at the Series name: text box. Subsequently, referring to the D5:D16 cells of this worksheet in the Series values: text box. Last but not least, click on the OK button.
- As a result, the Bad column is added as a Bar graph in your Excel file now.
- Similarly, add two more legend entries. One for the Medium column and the other one for the Good column. And, finally, click on the OK button.
- At this time, you can see a bar graph with all the bars in three different colors according to their criteria.
- Now, for a better look, click on the Chart Elements tool of the chart. Subsequently, untick the Axes and Gridlines option. Besides, tick the Data Labels option.
- Next, click on the Chart Elements tool >> rightward arrow beside the Legend option >> choose the Bottom option.
- Now, select any of the Good column bars from the chart and right-click on it. Following, select the Format Data Series… option from the context menu.
- As a result, the Format Data Series ribbon will appear on the right side. Following, go to the Series Options group. Next, choose the Series Overlap option as 100% and the Gap Width option as 50%.
- Now, go to the Fill & Line group from this ribbon and choose your desired color for the Good column. We have chosen Green, Accent 6 here.
- Similarly, choose the Orange color or any of your desired colors for the Medium column’s bars. Last but not least, choose the Red color or any of your desired colors for the Bad column’s bars similarly.
So, you will see that every student’s mark is now in a colored bar. And you have customized this bar graph color using conditional formatting successfully. And for example, the result should look like this.
Read More: How to Change Bar Chart Color Based on Category in Excel (3 Methods)
2. Customize Bar Graph Color with Conditional Formatting to Display Deviation of Data
Now, say you want to calculate the average marks of the students and show a bar graph according to the deviations from the average mark of each student. Follow the steps below to do this.
📌 Steps:
- First and foremost, you need to calculate the average mark. For this merge the cells D5:D16. Following, click on the cell and write the formula below involving the AVERAGE and the ROUND functions. Subsequently, press the Enter button.
=ROUND(AVERAGE(C5:C16),0)
🔎 Formula Breakdown:
=ROUND(AVERAGE(C5:C16),0)
This returns the average of C5 to C16 cells and rounds it to zero decimal place.
Result: 76
- Next, you need to calculate the difference between each student’s mark with the average mark. For this select the E5 cell and insert the following formula. Subsequently, press the Enter button.
=C5-$D$5
- At this time, place your cursor in the bottom right position of the cell and drag the fill handle downward when it appears.
- As a result, you will get each student’s marks deviations from the average mark.
- Next, select the E5:E16 cells >> go to the Insert tab >> Insert Column or Bar Chart tool >> Clustered Bar option.
- As a result, the bar graph will appear. Now, click on the Chart Elements tool for this graph. Following, untick all the options without only the Data Labels option.
- At this time, the bar graph will be more attractive than before. Now, right-click on any of the bars of the graph. Following, choose the Format Data Series… option from the context menu.
- As a result, the Format Data Series ribbon will appear on the right side. Following, go to the Series Options group. Subsequently, make the Series Overlap option 100% and the Gap Width option 50%.
- Afterward, go to the Fill & Line group. Next, tick on the option Invert if negative. Subsequently, choose the first Fill Color option as Green and the second Fill Color option as Red. It means, that a positive difference will show the green fill and a negative difference will show the red fill.
Finally, you can see the mark difference from the average mark bar graph in different colors.
Read More: How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)
Similar Readings
- How to Make a Simple Bar Graph in Excel (with Easy Steps)
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- Excel Stacked Bar Chart with Subcategories (2 Examples)
- How to Add Horizontal Line to Bar Chart in Excel (3 Easy Ways)
- How to Show Variance in Excel Bar Chart (with Easy Steps)
3. Highlight Maximum Value by Bar Graph Color
Moreover, you can also highlight the maximum value of the obtained marks in the bar graph using conditional formatting. Go through the following steps to do so.
📌 Steps:
- Initially, you will need to find the maximum mark of the students. To find this, click on the D5 cell and insert the following formula which includes the MAX function. Subsequently, press the Enter button.
=IF(C5=MAX($C$5:$C$16),C5,"")
🔎 Formula Breakdown:
=IF(C5=MAX($C$5:$C$16),C5,””)
This checks if the C5 cell’s value is the maximum in the range of C5:C16 cells. If it is true, it will show the C5 cell’s value. Else, it will return a blank cell.
Result: Blank Cell
- Now, place your cursor in the bottom right position of your cell. Following, the fill handle will appear. Drag it downward to copy the formula for all the cells below.
- As a result, you can check every mark for the maximum mark. Finally, you can see the maximum mark is 99 in the D13 cell.
- Now, follow the first example to insert a bar graph for the marks of the students. Next, right-click on the bar graph and choose the Select Data… option from the context menu.
- As a result, the Select Data Source window will appear. Afterward, click on the Add button.
- Consequently, the Edit Series window will appear. Write Maximum in the Series name: text box. Following, refer to the D5:D16 cells in the Series values: text box. Finally, click on the Ok button.
- Afterward, you will be back to the Select Data Source window. Following, click on the OK button.
- Now, right-click on the maximum mark bar and choose the Format Data Series… option from the context menu.
- As a result, the Format Data Series ribbon will appear on the right side of the worksheet. Following, go to the Fill & Line group. Choose the Fill Color option as your desired color for the maximum mark. Here, we have chosen the Blue, Accent 5.
Thus, you can see you have applied conditional formatting to customize the Excel bar graph color. For instance, the outcome should look like this.
Read More: How to Color Bar Chart by Category in Excel (2 Easy Methods)
Conclusion
To sum up, in this article, I have shown 3 practical examples to customize Excel bar graph color with conditional formatting. I would suggest you go through the full article carefully and practice thoroughly with our practice workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy for many more articles like this. Thank you!
Related Articles
- How to Add Vertical Line to Excel Bar Chart (3 Quick Ways)
- How to Make a 100 Percent Stacked Bar Chart in Excel (with Easy Steps)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- How to Show Number and Percentage in Excel Bar Chart
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
- Excel Add Line to Bar Chart (4 Ideal Examples)
