How to Make an Overlapping Bar Chart in Excel
Creating a basic bar chart in Excel is simple, but making a chart that truly tells a story - like showing your team's progress against a sales target - requires a little more finesse. An overlapping bar chart is one of the best ways to visualize this "actual vs. target" comparison, giving you an instant, clear picture of performance. This article will walk you through, step-by-step, how to create a professional-looking overlapping bar chart in Excel and share some tips to make your data even more insightful.
What Is an Overlapping Bar Chart and When Should You Use One?
An overlapping bar chart places one series of bars directly on top of another wider series, rather than side-by-side like in a standard clustered bar chart. This simple design trick immediately creates a visual hierarchy where one value is shown in the context of another.
The number one reason to use this chart is for comparing an actual performance value against a goal or target value. Think about a few common business scenarios:
Sales Performance: Comparing a sales rep's actual monthly sales against their quota.
Budget Tracking: Showing a department's actual expenses against their allocated budget.
Project Management: Visualizing hours worked on a task versus the estimated hours.
Marketing Campaigns: Tracking leads generated against a campaign goal.
In all these cases, the target acts as a benchmark - a container that you’re trying to fill. The overlapping effect perfectly communicates this relationship. It makes it instantly obvious whether someone has exceeded their goal (the inner bar extends beyond the outer bar), met it, or fallen short.
Getting Your Data Ready for an Overlapping Chart
Before you even open the chart menu in Excel, setting up your data correctly is the most important step. For an overlapping bar chart, you’ll want a simple table with three columns:
Category Labels: These will be the labels along your y-axis. They could be names of sales reps, months of the year, project names, or regions.
"Foreground" Series: This is typically your "Actual" data (e.g., actual sales, actual spend). This series will become the narrower, more prominent bar that sits on top.
"Background" Series: This is your "Target" data (e.g., sales quota, budget). This series will become the wider, background bar.
It's crucial to arrange your columns with the "foreground" data first, followed by the "background" data. Excel builds layers based on the column order, so placing your "Actual" data first ensures it will visually appear on top.
Step-by-Step Guide to Creating an Overlapping Bar Chart
With our data ready, we can now build the chart. We'll start with a standard chart and then adjust a few settings to create the overlapping effect.
Step 1: Insert a Standard 2-D Clustered Bar Chart
First, select your data range, including the headers (in our example, that's cell A1 through C5). Then, navigate to the Insert tab on the Excel ribbon. In the Charts section, click the "Insert Column or Bar Chart" icon and select 2-D Clustered Bar Chart.
At this stage, you'll have a standard bar chart with the "Actual Sales" and "Target Sales" bars displayed next to each other for each quarter. It's functional, but it doesn't give us the clear comparison we want.
Step 2: Move the Target Sales Series Onto the Secondary Axis
Here’s the first part of the trick. We need to tell Excel to layer one of the data series differently. By assigning one series to a “secondary axis,” we can manipulate its appearance independently of the first series.
Right-click on any bar in the chart and select Change Chart Type... from the dropdown menu.
This opens a new window. At the bottom, in the "Choose the chart type..." section, you'll see your two data series ("Actual Sales" and "Target Sales").
For the Target Sales series, check the box under the Secondary Axis column.
Leave both chart types as "Clustered Bar" and click OK.
Your chart will now look a bit strange. The bars are layered directly on top of each other, and you have two horizontal axes - one at the top and one at the bottom. Don't worry, this is exactly what's supposed to happen. We'll fix it in the next steps.
Step 3: Adjust the Overlap and Gap Width Settings
Now we create the overlapping visual effect by playing with the width of the bars.
First, let’s make the background (Target) bars wider.
Right-click on one of the Target Sales bars (the series you just moved to the secondary axis).
Select Format Data Series... from the menu.
The Format pane will open on the right. Under "Series Options," set the Series Overlap to 100%. This ensures the bars are perfectly aligned.
Next, adjust the Gap Width. This setting controls the amount of empty space between each group of bars. A smaller percentage makes the bars thicker. Set the Gap Width for the Target Sales series to a low value, like 100%.
Now, let’s make the foreground (Actual) bars narrower.
Click on one of the Actual Sales bars to select them.
In the same Format Data Series pane, set its Series Overlap to 100% as well to ensure perfect alignment.
Now, set its Gap Width to a high value, like 300%. Larger Gap Width values make the bars thinner.
Your chart should now have a clear overlapping effect. The thinner Actual Sales bars sit inside the thicker Target Sales bars. We're almost done!
Step 4: Clean Up and Format Your Chart
The chart is functional now, but a few final touches will make it look professional and easier to read.
Sync and Remove the Secondary Axis
One of the most important final steps is to remove the extra axis for a cleaner look:
Right-click on the upper axis (the secondary axis) and select Format Axis....
In the Format Axis pane, under Bounds, set the Maximum value to match the max value of your primary axis. For example, if your bottom axis max is $300,000, set the top axis maximum to the same value.
Then, you can simply click on the secondary axis and press Delete to remove it altogether.
Add a title and adjust the colors to finalize your chart:
Give Your Chart a Title: A clear descriptive title like "Sales Performance: Actual vs. Target" helps your audience understand what they're looking at.
Adjust Colors: Use contrasting colors. I like to use a medium grey for the target bar and a bright blue or green for the actual bar. This makes the actual numbers really pop.
Add Data Labels: For more clarity, add data labels to show the exact values. Right-click on the chart data series and select Add Data Labels. I suggest adding them only to your actual values, as adding it to everything can make the chart cluttered.
Advanced Tips for Your Overlapping Bar Chart
Once you've mastered the basics, here are a few ways to take your charts to the next level:
Create a Vertical Version
The exact same techniques apply to creating overlapping column charts as well. Just start with a Clustered Column chart instead of a Clustered Bar Chart in step 1, and follow the same steps. This is great for timestamped data, like showing monthly performance.
Use Formulas for Dynamic Colors
For a truly engaging dashboard, you can use Excel’s IF() function to conditionally color your “Actual” bars. For example, you could create a "Helper" column:
=IF(B2>C2, "Met Goal", "Missed Goal")
This formula would categorize if a value is greater or equal to the target. You'd have a similar formula for others. Plot both the "Met Goal" and "Missed Goal" in the chart instead of one series. Then you can color them differently, giving immediate visual feedback on whether a target was achieved.
Save it as a Chart Template
Once you’ve perfected the look of your chart, you don’t have to go through the process every time. Right-click on the chart and select Save as Template. This will add your custom chart to the Template category so you can reuse it with just a few clicks in the future.
Final Thoughts
Overlapping bar charts are a powerful tool for visualizing progress against goals. By using Excel's secondary axis in conjunction with strategically adjusted widths, you can transform any standard bar chart into a dynamic, insightful visualization that tells a story.
While manually building these charts in Excel requires some setup, especially when merging data from across platforms like Salesforce and Google Analytics, Graphed makes this process easier. We connect directly to your tools, so you don’t have to download CSV files. You can just ask us to "show me a bar chart comparing sales rep performance against their quotas" or more, and create live, up-to-date dashboards for instant and automated reporting.