The article will show you how to change the Bar Chart color based on the category in Excel. The application of this can help you to visualize certain data in a convenient way. We use a Bar Chart to demonstrate the comparison among several data and how they change over time. It’s sometimes difficult to identify information about a particular set of data from a casual Bar Chart, even though we activate the Data Labels for that Bar Chart. It’s better to vary the Bar Chart color based on some values or formulas in the data. In that case, you can have a perfect visualization of your dataset. I’m going to show you the easiest possible ways to do that.
Excel Bar Chart Change Color Based on Category: 3 Ways
In the dataset, we have sales and profit information about some products. Here, the negative profit of a product refers to the loss in sales of that product.
1. Changing Excel Bar Chart Color Automatically Based on Category
If you don’t want to vary the color of the Bar Chart on your own, you can change it automatically by Excel commands and applications. Let’s go through the process below.
Steps:
- First, select the range that you want to demonstrate in the Bar Chart. In my case, I want to show the Sales of the Products in the Bar Chart. So, I selected B4:C11 and then go to Insert >> Chart >> 2D Bar Chart.
- Next, you will see a Bar Chart with all the bars in the same color.
- After that, select any of the bars in the chart and go to Format (Tab) >> Current Selection >> Format Selection.
- After that, the Format Data Series window will appear. Fix your Series Overlap and Gap Width according to your convenience and select the Fill Shape icon.
- Thereafter, check the Vary colors by point.
- Finally, you will see your Bar Chart filled with different colors for different data.
Thus you can change the Excel Bar Chart color automatically based on a category.
Read More: How to Color Bar Chart by Category in Excel
2. Changing Bar Chart Color Based on Value Type
You can also change the Bar Chart color based on the type of values in your data. In this section, I’ll show you this scenario with the Profit values in the dataset. Let’s discuss the process in the following description.
Steps:
- First, select the ranges B4:B11 and D4:D11
- After that, go to Insert >> Bar Chart >> 2D Bar Chart.
- Next, you will see the bars in the Bar Chart of the Profit value in the same color. The negative profit value or the loss amounts are situated on the left side of the chart.
- After that, open the Format Data Series window following the steps described in Section 1.
- Select the Shape Fill icon and check Invert if Negative and Vary colors by point.
- Finally, you will see that the loss amounts are in inverted color and the profit amount in various colors.
Thus you can change the Excel Bar Chart color based on a data type in the dataset.
Read More: Excel Bar Graph Color with Conditional Formatting
3. Bar Chart Color Changing Based on Formula Category
If you don’t want to use commands to change the color of your Bar Chart, you can categorize the data of your dataset by formulas. In this section, I’ll categorize the Sales values by some Sales Margin and make formulas with them. Let’s go through the process below for a better understanding.
Steps:
- First, set up Sales Margins. In this case, I divided the sales amount into 3 classes: less than or equal to 2500$, between 2500$ and 3000$, and greater than 3000$.
- Next, type the following formula in cell D5.
=IF(C5<=$B$14, C5, NA())
The formula uses the IF function and returns the value of the sales amount if it is less than or equal to 2500$, otherwise, it returns #N/A error (Value not Available Error). The advantage of this error for the chart is that we won’t get any bar for it. And the values we categorize by this formula will be in a certain bar color.
- After that, hit the ENTER button and you will see the #N/A error as the formula didn’t meet the logical test value of the IF function.
- Thereafter, use the Fill Handle to AutoFill the lower cells.
- Similarly, use the following formula in column E and fill the lower cells with it.
=IF(AND(C5>=$B$14,C5<=$B$15),C5,NA())
Here, we used the AND function to set the range [2500$, 3000$].
- Later, write the formula below to define the range greater than 3000$.
=IF(C5>$B$15,C5,NA())
Note: You can use any #N/A error handling Excel function like IFNA, ISNA (and even IFERROR) to get rid of these #N/A from the visualization.
- Now, select the range B4:B11 and D4:F11 simultaneously and go to Insert >> Bar Chart >> 2D Bar Chart.
- After that, you will see the Bar Chart based on the formula values. But the Bar Chart is not convenient here.
- To make the Bar Chart look more understandable, open the Format Data Series following the process of Section 1, go to Series Options, and set the Series Overlap and Gap Width according to your convenience.
- Finally, you will see the bars in the Bar Chart possess three different colors (Blue, Orange, and Grey) for the three categories that we set in the formulas. You can change the color of any set of bars manually if you want. Also, if you change the sales value of any product in the dataset, the corresponding bar will change its color accordingly.
Thus you can change the Excel Bar Chart color automatically based on the formula variation.
Read More: How to Sort Bar Chart in Descending Order in Excel
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
Suffice it to say, that you will learn some easy and effective ways to change the Excel Bar Chart color based on a category after going through this article. If you have any better methods, questions, or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
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
- 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
Get FREE Advanced Excel Exercises with Solutions!
