Display stacked waterfall chart correctly

Take a look at Jon Peltier's Waterfall tutorial. This is not using the new waterfall chart type available in XL 2016, but the traditional chart

https://peltiertech.com/excel-waterf...bridge-charts/

You can add a column if you want to have visible stacked values in the chart.

What is upslide?

You can put any label on the data series. If you want the label to be a percentage, then you have to calculate that percentage somewhere and then point the label to use that cell as the label text.

If you need help with the chart, please upload a sample file with your data, so nobody has to eyeball your numbers and type it all in manually.

How much of Peltier's tutorial did you understand? Were there parts that you did not understand? When you tried it, what worked and what did not work?

Going with a stacked column chart approach, I:

Knowing that most of the work is in the spreadsheet, I started with the spreadsheet.
1) Enter my horizontal axis labels (2019E, DJ, less AG, 2019EPF) in A2:A5.
2) Enter my column labels (FT, AG, DJ) in B1:D1.
3) Enter values in B2:D5. Up to here, this looks like the top table in your picture.
Now I need to add the calculations for the stacked column chart
4) Enter data series names (base, FT, AG, DJ) in F1:I1.
5) Enter 0 in F2, Enter =ABS(B2) into G2 (the ABS() function will allow the column chart to handle both positive and negative entries like C4). Copy across into H2:I2.
6) In F3, I used the formula =SUM(G2:I2)+SUM(B3:C3). I copied G2:I2 and pasted into G3:I3.
7) Copy F3:I3 and paste into F4:F5. Enter 0 in F5.
That should be the table my stacked column chart will use.
8)Select A1:A5 ctrl then select F1:I5 -> Insert -> Stacked column chart
8a) My copy of Excel guessed (incorrectly) that I wanted series in rows, so I clicked on "switch row/column". If Excel really messes up your series definitions, fix them in the Select Data dialog.
9) Select the "base" data series and format it to be invisible.
Your chart should look a lot like the one in your picture. To get the data labels, compute the percentages in the spreadsheet, then add data labels to the chart using those values:
10) In K2, I entered =B2/SUM($B2:$D2) [note the mix of relative and absolute references]. Copy into L2:M2 and K5:M5.
11) Select the chart and add the data labels. Format the data labels to use values in cells and select the appropriate column for each data series. If there are unwanted 0% labels, clear those cells in the spreadsheet.

Peltier explains how this all works in his tutorial. Following these steps, you should be very close to the chart you show in your picture.

I cannot guess what Excel was thinking when you created the chart. As I explained in 8a, when Excel really messes up the series' definitions, you need to go into the Select Data dialog and fix it all up. I also noticed that you did something very different than what I was thinking in steps 4 to 7 -- especially leaving out the "base" column that allows the columns to "float". Starting with your current sample file, I:

4-7) Repeated steps 4 to 7 as I described above.
8) Selected the chart and pulled up the Select Data dialog.
8a) In the "horizontal category axis labels" section, I edited the horizontal category axis labels to be A2:A5.
8b) I edited each data series so that the series name would be row 1 (F1 for series 1, G1 for series 2, etc.), and the series values would be row 2 to row 5 (F2:F5 for the "base" series, G2:G5 for the "Fab Tech" series, etc.)
9-11) as listed.

Is that closer?

Did you understand the "base" calculation -- because I appear to have made a mistake in step 6. I should have included F2 in the first sum when entering F3. By changing F4 to be =SUM(F3:I3)+SUM(B4:C4) seems to have fixed that part of the chart.

To add the horizontal lines, I would add 3 more data series as line series.

12) Enter 3813 into O2 and O3
13) Enter +3813+1260 into P3 and P4
14) Enter +5073-1452 into Q4 and Q5. With more thought, we should be able to figure out these formulas so they are based on the values entered in B:D, but I did not think that hard (since most of this question is about generating the chart, I am assuming you can figure out the spreadsheet formulas. Let us know if you want some help in deriving these formulas).
15) Select O2:Q5 -> Copy -> Select Chart -> Paste to add the three new data series (they will add as more stacked columns.
16) Select one of the new data series -> Change chart type -> Line (with or without marker as you prefer). Repeat for each of the new data series.
17) Format the three line series as desired.

For the formulas for O2, P3, Q5 -- it seems like these formulas should be cumulative to this point summations. I tried =SUM($B$2:$D2) (note how I mixed relative and absolute referencing into this) in O2, then copied that into P3 and Q4, and that seemed correct. O3 was then =O2, P4 was =P3, Q5 was =Q4. That looked right.

I don't know why you could not get the percents to show. It should have been as simple as adding the data labels -> using the "values from cells" option -> then telling the data label to get its text from the appropriate range in the spreadsheet (K2:K5 for the Fabtech series, for example). Assuming that is what you tried, I am not sure why it would not let you do that (help file: https://support.office.com/en-us/art...2-f467c9f4eb2d ). Getting both $ and % to show should be the same thing -- put whatever text/values you want in the spreadsheet cells, then use the data labels -> values from cells option to make that text appear in the data label.

I do not see the extra stacked bar under Air divestiture, but I do see the extra stacked bar under 2019 pf. If you are understanding how this is working, this should be an easy fix, so I'll let you wrestle with the exact fix. Extra stacked bars in a stacked column chart mean that you have a non-zero value (Adding gridlines and the extra bar under 2019 pf looks like it is about $7000) where you should have a 0 value. It should be as simple as editing the source data for the chart so that the appropriate cells contain 0.