I am trying to create a stacked waterfall chart in Excel that behaves this way when there are positive and negative values:
(taken from here: )
In Excel 2019, the closest I have been able to get is the following when using the built-in waterfall chart feature:
How can I achieve something like the former?
31 Answer
As mentioned in Jon Peltier's comment, this chart doesn't use Excel's waterfall chart-it is a stacked column chart and it takes some work to setup.
1) Data Organization
Part of the complexity for recreating this chart is the data organization. Addressing the groupings, blanks, and alternating reference points (base when adding and top when subtracting) is worthy of a whole different question and answer. For the purpose of creating this chart, I'm using static values organized as below:
If you're going to use this for data that changes with any frequency or with multiple data sources, you'll want to work out the calculation logic to accomodate those changes.
You'll need to calculate your values based upon your actual data, but as noted while most of the reference values for stacking are for the bottom of the segment, you'll need to add the subtracted (alpha- and beta-) values to align the top of those segments at the appropriate level (and subsequently increasing the transparent gap value below).
2) Stacked Column Chart
To start, add a Stacked Column Chart with the first 6 data series for
- Terminal (start and finish)
- Gap (the void space below the waterfall)
- Alpha +/- (both are necessary to differentiate adding/subtracting)
- Beta +/- (same as alpha)
Each series will have a minimum of 17 values (19 if you want a blank to start and end the chart). They're organized in pairs separated by blanks to create the clustered with gap appearance in the chart (too bad Excel doesn't have a stacked, cluster column chart).
With all six series added and basic formatting applied to match colors and show separations, your chart will look like this:
3) Combination Chart
Next, you need to add the horizontal line that ties series together. This will be a Line Chart element, so after adding the Connector Series change the chart type to Combination with the Connector Series on a secondary axis.
Make sure that the secondary axis is formatted so that Axis Position is On Tick Marks, this will line your points with the outer edge of the columns rather than centered on the column.
Also, make sure the secondary vertical axis has the same extents as the primary axis, otherwise your elements won't line up properly (without lots of extra math).
4) Basic Layout Complete
All of the data series are in position and generally finished format. The legend confirms each series.
5) Data Labels
Add the Label Series as another line chart element, as shown below. Then, format the lines and markers to be No Lines/No Markers so they're not visible. Finally, add data labels for the series. Format the data labels to use Value From Cells using the Label line in the data. Then change the Label Position to Above.
6) Axis Labels
Similar to step 5, add the Axis Line Series as a line chart element and add data labels, only using the Below position.
7) Final Product
Based upon all that work, your final product should resemble the sample. Feel free to format to your taste.
Final Thoughts
- The Up Up and Down Down columns visually double the values. Where as in Up Up, Alpha increases once by 150 and Beta increases once by 100 (and similarly, they both decrease twice in the Down Down value), each change is displayed twice (it's double wide).
- The order between Alpha and Beta changes. In Up Up and Down Down, since they're duplicated, they happen coincidently. In Up Down, Alpha is first. In Down Up, Beta is first. In those two cases, they're ordered by increase then decrease.
These contribute to potential visual misinterpretation and increased complexity in source data formatting. Here's an alternative to addresses both issues.
