Bottom line: Learn how to create this interactive dashboard waterfall chart that tells the story of what happened over time.
Skill level: Intermediate
Tools used: Charts, Pivot Tables, Slicers
What Does the Dashboard Tell Us?
This interactive dashboard is great for displaying amounts of data between two periods of time, and compares multiple series or groups.
In this example, we are looking at the starting versus ending inventory of the month for a few different products.
The column chart at the top of the dashboard compares the inventory levels for all the products. This chart is useful on its own, but we typically want to dig deeper and see what caused inventory to increase or decrease over the month. The waterfall chart at the bottom of the dashboard is perfect for this.
This type of dashboard can be used for all types of data, not just inventory. I've used it for sales pipelines, GL account balances, cash flows, and much more.
The Waterfall Chart Explained
When the boss asks the inevitable question, “What happened last month???”, the waterfall chart can be your savior. It's a great way to paint a picture of exactly what caused changes in your data over two time periods.
When creating charts and dashboards our goal is to tell the story of what happened in the past. This helps us make decisions, plans, and forecasts about the future.
The waterfall chart allows you to drill down on the details of your data to gain clear insights on what is happening with the business. This is essentially cross-filtering between the charts.
It is also very easy to read and understand, meaning you won't have to do a lot of explaining to your readers.
How to Create the Waterfall Dashboard
You're probably wondering how I created this dashboard… Well, the secret is:
- 3 Charts
- 2 Pivot Tables
- 1 Slicer
NO macros or VBA are required to create this, thanks to the slicer and pivot tables.
So how does it work? I'm going to explain how each piece of the dashboard works, without going into too much detail. Please feel free to leave a comment if you still have questions. You can also download the file to follow along.
Download the File to Follow Along
Waterfall Chart Dashboard.xlsx (64.8 KB)
1) Setup the Data
First we have to start with some data. I compiled the data into one table on the ‘Data' tab. The data is arranged in a way that makes it ready for a pivot table. Please see my articles on How to Structure You Source Data for a Pivot Table and How Pivot Tables Work for more details on this layout.
2) The Pivot Tables
Once the data is setup in the correct layout, we can then create pivot tables to summarize the results for the charts.
The ‘Pivot' tab contains two pivot tables that are used for the source data of the charts.
The pivot table starting in cell B4 contains the data for the starting and ending data that is displayed in the column chart. Starting in cell F4, there is another range of data that is used to displays the colors of the selected item on the chart. A simple IF formula in cells G6:H9 only show the numbers for the selected slicer item (more on this below).
The pivot table starting in cell M3 displays the data for the waterfall chart. The slicer is connected to this pivot table, and slicers for the Item field which is in the Filters area of the pivot table.
3) The Charts
The column chart displays the amounts of the selected item in color (blue), and the rest of the bars are grey. How does this work?
The chart uses two axis, the primary and secondary. All the bars are displayed on the primary axis and are colored grey. The pivot table in cell B4 of the ‘Pivot' tab is the source data.
This data is plotted on the secondary axis of the column chart. The secondary axis is plotted on top of the primary axis, so we can color the bars blue for the series on the secondary axis. This gives us the effect where the columns on the chart for the selected item are blue, and all others on the primary axis are grey.
For the waterfall chart I used Jon Peltier's Charting Utility. This is a very handy Excel Add-in, and one that every chart maker should have.
The waterfall charts are NOT easy to make. They are made up of a complex combination of column and line charts, and I've spent the better part of a day trying to create one on my own. The Charting Utility Add-in creates the complete chart you see here with a few button clicks, and it has a lot of features that allow you to customize the chart further.
The ‘Waterfall Chart' tab was created with the add-in, and contains all the information necessary to plot the different series on one chart.
The XY Scatter chart is just used to create a vertical line between the slicer and the waterfall chart. The source data and formulas can be found starting in cell J6 on the ‘Pivot' tab. Basically the vertical line moves to the right or left depending on which slicer is selected.
4) The Slicer
As I mentioned before, the slicer is connected to the pivot table that starts in cell M3 on the ‘Pivot' tab. This filters the data for all the charts to make everything interactive.
The nice part about the slicer is that it saves us from having to use VBA or macros. You don't have to worry about your users enabling macros to make it interactive.
Plus, the slicer can be created with a few clicks, and it looks great right out of the box.
Do You Use Waterfall Charts?
If so, what types of data do you display on your waterfall charts. Or, what types of data would you display on a waterfall chart now that you know what they do?
Here are a few examples:
So how do you use waterfall charts?
Please leave a comment below with your answer, or any questions you have.