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.

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.

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

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.

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.

Read More: How to Change Bar Chart Color Based on Category in Excel

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.

Read More: Excel Bar Graph Color with Conditional Formatting

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

• Reverse Legend Order of Stacked Bar Chart in Excel
• How to Add Grand Total to Bar Chart in Excel
• How to Create Bar Chart with Error Bars in Excel
• How to Sort Bar Chart in Descending Order in Excel
• Excel Add Line to Bar Chart
• How to Add Horizontal Line to Bar Chart in Excel
• How to Add Vertical Line to Excel Bar Chart
• How to Create Bar Chart with Target Line in Excel
• Excel Bar Chart with Line Overlay

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