Interactive Waterfall Chart Dashboard - Excel Campus
13

Interactive Waterfall Chart Dashboard

Interactive Waterfall Chart Dashboard in Excel

Bottom line: Learn how to create this interactive dashboard with a 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.

Waterfall Chart and Column Chart Inventory Dashboard Explained

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.

The Waterfall Chart Tells a Story in Excel

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.  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.

The Source Data for the Waterfall Dashboard Pivot Tables

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).

Column Chart Source Data in Excel Pivot Table

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.

Waterfall Chart Source Data in Excel 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.

Excel Column Chart Colored Bars for Selected Item

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.

Peltier Tech Chart Utility Add-in Waterfall Toolbar Screenshot

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.

Peltier Tech Chart Utility Waterfall Chart and Source Data Setup

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.

XY Scatter Chart Makes Vertical Line Moves with Slicer Selection

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.

Slicer for the Waterfall Chart Dashboard and Pivot Table

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:

Headcount Changes Waterfall Chart Example

Display Changes in Headcount

Water Reservoir Waterfall Chart Example

Changes in Water Levels

Sales Pipeline Waterfall Chart Example

Changes to a Sales Revenue Pipeline

So how do you use waterfall charts?

Please leave a comment below with your answer, or any questions you have.

Want to Learn More About Dashboards?

I hope you find this dashboard useful.  Please feel free to use it to tell your data story, and impress your boss. 🙂

Learn Dashboards from MyOnlineTrainingHubDashboards are a hot topic these days, and for good reason.  With an ever increasing amount of data at our fingertips, we are constantly looking for a way to consolidate, summarize, and analyze data in meaningful ways that help tell a story.

If you really want to learn more about dashboards and take your “data communication” skills to the next level this year, then I highly recommend checking out the Free Webinar on Excel Dashboards from Mynda Treacy at My Online Training Hub.

Mynda also has a full course on dashboards.  You can checkout my full video review of the course if you are interested.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 13 comments
ani - September 12, 2017

hello, great chart… can the bard colors for negative (-) numbers be different? they are both red, can i have one red and one gray?
thank you!
ani

Reply
Aubrey - January 8, 2017

Hi Jon,
I was teaching my daughter how to use the filter in a pivot table and stumbled upon your article…..the slicer is great fun ! Thanks for this.

Reply
Dave Chu - October 21, 2016

Can you please create a video with steps on how to create this dashboard? I am trying to read the directions but it is very vague and i cant seem to follow on how to create this dashboard with the techniques with the bar chart and scattered charge to create the effects that you have.

Reply
Mynda - January 14, 2015

I love what you’ve done with the Slicer! Very clever.

I can see many applications for this e.g. instead of the waterfall chart you could put a line chart that shows the trend over time etc.

Mynda

Reply
MF - January 13, 2015

One little suggestion is to align the color used for Starting and Ending Inventory in both charts. 🙂

Reply
MF - January 13, 2015

Nice article! I think it’s a very good idea to put the the WaterFall Chart right under the column chart to explain the reason for the changes.
Good job as usual, Jon!

Reply
Jomili - January 13, 2015

You’re right, that WAS rude of me.

Jon,
Please accept my apologies. I did go the Google thing, and can create a Waterfall chart. However, I still don’t see how the waterfall chart in the example was created, so I’m still missing that crucial piece. Could you put more detail behind that for those of us who don’t have access to the add-in?

Reply
    Jon Acampora - January 13, 2015

    Hi Jomili,
    Apology accepted! 🙂 I totally understand your frustration, and I appreciate you expressing your concerns.

    As Jeff mentioned, there are other ways to create the waterfall without the add-in. As most examples show, you will use a stacked column chart, and make the bottom column transparent so it is not visible to the viewer. This visible column basically holds up the amounts that cause the change between the two time periods.

    Jon Peltier has a great explanation at the following link on how to create it manually.

    http://peltiertech.com/excel-waterfall-charts-bridge-charts/

    The major difference is that this chart won’t contain the horizontal lines like you see in the chart in the example. It’s not a deal breaker by any means, I just like the formatting of the chart created by Peltier’s add-in.

    Regardless of how you create the waterfall chart, for this to work, the source data of the waterfall chart needs to point to the pivot table starting in cell M3 on the ‘Pivot’ tab. Checkout cell B2 on the ‘Waterfall Chart’ tab. It points to cell Q6 on the ‘Pivot’ tab. So basically the waterfall chart is using the pivot table as it’s source. Whenever a slicer item is selected, the pivot table in cell M3 is filtered, and the data in the pivot table changes. This then changes the data that feeds the waterfall chart.

    I will update the file with a waterfall chart that is created manually, so you can get this working. Hopefully you can impress your boss enough to get that budget extended j/k. 😉

    Well, I hope that at least gets me a bunt, with a chance to get to first base… haha! 🙂 Have a good one Jomili! Thanks

    Reply
      Jomili - January 14, 2015

      Jon,
      Thanks for your graciousness in accepting my apology. And thanks for the link to Jon Peltier’s walkthrough. I like the way he walks through it. Between his post and yours I’m learning a lot these days!

      Reply
Jeff Weir - January 13, 2015

“Swing and a miss” is a bit rude, in my opinion, Jomili. This is still a good post that has some good insight into a great data visualization idea.

You could always pick up the bat yourself, and see what hits you come up with if you type “Create Waterfall Chart” into Google. Because Jon Peltier’s utility isn’t the only way of creating a waterfall chart, and Jon Acampora shows in this post how they can be put to good use.

Reply
Jomili - January 13, 2015

Swing and a miss. I was very excited to see the dashboard, and to learn how to make it. The “Tools used: Charts, Pivot Tables, Slicers” let me know I could do it. Until I got to the Chart Utility. I use my Excel at work, and we have no budget for add-ins, so I was disappointed there was no other direction on creating the waterfall chart. Because of that, there’s not much help for me from this post.

Reply

Leave a Reply: