Chart & Visualization Issues

How to Create an Excel Column Chart with Overlapping Non-Cumulative Values

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to display two values in a single column chart where the total column height is determined by the larger value rather than the sum of both values.

Product: Excel
Device & OS: not provided
Scenario: Comparing two metrics (like Target vs. Actual) for the same category within a single, overlapping visual column space.
Observed behavior: Standard clustered charts place data columns side-by-side, while stacked charts add the values together cumulatively. The goal is to overlap them without adding the underlying data.

Before you start

Ensure your dataset is organized with category labels in the first column and your two data series in adjacent columns. It is helpful to know which series contains the larger values so they do not completely block the smaller values in the final chart.

Solution 1Recommended

Use a Clustered Column Chart with 100% Series Overlap

By modifying a standard clustered column chart's series overlap to 100%, you can layer the columns over the exact same category position without summing their values.

This method is ideal for comparing 'Actual vs. Target' or 'Budget vs. Spend' metrics where both data points belong to the same category.

Insert a Clustered Column Chart

Highlight your data range, navigate to the 'Insert' tab on the ribbon, click the 'Insert Column or Bar Chart' icon, and select 'Clustered Column'.

Format the Data Series

Right-click on any of the data columns in your newly created chart and select 'Format Data Series' from the context menu to open the formatting pane.

Adjust Series Overlap

In the Format Data Series pane, locate the 'Series Options' icon (usually depicted as vertical bars). Find the 'Series Overlap' slider and change its value to 100%.

Modify Fill and Transparency

If the front column hides the back column, select the front data series. Go to the 'Fill & Line' section in the formatting pane and adjust the transparency slider to roughly 30-50%, or change the fill color to ensure both values are easily visible.

Check Series Order: If the larger value completely obscures the smaller one, you may need to reverse their drawing order. Right-click the chart, choose 'Select Data', and use the up/down arrows to change which series is drawn last (in front).

WPS Spreadsheet Solution

Create Overlapping Column Charts Easily in WPS Spreadsheet

WPS Spreadsheet offers an intuitive charting engine that allows you to easily adjust series overlap and customize visual comparisons without complex workarounds.

1. Insert the Chart: Open your dataset in WPS Spreadsheet, select the data, go to the 'Insert' tab, and choose 'Clustered Column' from the Chart menu.
2. Open Series Properties: Double-click on one of the data columns in the chart to automatically open the right-side properties panel.
3. Set 100% Overlap: Under 'Series Options', drag the 'Series Overlap' slider to 100% so the columns align directly on top of one another.
4. Adjust Visibility: Switch to the 'Fill & Line' tab in the same panel to change the transparency or color of the front series so the rear data point remains visible.
5. Reorder Series if Needed: Right-click the chart, select 'Select Data', and adjust the series order if your larger columns are covering the smaller ones.

Easily configure Series Overlap to 100% for non-cumulative visual comparisons.Fully compatible with Microsoft Excel (.xlsx) chart formats, ensuring seamless file sharing.Rich formatting capabilities including transparency, custom borders, and gradient fills.Lightweight, fast, and completely free to use for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my smaller value hidden behind the larger value in the overlapping chart?

This happens when the larger data series is plotted after the smaller one, physically covering it up on the chart. To fix this, right-click the chart, choose 'Select Data', and use the arrow buttons to change the series order so the smaller value is drawn last and appears in front.

Can I make the overlapping columns different widths?

Yes, but it requires placing one of the series on a Secondary Axis. Once one series is on a secondary axis, you can adjust the 'Gap Width' independently for each axis. Make the Gap Width larger for the secondary axis to make its column narrower, creating a highly visible 'bullet chart' effect.

Is an overlapping clustered chart the same as a stacked column chart?

No. A stacked column chart adds the values of your data series together cumulatively to form the total column height. An overlapping clustered chart (with 100% overlap) places the columns on top of each other at their true, individual values without adding them.

Excel & Data Problems