Ultimate Excel Waterfall Chart Course

Excel 2016 came with a built-in waterfall chart. In this lecture I’m going to show you how to use the Excel 2016 standard waterfall chart. I will also highlight it’s shortcomings and why I still prefer to create the waterfall chart from scratch. Even in Excel 2016. It obviously involves more steps but it comes with great flexibility. Flexibility that the new built-in Waterfall graph does not provide.

Those of you that have older versions of Excel, can get to see how the Excel 2016 Waterfall chart works. You cannot replicate the steps in Excel as you do need the 2016 version. This is the ONLY lecture that requires Excel 2016, if you want to follow along. All the remaining lectures and techniques can be replicated in older versions of Excel.

Those of you that have Excel 2016, can see the advantages and the disadvantages of using this new graph.

This lecture provides a summary of the new Excel 2016 built-in Waterfall chart. It highlights it's advantages and the shortcomings of the new Waterfall graph.

Here, I take you through the basics of creating the Excel Waterfall graph from scratch. I will introduce you to 3 "secret" - or better, "less known" Excel techniques that you can use to create an easy and super flexible Waterfall chart in Excel. The techniques I describe here allow you to quickly create a Waterfall Chart that not only works on ANY Excel version but also shows negative cumulative values. If you've been using stacked columns until now, you can say goodbye to them. You will learn a much simpler approach here.

The three techniques talked about here, can be applied to ANY other chart to get the impact that you want from the chart. Do pay close attention to these and think about how you could use the techniques in your other Excel charts.

In the last lecture I introduced you to important techniques required to create a flexible chart. This lecture, will be hands-on where I take you through each step in Excel from scratch until we have our flexible Waterfall chart which also works with negative cumulative values as well, i.e. below the horizontal axis.

I show you here how to customize the existing Waterfall Excel chart to your own data sets. For example, what you need to update if you have more delta categories than the ones I show in the lectures.

Some Waterfall charts need subtotals. I find that when I have a lot of deltas, the graph is easier to read when you add subtotals to it.In this lecture I show you how.

Please complete the exercise based on your own data to create a Waterfall chart from scratch!

Real business case scenarios are generally more complex than the models you learn in a standard course. What if you have very long category labels or you want to group your categories on the x-axis? You might want to format certain categories different to the rest. You might also want to add deviations to previous year deltas below the horizontal axis. How can you handle these cases in Excel? In this lecture, I will show you various options.   

In this video I will introduce you to a very "hidden" Excel feature that can help you improve your Excel Reports. The feature you see here, can be used for MANY different situations. Handling long category labels is just one of them. You can apply this technique to your other Excel files to create nicely designed Excel Corporate reports.

Here you will learn how to create dynamic Elbow connectors that are a PART of your Excel chart. They will move up and down dynamically as the values for your Waterfall chart change. You will also see how you can dynamically present the difference between your end and start values in the chart for easier readability. This technique will surely impress your management.

This is an alternate technique to the one shown in the previous lecture and works well if your end value is negative. It includes floating arrows "above" the chart - Actually, they look like they are above the Excel graph, but in fact they a PART of your chart. They also reflect the difference between your end and start values for easier readability.

In many business case scenarios the delta values (the values corresponding to your changes) are too small in comparison to the start and end values. In this lecture I show you a smart alternative to handle such cases.

Please complete the exercise based on own data to build on your existing Waterfall chart.

I show you here, a simple approach to creating a Waterfall bar chart (Vertical Waterfall Chart) in Excel. The method uses stacked bars. It’s relatively easy to create and helps you get a good feel of using bars instead of columns. It does come with a limitation though. It works as long as everything is on the positive side of the axis. In later lectures I will show you other methods that are fully flexible, which work on both sides of the axis. It’s always best to start small and then build on it. That’s what we're going to do in this lecture.

Here, we build on the Excel chart we created in the previous lecture and improve the positioning of the data labels to have them all lined up at the outside end of the bars.

Here, I show you how to create a Waterfall bar chart (Vertical Waterfall graph) that works on both the positive and negative side of the axis. To keep our data preparation table as simple as possible, you need to use another technique. The techniques we applied in the first sections of the course to create a fully flexible standard Waterfall chart, unfortunately do not all work in the "bar" version of the Waterfall. Watch this lecture to find the best approach. This might surprise you!

In this lecture I will build on what you learnt last and full proof the Waterfall chart. We will normalize our Y values.

Here, you will see how to use the "hidden" technique which I previously introduced you to, to create a vertical Waterfall chart in no-time. This is specially great and saves a lot of time if you are designing a prototype to show your colleagues and management.

You will find out the best method to add a vertical dynamic line (with arrows) to your Excel chart that also reflects the difference between end and start values. These dynamic arrows, look like they are outside your chart, but in fact they are a part of your chart. The positioning of the line changes depending on the values for your bars and so does the total difference. 

You will learn how to add a dynamic line that reflects the length of the difference between your end and start values and also shows this value in the middle. The line looks like it is outside your chart but it is dynamic and adjusts it's size and position as your data changes. Here, you will learn a brand new technique you can use in other charts as well.

Complete this exercise to transform the Waterfall chart you created in the first exercise to a Vertical Waterfall chart.

In this lecture, I show you the traditional approach of setting up the Excel Waterfall chart which is using stacked columns. It is always good to see different ways of getting to the same place. Note that the approach shown in this lecture works as long as cumulative values are positive. In the next lecture, I will show you how to expand on the data preparation and use the stacked column method to create a Waterfall chart that also works with negative cumulative values.

This lecture expands on the previous lecture. I take you through the steps of setting up a Waterfall chart which also works on negative cumulative values using the stacked column approach. You will see here, how your data preparation needs to be setup. I explain each step as I set it all up from scratch.

Here I show you an alternative to creating the connectors used in Waterfall charts. 

In this lecture I provide you with an overview of different approaches and techniques for creating stacked waterfall charts and my personal favorites. 

I find this option to be the easiest to visualize and also for the reader to understand. Stacked waterfall charts can be confusing, specially when one stack has a different sign to the other stack - for example: one stack shows a negative change and the other stack shows a positive change. How can this be visualized from your side but also understood from the readers side? This lecture shows you one option.

This option requires a little bit of effort to visualize from your side, but can handle many stacks and also stacks that have different signs. How does it do that? By showing separate stacks when all stacks have the same sign (i.e. all moving either in positive or negative direction) and showing one single stack with an alternate color when there are mixed sign stacks. 

Make sure you download the template from the first section.

This lecture builds on the last one to complete option 2 for stacked waterfall charts.

Make sure you download the Excel workbook from the first section to follow along.

Where I wrap-up everything you learnt in this course which includes 

• the various versions of Waterfall charts covered
• important techniques required to create these charts in a flexible & easy manner
• the approach you need to take when designing more complex charts in Excel

The attached Waterfall Chart cheat sheets provide brief and simple instructions to help you quickly create the waterfall charts in this course. Don't forget to download the PDF document and keep it handy.