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.
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 get 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.
- 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.
=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.
- 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 (3 Methods)
Similar Readings
- How to Make a 100 Percent Stacked Bar Chart in Excel (with Easy Steps)
- How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- Excel Add Line to Bar Chart (4 Ideal Examples)
- How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)
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 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 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
- How to Show Difference Between Two Series in Excel Bar Chart (2 Ways)
- How to Make a Bar Graph with Multiple Variables in Excel
- What is the Difference Between Bar Graph and Histogram?
- How to Make a Grouped Bar Chart in Excel (With Easy Steps)
- How to Make a Percentage Bar Graph in Excel (5 Methods)
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
