Waterfall Chart in Excel

Waterfall Chart (because it looks like a waterfall) is an awesome way to display how things add up to form the total. Let’s dive into this

 

Here is some Random Data

Waterfall Chart in Excel 2

We have units produced (in millions) over 6 months period

 

The Waterfall Chart Logic

The waterfall chart is a combination of 2 data points combined in a single chart. Let me explain. Take a look at this picture..

Waterfall Chart in Excel 3

  1. The first bar for Jan valued 66 stands on the ground (x axis) because it is the starting point
  2. The second bar for Feb valued 30 stands on 66 (hidden, dummy value for Jan) because 30 has incremented over 66 (last month’s value) and this way we keep plotting values till the Total

 

Preparing dummy for the Chart

Now we need to prepare dummy data which keeps on totaling the last year values. Here is how to do it

Waterfall Chart in Excel 4

Dummy Data Formula  =C6+C5  Add the previous empty cell (C6) and the value for Jan (C5) and drag it across Feb to Total. This will give a cumulative total

 

Preparing the Chart

Waterfall Chart in Excel 5

  • Now reverse the Chart series order to make the dummy series go below the actual data series. Here is how you can do it
    • Right click on the chart and go to ‘Select Data’
    • You’ll see Series 1 (actual data) and Series 2  (Dummy Data)
    • Reverse the order by the down arrow button so it should appear (in reverse order) Series 2 and Series 1
  • Hide the dummy series (do a no fill), leaving aside the total bar (fill it with any color)

Waterfall Chart in Excel 6

The Waterfall Chart is ready! Download the completed Waterfall Chart

 

Learn More Interactive Charts

  1. How to add a total bubble at the end of the line
  2. Highlight the max and min values in the chart
  3. Interactive Stock Ticker Chart

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI