Waterfall Charts are often used to show the cumulative contributions, both positive and negative of various factors to creating a change in a baseline figure. They are often used to explain changes in, for example, year-on-year performance and all of the factors that came into play. More information about Waterfall charts can be found here.
Excel has its own built-in functionality but the implementation leaves something to be desired, particularly with regard to styling. The capability provided by QuickChart allows greater flexibility. It also allows you to make simple Waterfall charts with a single click, or to make responsive charts using a formula.
To make a single-click chart, arrange your data in three columns. The first has the labels that will appear on the X-axis denoting the baseline data and factors that are to be shown. The second column should contain the data for the baseline points (shown on the chart as the grey bars) and the third column should contain the data for the factors that you want to show as positive or negative influences. In order for the chart to display the data correctly, each baseline number should be the sum of the previous baseline together with the factors, as illustrated below.
To create the chart, just highlight the range and click the Waterfall button on the Excel ribbon toolbar.
Creating Waterfall charts that cross the X-axis is exactly the same.
The single-click Waterfall chart is not “live”, however, because the actual data being charted has been split into several different series. This means that it won’t update with changing data. However, to achieve this you can use the “=QC.WATERFALL(…)” formula.
The “=QC.WATERFALL(…)” formula has just a single input parameter which is the same data table that is used to create the single-click table.
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
The “=QC.WATERFALL(…)” formula returns an array with the same number of rows as the source data but with 8 columns. The first column is a duplicate of the factor names, the next 6 are used to populate the series required to create the chart and the final column contains labels for the points if these have been applied in the Settings (see Settings below).
An additional formula, “=QC.YOY(…)”, is also provided to simplify the making of year-on-year charts (or any other periodic comparison).
The “=QC.YOY(…)” formula takes a single parameter, a range of cells where the first column is a list of factors, and the subsequent columns are the periods to be compared. It returns an array with the baseline figures (the sum of all the factors) and then the change in each factor between the various years. In the example shown, Factor 1 has a value of 150 in 2014 and 80 in 2015. This is represented in the output table as a decrease of 70 between 2014 and 2015.
This range can then be used to create either a single-click waterfall, or passed to the “=QC.WATERFALL(…)” formula in order to create a “live” waterfall chart.
In fact, if you want a live Waterfall chart from year-on-year data you can skip the intermediate stage and just nest the two formulae.
The Settings for Waterfall Charts is accessed through the Waterfall dropdown menu on the Excel ribbon toolbar.
There are only three formatting settings:
- Label Waterfall Points – creates labels for each of the relevant series to illustrate the baseline and change values
- Auto-Scale Waterfall – if the changes are small in comparison to the baseline, setting the chart to autoscale will zoom in on the changes by truncating the Y-axis (see below)
- Show Legend – there is usually no requirement to see a legend, but selecting this option will provide a legend with just three entries; Baseline, Positive and Negative. It automatically removes the three additional series used to create the chart from the legend (see below).