How to do Conditional Formatting on Stacked Bar Chart in Excel

This guide will discuss how to do conditional formatting on a stacked bar chart in Excel

In fact, there is no function or tool to automate this task. Thus, we will need to perform a few things manually to achieve conditional formatting on a stacked bar chart in Excel.

A stacked bar chart is a type of chart available in Excel. It also shows different values across different categories. Each bar is split into sub-segments on top or stacked together. 

Conditional formatting is commonly used to emphasize or highlight specific data. It is a feature that allows us to apply specific formatting like color to cells that fit our criteria. 

Although it is easy to perform conditional formatting on cells, it can be difficult to perform on a bar chart. There is no built-in function or feature in Excel that will make it easy. 

So we will manually perform conditional formatting on a stacked bar chart. There are two ways to do this task. First, we can change the data source by adding one more table. Then, we will use that as our data source for the stacked bar chart. 

On the other hand, we can manually create a stacked bar chart in which we can easily use different colors for each stack. Both ways will require us to create multiple tables for our data. 

Let’s take an example.

Suppose you want are doing a monthly sales report. And you want to highlight the bars that are less than 100, meaning the products that sold less than 100 units. So you manually performed conditional formatting by creating a separate table for the values less than 100. 

And doing that would allow you to have a separate bar for those products for which you can use a different color like red to highlight it. 

Awesome! Let’s move on to a real example of performing conditional formatting on a stacked bar chart in Excel. 

 

A Real Example of Conditional Formatting on a Stacked Bar Chart in Excel

Let’s take a look at our sample data. For example, we have a table with the number of units sold per product.Sample data set

 

First, let’s try performing conditional formatting on a stacked bar chart. We can do this by creating a new table. In this case, we want to highlight products that sold less than 100 units. So we create a separate column for those values. 

Then, we can choose the stacked bar chart in the charts available in Excel. Make sure also to use the modified table as the data source. Now we can format it however we want. In this case, we will turn the bars with values less than 100 into the color red to emphasize them.Final output after conditional formatting

 

Great! Now let’s try the second conditional formatting method on a stacked bar chart. And this time, we will manually create a stacked bar chart. Similarly, we need to create additional columns to separate our data. 

Then, we will use conditional formatting to create a stacked bar chart in the cells. It would look something like this:Final output after manually creating stacked bar chart

 

You can make your own copy of the spreadsheet above using the link attached below. 

Now let’s learn the steps of how to do conditional formatting on a stacked bar chart in Excel. 

 

How to do Conditional Formatting on Stacked Bar Chart in Excel

In this section, we will explain the process of how to do conditional formatting on a stacked bar chart in excel using two methods. 

1. First, create a new table containing two columns for the units sold. So we will separate the values that are >=100 and >100. In cell B9, input the formula “=IF(B2>=100;B2;””)”. Then, drag down to copy the formula to the entire column and type “0” in the blank cells.Creating the new data source

2. Second, input the formula “=IF(B2<100;B2;””)” in cell C9. Similarly, drag down to copy and type “0” in the blank cells.Getting the data with values less than 100

3. Third, select A9:C13. Click to Insert and go to the Charts. From there, select the Stacked Column.Conditional Formatting on Stacked Bar Chart in Excel

4. Once the chart appears, right-click anywhere and click Format.Formatting the chart

5. In the Chart, you can format the chart based on your preference. But we will go to Series <100. Since we want to highlight the products sold less than 100, we will change the fill color to red.Conditional Formatting on Stacked Bar Chart in Excel

6. And tada! We manually did conditional formatting on a stacked bar chart.Conditional Formatting on Stacked Bar Chart in Excel

7. Next, let’s try doing conditional formatting by making our own stacked bar chart. For instance, we are making a quarterly report, but we want to highlight the data in August. 

Similarly, we will create another table. And we need to calculate the sales percentage of the product each month. To do this, input the formula “=B3/SUM($B$3:$D$3))*100” in cell F3. Conditional Formatting on Stacked Bar Chart in Excel

8. Then, drag the formula to the entire table to apply the same formula. So this will return the percentage equivalent to our data in the original table.Conditional Formatting on Stacked Bar Chart in Excel

9. Next, select 100 columns next to the table. In the Home tab, click the three dots. Then, click Format and Column Width.Conditional Formatting on Stacked Bar Chart in Excel

10. Then, change the Value to “0.1” and click OK.Resizing cell width

11. Make sure the columns are still selected. After, go to Conditional Formatting and click New Rule.Conditional Formatting on Stacked Bar Chart in Excel

12. Then, check the If true then stop option. In Rule Type, select Formula. Under Format all values where this formula is true, input the formula “COLUMNS($J$3:J3)<=$F3”.Conditional Formatting on Stacked Bar Chart in Excel

13. Then, you can change the fill to any color you want. Finally, click Done to apply the conditional formatting.Conditional Formatting on Stacked Bar Chart in Excel

14. Repeat steps 12 and 13 to add two more new rules. In the second rule, follow the same steps but input the formula “=ANDCOLUMNS($J$3:J3)<=$F3;COLUMNS($J$3:J3)<=($F3+G3)”. 

Again, follow the same steps for the third and last time. But this time, input the formula “=ANDCOLUMNS($J$3:J3)>($F3+G3);COLUMNS($J$3:J3)<=100”. Now we have three conditional formatting rules.Conditional Formatting on Stacked Bar Chart in Excel

15. Since we want to highlight the month of August, let’s change the color of the first two conditional formatting to shades of gray and keep the bright color for August. Additionally, we can format the borders of each product to separate each bar.Conditional Formatting on Stacked Bar Chart in Excel

16. And tada! We have manually created a stacked bar chart and applied conditional formatting to it in Excel.Conditional Formatting on Stacked Bar Chart in Excel

 

That’s pretty much it! You have successfully learned two methods to do conditional formatting on a stacked bar chart in Excel. Now you can apply any of the two methods whenever you need to highlight a bar in your chart. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like