Waterfall with Secondary Axis...

Many of us prefer that files be attached to the forum post rather than go to 3rd party sites to download. If you are having trouble uploading a sample file, be sure to click on the reply to thread or go advanced buttons (to bring up the main post editor), then find the "manage attachments" link beneath the post editing field that will bring up the working file uploader.

Assuming you are still working in Excel 2010 as your profile indicates, I would assume that your existing waterfall chart is really a stacked column chart. Your picture shows a combination of two stacked column charts -- the main waterfall chart, and then a stacked column in the background that fills the chart area with the three classifications. Without seeing what you have tried, I would expect to add the three additional data series, move them to the secondary axis, then format them to be mostly transparent and have no gap between columns, and any other formats desired.

If you have upgraded to a newer version of Excel and are using the built in waterfall chart type, I cannot help. My impression from other discussions here suggests that the built in waterfall chart type is very limited in the formatting options available -- so that it may not be possible to have a colored background like your picture shows. If that is the case, you may need to create the waterfall chart in the old fashioned way -- as a stacked column chart, which should allow you the flexibility to add a colored background like in your picture.

In order to help with the specifics, we will need to be sure of your Excel version, understand some of what you have tried, and be able to see your sample file.

If you are now using Excel 2019, it would help us help you if you would edit your forum profile to reflect the change. I know the forum limits links, but it does not limit file uploads for new users. I also know that many of us, while we are willing to download samples direct from the forum, we are not willing to download sample files from outside of the forum. We cannot require you to do these, but editing your profile and uploading your sample file to the forum will increase the number of users here who can help you and improve the quality of the help you receive. It is up to you how much you want to help us help you.

From there, we need to determine if you are using the built in waterfall chart type or if you are using a stacked column chart type.

If you are using a waterfall chart type, how much will Excel allow you to do. Can you add additional data series to the chart? Can you make a waterfall + stacked column chart? I expect that Excel will block you at one of those steps with a message like "cannot add data series to some chart types" or "some chart types cannot be combined with others." If it will allow you to make a waterfall+stacked column combination chart type, it should then be a matter of formatting the stacked column part of the combination chart to give the desired background effect.

If you are already using a stacked column chart type, or determine that you cannot combine waterfall + stacked column, then it should be a simple matter of adding the three additional data series for the background bands, moving them to the secondary axis, then formatting those three data series as desired.

@MadForPlaid

What you are after is impossible to do with single waterfall chart in Excel.

Waterfall chart does not allow control over series chart type.
You can read about advantages and disadvantage of this chart type in link below.
https://www.xelplus.com/waterfall-ch...s-limitations/

You could emulate it by overlaying another chart on top. But I'd advise against this method as it's near impossible to accurately match data points.

Perhaps you could create water fall chart using old method and then add secondary axis for bands.
https://peltiertech.com/excel-waterf...bridge-charts/

Did you follow the instructions I gave in post #2 for uploading attachments (click on go advanced or reply to thread to bring up the post editing window, then find the "manage attachments" link beneath the editing field)? Some of the "shortcuts" for uploading files do not work on this forum, and this is the only reliable method I know of to upload files on this forum.

At this point, it looks you are trying a regular column chart for the waterfall chart and a stacked area chart for the background bands. The stacked area chart will work okay for the banding, but your column chart is not going to work for the waterfall chart. I also notice that, where you example picture only shows values above 0, your example data seems to include the need to go both above and below 0 -- which is an important consideration in building the waterfall chart.

In a broad overview, here's how I would build this waterfall chart:

1) Peltier's tutorial on using a stacked area to create the background banding (https://peltiertech.com/excel-charts...izontal-bands/ ) claims that it is slightly easier to start with the background first, so I would start by building the banded background. His tutorial includes instructions for starting both ways, so choose the approach you want to use. At this point I will assume you can create the background banding from the stacked area chart following the tutorial.

2) When you come to the steps where he adds the real data to the chart, switch over to Peltier's tutorial for the waterfall chart (https://peltiertech.com/excel-waterf...bridge-charts/ the section using line chart with up-down bars) or xelplus's tutorial for the same technique (https://www.xelplus.com/easiest-wate...-from-scratch/ ) and build the waterfall chart. Note that there is quite a bit more to this than just selecting the data in column R and inserting a chart. You will need to add a few helper columns to the spreadsheet and then use those columns to build the chart. The process is pretty well explained in the tutorials, so be sure to read and follow the tutorials. For now I will assume that you are able to follow the tutorials to get the waterfall chart.

At this point, I assume that you will be able to follow the tutorials to get these charts combined together in something like your picture. If you have trouble, let us know where you get stuck and we'll help as best we can.

It looks like you have been pretty successful -- I see a column chart with 4 colored bands in the background:

1) A bottom white band that goes from 0 to 77000 on the primary vertical axis as specified in Z26:AA26 (from -160000 to -6000 on the secondary vertical axis),
2) a lavender band that extends from 77000 to 116000 on the primary vertical axis as specified by the 39000 in Z28:AA28 (from -6000 to 32000 on the secondary vertical axis),
3) an orange band the extends from 116000 to 138000 on the primary vertical axis as specified by the 22000 in Z29:AA29 (from 32000 to 116000 on the secondary vertical axis),
4) a yellow band that extends from 138000 (116000 on the secondary axis) to the top of the chart where the primary horizontal axis is located.

The question in your latest post suggests that you want something different, but I am not sure what you want to be different. I will draw your attention to a couple of things to suggest things to change and/or offer a way for you to explain what you want to be different.

A) I note that Peltier suggests changing the plot area's fill color to add color above and below the bands defined by chart. If you want a different color for the bottom band, select the plot area and change the fill color to whatever you want for the bottom band (the band that will extend from the bottom of the chart to the value entered in Z26:AA26).
B) It is not clear to me exactly where the bands should position themselves on the secondary axis, but having the bottom band end at -6000 on the secondary axis seems wrong somehow (though I don't really know -- it could be right). Assuming that the position of the bands is part of the question, I would point out that positioning the bands is a combination of choosing the correct axis scale values for the primary axis (maximum, minimum, major unit, minor unit) and entering appropriate values into Z26:AA29 of the spreadsheet. Often, I will format the primary axis so that it has the same axis scale as the secondary axis. In this case, maximum is 160000, minimum is -160000, and major unit is 50000. This way, whatever values I enter into the spreadsheet will be at the same position on the primary and secondary axis.

You mean the first chart with red and green?

Like MrShorty and I have mentioned, you should create waterfall chart using Peltier's method and combine it with bracket (stacked column chart using secondary axis).

Not using waterfall chart that comes packaged.

While you can make background transparent for waterfall chart, and the place it on top of bracket chart... it is terrible practice for data visualization.
You'd have little to no control over scale and will require manual adjustment every time underlying data changes, to make scale match.

0.JPG

Couple of notes.

1. You'd need to change your data layout to use Peltier's method of creating waterfall chart (this could be done using intermediate table).

2. I believe you have some values wrong, as Total taxable income should not be negative... (i.e. you can't tax negative income).

3. In order to make bracket align with waterfall... you have to match scale of both axis (i.e. same Max & Min bounds)