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

**Table of Contents**hide

## 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)**