Excel Bar Graph Color with Conditional Formatting (3 Suitable Examples)

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.

Students' Marks to Customize Excel Bar Graph Color with Conditional Formatting

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,"")

Find Bad Marks to Customize Excel Bar Graph Color with Conditional Formatting

  • At this time, place your cursor in the bottom right position of your cell and drag the fill handle downward upon its appearance.

Drag Fill Handle to Copy Same Formula

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

Al Bad Marks in a Single 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,"")

Find the Medium Marks to Customize Bar Graph Color with Conditional Formatting

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

Drag the Fill Handle to Copy Same Formula

  • As a result, all the marks between 60 and 80 will be inserted in this column.

All Medium Marks in a Single 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,"")

Find Good Marks to Customize the Excel Bar Graph Color with Conditional Formatting

  • Just like before, place your cursor in the bottom right position of this cell and drag the fill handle downward when it appears.

Drag Fill Handle to Copy Same Formula

  • Consequently, you will get all the good marks in this column.

All Good Marks in the Good Column

  • Now, select the D5:F16 cells >> go to the Insert tab >> Insert Column or Bar Chart tool >> Clustered Bar option.

Insert a Bar Chart

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

Select Data to Customize the Bar Chart

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

Select Data Source Window

  • Following, click on the Add button from the same window.

Access the Add Button to Add Data Series

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

Add the Bad Criteria to Customize the Excel Bar Graph Color with Conditional Formatting

  • As a result, the Bad column is added as a Bar graph in your Excel file now.

One Column Added As Data Series in the Graph

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

Add Other Two Criteria to Customize Bar Graph Color with Conditional Formatting

  • At this time, you can see a bar graph with all the bars in three different colors according to their criteria.

Excel Bar Graph with All 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.

Access Chart Elements for A Better Representation of the Graph

  • Next, click on the Chart Elements tool >> rightward arrow beside the Legend option >> choose the Bottom option.

Add Data Labels

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

Select Format Data Series to Access the Format Data Series Options

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

Change Series Overlap and Gap Width

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

Choose Excel Bar Graph Color with Conditional Formatting

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

Excel Bar Graph Color with Conditional Formatting

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)

Find the Average Mark of the Students

🔎 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

Find the Deviations from the Average Mark to Customize the Bar Graph Color with Conditional Formatting

  • At this time, place your cursor in the bottom right position of the cell and drag the fill handle downward when it appears.

Drag Fill Handle to Copy Formula

  • As a result, you will get each student’s marks deviations from the average mark.

All Students' Marks Deviations from Average Mark

  • Next, select the E5:E16 cells >> go to the Insert tab >> Insert Column or Bar Chart tool >> Clustered Bar option.

Insert a Bar Chart with Deviations to Customize Bar Graph Color with Conditional Formatting

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

Access the Chart Elements Tool to Get Better Visualization

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

Access the Format Data Series Options

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

Change Series Overlap and Gap Width to Customize Bar Graph Color with Conditional Formatting

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

Access Fill & Line Group to Customize the Excel Bar Graph Color with Conditional Formatting

Finally, you can see the mark difference from the average mark bar graph in different colors.

Excel Bar Graph Color with Conditional Formatting

Read More: How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)


Similar Readings


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,"")

Find the Maximum Mark

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

Drag Fill Handle to Copy Formula

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

Maximum Mark Found

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

Access the Select Data Series Window of Bar Graph with All Criteria

  • As a result, the Select Data Source window will appear. Afterward, click on the Add button.

Select Data Source Window

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

Add the Maximum Data in Bar Graph

  • Afterward, you will be back to the Select Data Source window. Following, click on the OK button.

Finalize the Bar Graph to Customize Bar Graph Color with Conditional Formatting

  • Now, right-click on the maximum mark bar and choose the Format Data Series… option from the context menu.

Access the Format Data Series Window to Customize Bar Graph Color with Conditional Formatting

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

Choose Maximum Mark's Color to Customize Excel Bar Graph Color with Conditional Formatting

Thus, you can see you have applied conditional formatting to customize the Excel bar graph color. For instance, the outcome should look like this.

Excel Bar Graph Color with Conditional Formatting

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

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo