Sometimes a seemingly simple visual can be difficult to convey with a built-in Excel chart. I present here such an example. For instance, I will describe how and why to create an Overlapping Column Chart, aka 100% Overlapping Column Chart in Excel. It can also be turned into an Overlapping Bar chart.
Say, we have data on monthly visits to a site over a year. Each month’s visits are broken up into total visits and unique visits where Visits shows the number of visits for the month and Unique visits is the count of unique IP address visits (if any) for the month. The sample data is below:

OBJECTIVE: What I want is to draw a column chart with the height of each column to be the total Visits, and a portion of that column (in a different color) showing the Unique visits value (if any). And months shown in x-axis.
THE CHALLENGE: The Clustered Column chart in Excel will show this as two separate bars (Visits, Unique) side-by-side for each month. I want them both within the same columns.
And the Stacked Column chart will show one column per month, but the height of the column is not the Visits, rather it becomes Visits + Unique which skews the perspective.
For example, for 8K visits in January where all of them were unique visitors, this chart would show 8,000 for Visits, and another 8,000 on top of that for Unique giving the impression that there
were 16,000 visitors…which is incorrect. Similarly, for August, it seems to show 10K visitors, where in reality there were 6K visitors out of which 4K were unique! I want to show the Total without that distortion, and show what portion of it is Unique (if any) for the months.
Let’s see what the default Clustered and Stacked column charts look like:


As you see, Chart 1 shows two bars per period. It means having to compare both values side-by-side each time; additionally, it’s more cluttered.
In Chart 2, the values from each series are stacked on top of one another making the y-axis totals go up by the sum. This means the viewers have to derive the value of each portion by deducting from the total for each period.
THE SOLUTION: The solution is to tweak a clustered column chart. We start with a Clustered Column Chart, but then format any of the series’ Series Overlap attribute to 100% instead of the default. This is not a standard chart type in Excel, but it’s a useful way to compare two sets of data (like total Visits and Unique in this example) within the same columns.
STEPS:
- Select all data (including header), Insert->Clustered Column chart.
- After clustered chart is placed, there will be two columns for each month side by side (as in Chart 1).
But we want both in one column with different colors and the column height should be the value of Total visits column value. - Click on any of the columns on the Clustered column chart to select the series (all its bars), right-click->Format Data Series. On the right pane, set Series Overlap=100% and Gap Width=5% (or something tighter than default).
See Overlapping Column Chart and Overlapping Bar Chart charts below as the final solutions.


TIP: In the bar chart, by default the order will be Dec, Nov…Jan from top to bottom. To reverse the order: click on the Y-axis label, Right-click->Format Axis: [x] Categories in reverse order.
I hope this was helpful. Thanks for reading! Explore the site for many more tips and techniques (Excel, Python, and more).
Related: