How to Create a Stacked-Style Chart Without Adding Averages in Excel
Question details
The user needs to visually compare two average process times in a chart format similar to a stacked chart, but without the values being cumulatively added together.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a visual comparison of two independent averages using a single bar or column presentation.
- Observed behavior
- A standard stacked chart automatically sums the two averages, making the total column height incorrect for a direct comparison.
Ensure your data is organized in adjacent columns with clearly defined headers for the two average values you wish to compare.
Create an Overlapping Column Chart
Overlap two data series to create a visual comparison where the bars sit on top of each other without summing the values.
Since a true stacked chart will always add values together, adjusting a standard clustered column chart to overlap completely is the most effective way to compare independent averages without distorting the total height.
Highlight your data table containing the two averages, navigate to the Insert tab, and select Clustered Column from the Charts group.
Right-click on one of the data series bars in the chart and select Format Data Series. In the pane that appears, set the Series Overlap slider to 100%.
Go to the Fill & Line settings within the Format Data Series pane. Increase the transparency of the front bar so that the bar positioned behind it remains clearly visible.
Right-click the chart series and select Add Data Labels to explicitly show the exact average values, avoiding any visual ambiguity.
Apply Transparent Shapes to Chart Series
Use custom transparent shapes to replace standard chart bars for better visibility when one average is not consistently larger than the other.
Easily Create Custom Overlapping Charts in WPS Spreadsheet
WPS Spreadsheet provides intuitive and advanced chart formatting tools, allowing you to seamlessly adjust series overlap, order, and transparency to visually compare averages without unwanted data summing.
- 1. Insert a Clustered Column Chart: Select your data containing the two averages, go to the Insert tab, and click the Column Chart icon to insert a Clustered Column Chart.
- 2. Adjust Series Overlap to 100%: Double-click the bars to open the right-side formatting pane. Navigate to the Series Options and type 100% into the Series Overlap field.
- 3. Modify Fill Transparency: Switch to the Fill & Line tab in the same pane, and drag the transparency slider for the front series so the back series shines through.
Frequently Asked Questions
Why does a standard stacked chart add my averages together?
By definition, a stacked chart displays the cumulative total of the plotted series. It adds each subsequent data point on top of the previous one, which is why it cannot be used to simply overlap two independent values.
How can I ensure the smaller average is always in front?
You can change the plot order by right-clicking the chart and selecting 'Select Data'. In the dialog box, select a series and use the up or down arrows to move it. The series located at the bottom of the list is visually plotted in front.
Are data labels recommended for overlapping charts?
Yes. Adding data labels is highly recommended for overlapping charts because adjusting transparency or hiding portions of a bar can make it difficult to estimate the exact values visually.
