Stacked waterfall chart with horizontal line

Hello,

Let's troubleshoot.

Select the chart and turn on the secondary X axis: Chart Tools > Layout > Axes drop-down > Secondary Horizontal Axis > Show Default axis.

Now all the lines show. You can format the axis to not have a line, labels or ticks, to make it disappear, but let's not do that for a moment, because we still need it to get the lines sorted out.

I assume you want to draw a line from the first waterfall column to the next one to the right an so on. The line only has to cover the distance from the right edge of the left column to the left edge of the next column.

There are seven columns, but the secondary axis has eight axis markers. Format the secondary X axis to have a maximum value of 7 and a minimum value of 0.

Now the chart shows 6 lines at the tops or bottoms of six columns.

Next we need to shift these lines to the left, so they connect the columns: Here is how: Copy the cells from S13 to S29 and paste them into R13:R29. In cell S13 enter the formula =R13-0.5 and copy down to S29.

Now the lines connect the columns. But they are thick and blue and look a bit awkward.

The rest is formatting. The blue lines and the green and red columns don't quite match. Format each column series to have a border of 1 px width with a black line colour.

Format the line series to have 1 px width with a black line colour.

Now it's time to make the secondary X axis invisible. Format it to have no lines, not markers, no labels. Format the secondary Y axis in the same way, so it also disappears.

The result is attached.

Of course, you can use different colours or border thicknesses for the chart. With three colours for the bars (gray, green, red), you will need to compromise on the line colour to make it blend into the chart seamlessly. The trick is to use the same line colour and thickness for the column borders AND the connecting line series.

Ping me if you have questions.

cheers, teylyn