How to Make a Multicolored Bar Graph in Excel

Want to make certain bars in your Excel chart a different color? Highlighting a specific data point, like your top-performing month or a metric that needs attention, is a powerful way to guide your audience's focus. This tutorial will show you exactly how to create a multicolored bar graph in Excel, from a quick and easy manual change to a more advanced, automated method.

Why Use Multiple Colors in a Bar Graph?

Before jumping into the how-to, it's worth asking: why multicolored? A splash of strategic color does more than just make your chart look nice, it adds another layer of information. You can use different colors to:

• Emphasize a Key Data Point: Make your highest or lowest value stand out instantly.
• Categorize Data: If your bars represent different teams, products, or regions, color can help group them visually.
• Show Progress Towards a Goal: You can automatically color bars green if they meet a target and red if they fall short.
• Compare Time Periods: You might use one color for projected figures and another for actual results.

Used thoughtfully, color transforms a simple chart from a wall of data into a clear story.

Method 1: The Quick Manual Way (Recoloring One Bar at a Time)

This is the simplest method and perfect for one-off reports or presentations where you just need to highlight a single bar quickly. The key is in how you select the bar.

Step-by-Step Instructions:

1. Create Your Basic Bar Chart: If you haven't already, select your data and go to Insert > Recommended Charts or choose the bar chart icon. For this example, we have simple monthly sales data.
2. Select the Data Series: Click on any bar in your chart. You'll notice that all the bars become selected, indicated by small dots at their corners.
3. Isolate a Single Bar: Now, click a second time only on the specific bar you want to change. This is the crucial step. Now, only that individual bar should have selection dots at its corners, not the entire series.
4. Change the Color: With that single bar isolated, you can change its color.

Repeat this "click, then click again" process for any other bars you wish to recolor individually.

Pros & Cons of This Method

Pros: It’s incredibly fast and intuitive for making simple highlights. Cons: It’s manual. If you update your data and the chart reorders itself, the color doesn't move with the data. It stays on the bar's position (e.g., the third bar in the series), making it unsuitable for dynamic reports that change often.

Method 2: Splitting Data Into a Second Series

What if you want your colors to update automatically when the data changes? This method involves a bit of data prep but creates a much more robust and dynamic chart. We'll set it up so that any sales figure above a certain target is automatically colored green.

The Scenario

Imagine you have a monthly sales goal of $30,000. You want any month that surpasses this goal to be shown in green, while all others remain the standard blue.

Step 1: Set Up Helper Columns

This is where the magic happens. We need to create two new columns next to our original sales data. Let's call them "Below Target" and "Met Target."

Your original data might look like this:

Here’s how to set up the new table:

1. In cell C1, type "Below Target".
2. In cell D1, type "Met Target".
3. In a separate cell (say, F2), enter your target value: 30000. This makes it easy to change the target later without rewriting formulas.
4. In cell C2, enter this formula:
5. In cell D2, enter this formula:

Drag the fill handle from C2 and D2 down to apply the formulas to all rows. Your table will now look something like this:

Why the NA()?

This is a very important part of the trick! The NA() function tells Excel to return a #N/A error. When Excel tries to plot an #N/A value on a chart, it simply leaves a gap. If you used a 0 instead, you’d see tiny zero-height bars on your chart, which looks messy. Using NA() ensures a clean look.

Now, click on cells C2 and D2 and drag the fill handle down to apply the formulas to all your rows.

Step 2: Create a Stacked Bar Chart

Now we’ll build a chart from our helper columns.

1. Select the range of data, including the Month column and your two new helper columns (for example, A1:D5).
2. Go to the Insert tab and click the Bar Chart icon, then choose the Stacked Bar Chart option.

You’ll see a chart where some bars are blue and others are orange (or whatever default colors Excel chooses). But they appear in the same position for each month!

Step 3: Format the Chart

Just a few small tweaks are needed to perfect it.

1. Set Series Overlap: Right-click on any of the bars and select Format Data Series... In the pane, find the Series Overlap setting and move the slider all the way to 100%. This makes the bars for "Below Target" and "Met Target" sit directly on top of each other, making it look like a single bar that is colored based on the value.
2. Color Your Series:
3. Clean up the Legend: The default legend will say "Below Target" and "Met Target," which might be confusing. You can delete the legend or create a chart title like "Monthly Sales vs. $30k Target."

And you're done! You now have a dynamic, multicolored chart. If you change a sales value or the target in cell F2, the colors will update automatically.

Best Practices for Using Color

Building the chart is half the battle, using color effectively is the other half. Keep these tips in mind:

• Be Purposeful: Don’t add color just to be colorful. Every color should have a meaning. If two bars are the same color, your audience will assume they belong to the same category.
• Prioritize Accessibility: Roughly 8% of men have some form of color blindness. Avoid common problem combinations like red/green or blue/purple. Use tools or add labels to avoid this issue.
• Stay Consistent: If you use green to represent "good performance" on one chart, don’t use it to mean "marketing spend" on another. Keep color meanings consistent.
• Use Brand Colors: For a professional touch, use your company's brand colors. Most tools allow you to input specific Hex codes or RGB values.

Final Thoughts

You now know how to create multicolored bar graphs in Excel, from a simple manual click to an automated, formula-driven chart that dynamically updates based on your criteria. These techniques are fantastic for making your data easier to interpret at a glance, helping everyone from stakeholders to your team to understand performance instantly.

While mastering Excel is a great skill, we know that manually wrangling data and building reports can consume hours of your week. At Graphed, we automate the entire process by connecting directly to your data sources—like Shopify, Google Analytics, and Facebook Ads. Instead of building helper columns and charts piece by piece, you can simply ask for what you need in plain English and get a live, interactive dashboard in seconds. This lets you spend your time acting on insights, not just finding them.