Cascade chart (Waterfall)

Cascade chart (waterfall) allows better understanding of accumulated impact of various values (positive or negative) on whole. These values of course can be split in time or by any other dimension (like categories). Waterfall chart is also known as cascade or bridge chart. It allows quick and easy interpretation of impact of different KPIs on whole value like for example performance impact of different categories in financial results throughout two different periods of time but also an iteration of further trend like split of whole year by months and split between categories. Usually it is presented in form of two static columns (start / end) between which we can find floating columns. Usually floating columns are determined by two colors interpreting either increase or decrease of value.

How to create waterfall chart?

If you are using office 2019 and higher then waterfall chart is naturally built into Excel. You can find it in ribbon Insert. Waterfall chart can also be created by using column chart or combo chart. Below I will describe all three methods.

Wykres kaskadowy (Office 365):

Waterfall chart is created by selecting data and picking up waterfall chart from the ribbon. Excel should automatically prepare your chart (of course if your data is organized properly). How to organize data for chart? Put your categories in left column and values in right. Excel will pick them up immediately (example below).

Column chart (any Excel version):

Create table with listed columns:
Category, Base, Fall, Rise, Value.
Category field will contain your axis labels. Field ‘value’ determines value for each category. Fields ‘Rise’ and ‘Fall’ determine rise and fall. Field ‘base’ is being used to get previous value and do calculations with current row determining rise or fall.
Field Rise will use formula accordingly to screenshot below:
=IF(G29>0;G29;0)
Field Fall determining loss will use formula as below:
=IF(G29<0;-G29;0)

Field Base takes previous value (in case of first category leave base value empty) and adds Rise and deducts Fall values.

After dragging your formula down to last category effect should be exact as in screenshots above. Let’s proceed to building a chart. Select your data with headers but skip Value column – you will need it later. Then from Insert ribbon pick stacked column chart. Excel should automatically build your chart as below.

Do following steps:
– Change formatting of Base series – make it no fill (add filling only in total column), and color the rest of columns as per your needs.
– Add data labels only for series ‘Rise’ and single label for series ‘Total’.
After these two simple steps your chart should look like below:
Let’s go to our third option.

Combo chart (column and line)

Create table with following columns:
Category, End, Before, After, Value, Labels.
Category like before will contain axis labels. Column ‘End’ will contain data for first and last column on chart. Column ‘Before’ will contain sum of range from last element (remember to create it as dynamic range) while we move forward.
=SUM($G$51:G51)
Column ‘After’ will also contain sum but in this case first range will be previous element (freeze it) until actual as in screenshot below.
=SUM($G$51:G52)
Now drag the formulas down to get your data calculated as in above example.
Lastly create column Labels. It will contain our data labels so we will not need to move them manually. Create new column and for first and last record put the ‘End’ value and for all categories between beginning and end use formula MAX selecting actual row in columns ‘Before’ and ‘After’ (see screenshot below):
All we have to do now is to create a chart. Select columns in range Category : After together with their values and go to Insert ribbon and pick all charts (as per below) and in All charts pick Combo and for series End pick clustered column while for Before and After pick line chart.
After creating chart select one of line series and add element ‘Up/Down Bars’. This will create columns between lines.

All we have to do now is to change colors for positive and negative values as well as turn off filling line labels. You can do it all from chart formatting options.

Now let’s move to adding data labels series. Go to chart options and add new line series from column Labels. Now add data labels to newly created series and change line formatting to ‘no fill’. Next go to data labels options and in section ‘Label contains’ turn off Value and select Values From Cells. Now you need to select your range of data labels – select all values in Value column. Thanks to that your data labels will secure their position and will show proper information based on increase or decrease.
That would be it in terms of creating waterfall charts. Even tough it might seem hard for beginners it is a matter of practicing it on few examples to become fluent in creating this kind of solutions. Of course it is best to use built in solutions as these are the fastest but I also wanted to show few workarounds.

Interesting article? Share further!

Leave A Comment