How to Color Bar Chart by Category in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article shows how to color a Bar Chart by category in Excel. In some circumstances, you might need to make a Bar Chart with various colored bars based on the categories. Here, we will take you through 2 easy and convenient methods for creating a colored Bar Chart by category in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


2 Methods to Color a Bar Chart by Category in Excel

This article demonstrates two ways to color chart bars based on their values. If you wish to color chart bars differently depending on a condition, you must employ a workaround.

Suppose, we have a Mark List of Math of some students for a certain exam. Here, column B and column C includes the Name of the students and their Scores respectively.

Excel Bar Chart Color by Category

Now, we wanna classify those marks into some categories like 0-64, 65-79, 80-89, and 90-100. Also, wanna plot them in a Bar Chart and add color to chart bars according to the categories. Please follow the 2 methods below to get it down.


1. Using IF Function to Color a Bar Chart by Category in Excel

Our first method includes the usage of the IF function to classify the scores into different categories. Follow the steps below to get it right.

Steps:

  • Copy the dataset and paste it down into the worksheet IF Function.

Excel Bar Chart Color by Category Using IF Function

  • Now, go to cell B16 and write down the same as the image below. Here, we’ve mentioned the lower limit and upper limit of marks in our categories.

Excel Bar Chart Color by Category Using IF Function

The scores are classified into 4 categories in total. Hence, they are Below Average or simply Below Avg, Average, Good, and Excellent. The marks between 0 to 64 are introduced as Below Avg, 65 to 79 as Average, 80 to 89 as Good, and 90 to 100 as Excellent.

  • After that, create a table in the B22:F32 range. In this table, the marks will get distributed in their categories.

Excel Bar Chart Color by Category Using IF Function

  • At this moment, select cell C23 and write down the formula below. Then, press ENTER.

=IF(AND($C$17<=$C5,$C5<=$D$17),$C5,"")

Here, C5 represents the Score of Robin. On the other hand, C17 and D17 serve as the Min and Max values of the Category of Below Avg.

Formula Breakdown:
  • In the above formula, we used the AND function to insert two conditions simultaneously. The first condition is that the value in cell C5 would be less than or equal to the value in cell C17. That means the score of Robin would be less than or equal to the Min value of the Below Avg category. Another condition is that the value in cell C5 would be greater than or equal to the value in cell D17. Here, we used the dollar ($) sign for absolute cell reference.
  • These two conditions above are used as the logical test for the IF function. If the criteria are met, then it will show the value of cell C5 in cell C23. Otherwise, it will show a blank cell.

Excel Bar Chart Color by Category Using IF Function

  • Later, use the Fill Handle tool and drag it down to get the results of the lower cells.

Excel Bar Chart Color by Category Using IF Function

In cell C32 we got an output of 63 which is a score of Sandra. Because only she gets scores in the range of 0-64. As a result, she got herself in the Below Avg category.

  • Secondly, select cell D23 and type down the formula below. Then, press the ENTER key.
=IF(AND($C$18<=$C5,$C5<=$D$18),$C5,"")

This formula is also like the formula of our previous step. Just the difference is that here we used the cell reference of Min and Max value of Average category.

Excel Bar Chart Color by Category Using IF Function

  • Similarly, fill up the remaining two columns E and F.

Excel Bar Chart Color by Category Using IF Function

  • After that, select the whole table (B22:F32).
  • Now, go to the Insert tab.
  • Then, select Insert Column or Bar Chart > 2-D Clustered Bar as shown in the image below.

Excel Bar Chart Color by Category Using IF Function

  • However, we can see our Bar Chart colored based on the category.

Excel Bar Chart Color by Category Using IF Function

But, we can see that Robin is at the bottom of the Y-axis. To make it similar to the data table, we have to reverse the axis.

  • At first, right-click on the Y-axis.
  • Secondly, select the Format Axis option.

Excel Bar Chart Color by Category Using IF Function

  • It opens the Format Axis task pane. Now, click on the Axis Options icon.
  • Then, click on Axis Options to expand the menu.
  • Check the box of Categories in reverse order.

Excel Bar Chart Color by Category Using IF Function

  • Now, the chart looks in the same order as the data table.

Excel Bar Chart Color by Category Using IF Function

Read More: How to Change Bar Chart Color Based on Category in Excel (3 Methods)


Similar Readings


2. Utilizing Format Option

In this method, we will manually color each bar according to its category. Follow the steps below carefully.

Steps:

  • At first, select the whole data table in the B4:C14 range.
  • Now, go to the Insert tab.
  • Then, select Insert Column or Bar Chart > 2-D Clustered Bar.

Utilizing Format Option

  • Eventually, this creates the Bar Chart. Here, all the bars hold the same color.

Utilizing Format Option

  • Secondly, select any of the bars to add color to the Bar Chart.
  • Then, go to the Format tab.
  • After that select Current Selection group > Format Selection.

Utilizing Format Option

  • At this point, the Format Data Point task pane opens.
  • Now, click on Fill & Line icon.
  • After that, select your preferred color from the Fill Color list.

Utilizing Format Option

  • Our selected bar becomes colored with our preferred color.

Utilizing Format Option

  • Similarly, do this to each bar to get them colored as their category stated in method 1.

Utilizing Format Option

Note: This method is a time-consuming and lengthy process. It’s suitable for datasets with lesser rows. But it’s impossible to add color to each bar manually for a dataset of 100 rows.

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


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo