How to Add a Baseline or Line Series to an Excel Waterfall Chart
Question details
The user needs to add a second data series, such as a line graph or baseline, to an existing waterfall chart.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a customized combination chart to display a baseline or trendline over a waterfall financial or variance visualization.
- Observed behavior
- Excel's built-in Waterfall chart lacks support for multiple series or combination chart types. When a second series is added directly to the chart, it fails to appear on the visualization.
Before beginning, ensure your data is organized with helper columns calculating the start, positive, negative, and end values, as you will need to build this using a stacked column chart instead of the native waterfall tool.
Create a Custom Waterfall Chart using Stacked Columns
Since the native Waterfall chart does not allow combination charts, you can manually build a waterfall effect using a stacked column chart. This workaround lets you overlay a line series successfully.
By calculating transparent base columns and colored visible columns, you replicate the visual layout of a waterfall chart while retaining Excel's ability to add a secondary line chart.
Set up your dataset with helper columns (Base, Rise, Fall) next to your categories to calculate the floating points of the waterfall.
Highlight your newly structured data range, navigate to the 'Insert' tab on the ribbon, and click 'Insert Column or Bar Chart', then select 'Stacked Column'.
Right-click the bottom 'Base' data series on the chart, select 'Format Data Series', and change the Fill to 'No Fill' and Border to 'No Line' so it becomes invisible.
Right-click the chart area, choose 'Select Data', and click 'Add' to include your secondary data series (the baseline or trendline values).
Right-click the newly added series on the chart, select 'Change Series Chart Type', and change only that specific series to a 'Line' chart.
Create Advanced Combination Charts with WPS Spreadsheet
WPS Spreadsheet provides robust charting capabilities, allowing you to easily build custom combination charts and replicate complex waterfall effects using stacked columns.
- 1. Open data in WPS: Launch WPS Spreadsheet and open your dataset containing the helper columns for the custom waterfall.
- 2. Insert Stacked Column Chart: Select the data, go to the 'Insert' tab, click the 'Chart' icon, and choose 'Stacked Column' from the list.
- 3. Make base columns transparent: Double-click the bottom 'Base' series to open the right-side formatting pane, then select 'No Fill' under the Fill & Line options.
- 4. Change baseline to a line chart: Right-click your added baseline series, select 'Change Chart Type', and choose 'Line' to complete your custom waterfall combination chart.
Frequently Asked Questions
Why doesn't the second series appear when added to an Excel Waterfall chart?
Excel's native Waterfall chart type is strictly designed as a single-series visualization. It does not support multiple series or the combination chart feature, so any additional series added to it will remain hidden.
Can I use the 'Change Chart Type' option to fix my built-in waterfall chart?
No, the 'Change Chart Type' dialog does not offer a combination option if your primary chart is set to the built-in Waterfall type. You must start from scratch using a Stacked Column chart.
Do I need special add-ins to create a custom waterfall combo chart?
No special add-ins are required. You can achieve this entirely with native tools by structuring your data into helper columns and manipulating the fill colors of a standard stacked column chart.
