Chances are that since you are looking for step-by-step guide to create a waterfall chart, you already know the usefulness of it and what it helps in achieving. But, to give a very short introduction into it – “Waterfall charts are few of the common charts used to provide a comprehensive view on the cumulative effects of various variables / data items on the overall value. It helps to view the sequential effect from the initial value to the final value.“
Let dig right into the steps to create a Waterfall Chart.
If you are using the latest version of MS Excel, then excel has made our life easy by providing a direct option to create a waterfall chart, similar to how we create pie charts or bar charts. However, in case you are using previous version, then be rest assured, this blog will help you with detailed instructions for earlier versions of Excel as well. Hence, I will divide the steps into 2 segments: One for the most updated version and one for the earlier version. I will also attach the corresponding excel file with charts made basis both the version here for your ready reference and template.
In both of these cases, we will use the following data set to build our chart. The data set simply represents what components went into growth of Sales from 2020 to 2021. As we can se Acquisitions and Price Inflation contributed to increased sales, however closed location and organic sales decline resulted in a decrease of sales in 2021. Below is the table with the raw data:
Steps to Create A Waterfall Chart in Office 2016, 2019 and 365:
- Select the data set from 2020 Sales to $972.6
- Press ( ALT + N + I1) or Go to Insert –> Charts –> Select Waterfall Charts (as shown in picture below)
- You will get a chart similar to below:
- Before moving ahead, it is important to make the chart little cleaner. To do that clear our the gridlines, chart title (if not needed) and legends
- Next (note this is very important step to ensure the ending value is correctly reflected in the chart) – Right click on the last bar (2021 Sales) and select the second last option (Set as Total)
- Change the Vertical axis basis the data range (in our case can be $900 – $1,010) and recolor the bars by fill option – with positive values shown as Green and negative values as red (as shown below)
- For the final step, we can hide the vertical axis and also remove the connector lines between bars by selecting the connector lines and pressing delete
- Alternatively, right click inside the chart and go to format plot area. In the panel on right, select “Series Option” and untick the “Show connector lines” radio button
- Final chart will look as below:
Steps to Create A Waterfall Chart in Earlier Office Versions:
Unlike the above steps, where excel did all the work for us and left the formatting part to the user. There are more steps required to be performed in cases where the pre-existing charting tool for waterfall chart is not available. The manner in which the raw data needs to be organized is also very specific, without which we will not able to achieve the required results.
- As noted, since the excel cannot itself recognize the data for waterfall chart, we need to alter the raw data in various columns for easier chart construction
- Whenever one creates the waterfall chart, they will need to organize the data into 5 columns namely – Label, Base, Rise, Fall and Value
- Label: It just represents the label of the data set that will be shown on our Y-axis. In the example we have have been using, the label column will show 2020 Sales, Acquisitions, Price Inflation, Closed Locations, Other Organic and 2021 Sales
- Rise: Represents the column for positive values
- Fall: Represents the column for negative values
- Value: Represents the column that we will finally use to label the data points
- Base
- First row will be linked to the starting value. In our example Base column first row will be linked to 2020 Sales data
- From second row onwards base column will have the following formula:
- Base 2 = Base 1 + Rise 1 – Fall 2 ; meaning base row 2 will be equal to base value in row above (row 1 here) + rise value in row above – fall value in same row (row 2)
- Base 3 = Base 2 + Rise 2 – Fall 3 and so on
- Rise
- Since there will be no rise value in starting figure, as this is our base value, we will not put any formula there (2020 Sales row in our case)
- For each subsequent label (acquisitions etc), we will put either of the below formula in the respective rise column
- Max (Value of respective label, 0)
- If(Value of Label<0,0,Value)
- Fall
- Again, since there will be no fall value in starting figure as it is our base value, we will keep this row under fall column as blank
- For each subsequent label (acquisitions etc), we will put either of the below formula in the respective rise column
- – Min (Value of respective label, 0)
- If(Value of Label>0,0,-Value)
Once organized as above, our data set should look like this:
To build the chart select data from 2020 Sales to Fall column of 2021 Sales and follow the steps below:
- Go to Insert >> Charts >> 2D Stacked Column Chart
- Clean up the chart by removing the background grid lines (select the gridlines and press delete), delete the legend, chart title (if not required) and remove the vertical axis by unchecking the Primary Vertical option under Chart elements (as shown below)
- Select the “Base” columns (blue bars) and select no fill color option
- Recolor the starting (2020 Sales) and ending (2021 Sales) bar with one color
- Recolor the rise bars with Green and Fall bars with Red color
- Finally add data labels, but please note you will have to manually link each label to the Value column (this step is little tedious!)
- Finally, you have your finished chart as below
Below is the file that contains chart using both the methods.
Financial Fudge 