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.

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.

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

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

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

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.

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

• 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%.