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

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.

## Excel Bar Chart Color by Category: 2 Methods

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 include the Names of the students and their Scores respectively.

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.

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

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 be distributed in their categories.

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

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. The difference is that here we used the cell reference of the Min and Max value of the Average category.

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

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

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

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.

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

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

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

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

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

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

• Our selected bar becomes colored with our preferred color.

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

Note: This method is a time-consuming and lengthy process. Itâ€™s suitable for datasets with fewer rows. However, itâ€™s impossible to add color to each bar manually for a dataset of 100 rows.

Download Practice Workbook

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

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

## Related Articles

<< Go Back to Excel Bar Chart |Â Excel ChartsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF