How to create Waterfall Chart in Excel

Excel is used to store, organise and analyse various types of data. It is one of the most widely used tools for data visualisation. More than 1.5 billion people use Excel regularly to manage their everyday tasks.But, while analysing data many users might need to find the positive and negative changes made to the values and the resulting end value. Waterfall Charts provided by Excel is one of the most widely used tools for this purpose.

Its in-built feature allows you to have the analysis of your data on Excel Waterfall Charts with just a few clicks. Want to know more on this?Learn how to create waterfall chart in excel with this blog. You will understandeverything about Waterfall Charts in Excel after reading this blog . This blog will cover the following:

Table of Contents

1) What is Waterfall Chart in Excel?

2) How to create Waterfall Chart in Excel?

   a) Step 1: Rearrange the data table

   b) Step 2. Insert formulas

   c) Step 3. Create a standard Waterfall Chart

3) Waterfall Charts in different versions of Excel

4) When are Excel Waterfall Charts used?

5) Who uses Excel Waterfall Charts?

6) Why should you use Excel Waterfall Charts?

7) Major features of Excel Waterfall Charts

8) Conclusion

Sign up and learn the top 10 Excel shortcuts with our Microsoft Excel Masterclass!   

What is Waterfall Chart in Excel?

Waterfall charts are also known as cascade charts, bridge charts, waterfall graphs, flying bricks charts or net profit Waterfall Charts. Waterfall Charts help the user understand the changes between the starting and ending points of the data. It gives you a glance into the positive and negative changes to a value over a period of time. The start and end values are displayed as columns in a  Waterfall Chart, and the specific negative and positive changes are indicated as floating steps.

In short, Waterfall Charts are used to show the cumulative effect of positive and negative values based on the initial value. You can see that some join the lines in the Waterfall Charts to separate the columns as they give an impression that they are connected, whereas other Waterfall Charts leave the columns free-floating. Waterfall Charts is the best visualisation tool to get a quick analysis of starting value, positive value and negative value and the resulting end values.

How to create Waterfall Chart in Excel?

There is no in-built Waterfall Chart template available in Excel. But you can create your own Waterfall Chart by organising your data. Here we will see how you can create Waterfall Charts for your data.

Here we will take the example of the sales data of an imaginary company. We will insert random data with positive and negative values.
 

Here we are analysing the sales flow of the company XYZ over the period of 12 months. Next, you have to rearrange your data for visualising in Waterfall Chart in excel. Here the end row is added to calculate the change between starting point and at the end of the year in sales.

Step 1: Rearrange the data tables

Here you have to add three more columns, namely- Base, Fall and Rise. The base column is the amount used as the starting point for calculating the Fall and Rise in the Waterfall Chart in excel. Here are stating amount is 5000. All the negative sales amounts in the above table will be put in the Fall column. The positive amounts will go in the Rise column.
 

Step 2: Insert Formulas

We will start by selecting the C3 column in the Fall section and entering the formula:

=IF(E3<=0, -E3,0). Change the formula based on the row and column address of your sheet.
 

According to this formula - =IF(E3<=0, -E3,0), if the E3 value is less or equal to zero, then the negative number will be displayed as positive and the positive number will be displayed as zero. You need to understand that if you need all the values above zero in your Waterfall Chart, then you need to put minus sign (-) before the cell address in the formula. This will make two minuses a plus.

Next, you can use the fill handle to fill the Fall column by copying the same formula till the last month in the Fall column.
 

Now, let's move on to the Rise section. Here you will insert the formula - =IF(E3>0, E3,0). If the E3 value is greater than zero, then this formula will display all the positive numbers as positive and negative numbers as zero.
 

Again, use the fill handle to complete the Rise column.
 

At last, there is one more formula - =B3+D3-C4 and use the fill handle to fill the entire Base column till the end row.

The base value tells the addition to the business in the particular month and by the end of the year by computing the rise and fall together.

Step 3: Create a standard Waterfall Chart

Moving on to finally get all of the stats on the Waterfall Chart in excel.

First – Select the complete data table excluding the sales column. Then go to the insert tab and choose the stacked column.
 

Second – Remove the base point from the table.  


 

Third – Next remove all the base portions in blue from the waterfall charts. To do this, you have to select all the blue portion in the waterfall chart and remove it by selecting No Fill and NO line at the right option from the paint-like icon. 

 




Now, this is how your table should look after removing the base portion. 



Fourth – Select the End and start potion and change the colours to differentiate from the start to the end point t in the waterfall charts. You can do this by clicking on them, right click and fill different colours. 

  

Fifth – The last step is if you want to increase the width of the bars in your waterfall charts. Just click on these bars and at the right side of the page, you will have a stack icon – click on this. You will get the option the increase the gap width and you can choose the width size based on your requirements. 

 

This is how your final waterfall chart in excel should look like:


 

Additionally, you can change colours, and width or even insert numbers and format all the bars in the waterfall charts based on your preference. You can also save your favourite Waterfall chart in Excel template for future use.   

Waterfall Charts in different versions of Excel

Excel exclusively has two versions. These are as follows:

Excel before 2016 – Excel 2013 version and the earlier versions did not have built-in Waterfall Chart type in excel. You had to create your Waterfall Charts based on columns and charts and could customise everything based on your requirements.

Excel 2016 – Excel 2016 and the latest versions provide you with an in-built Waterfall Chart type. Here, all you have to do is to rearrange your data and create the Waterfall  Chart in a few clicks. Below you will learn about Waterfall Charts in detail, including their use, benefits and features.

When are Excel Waterfall Charts used?

The use of Waterfall Charts in excel is widespread in financial and business sectors.  They are excellent for visualising the positive and negative changes within a measurable quantity or KPIs (Key Performance Indicators), such as your Monthly Net Profit or Cash Flow.You can use waterfall charts wherever you have to show how a starting value increases and decreases because of positive and negative changes.  

You can use Waterfall Charts for several purposes, like visualising financial data to even census data. Let's see what are the other scenarios where Waterfall Charts in excel are used:

a) To evaluate company’s profit and loss

b) To compare sales earnings

c) To highlight budget changes in a project

d) To create executive dashboards

e) To track consulting jobs

f) To show the product value over a period of time

g) To keep track of retail inventory 

h) To analyse inventory over a period of time

I) To document contracts

j) To show how operating costs have changed over a period of time

Build your career as a Data Analyst, and learn to prepare tables. Charts, sheets and reports with our Data Analysis Training using MS Excel! 

Who uses Excel Waterfall Charts? 

Waterfall charts have become a standard in the accounts department and financial sectors after being developed as a means to track financial performance over time. Waterfall charts are now being used to track and display performance in an increasing number of businesses and departments within those industries.   

Other businesses and departments that use Waterfall Chart are the following:  

a) Sales organisations and groups 

b) IT experts and developers 

c) Retailers and online businesses 

d) Legal professionals and departments 

e) construction firms 

f) Teachers and test-scoring companies 

Why should you use Excel Waterfall Charts?  

By now, we hope you have understood what  Waterfall Charts are and how popular they are across various sectors. And well in the times of so much data flowing in every second, who wouldn’t want a simple visualisation tool like Waterfall Chart in Excel that gives them the analysis of their business and performance in one glance.

It is the best tool to reorganise and interactively present all your data. There are a number of benefits of using Waterfall Charts, they are simply so popular. We will discuss a few of the reasons why should use Waterfall Charts below:

a) You can customise your Waterfall Chart based on your requirements

b) You can use it for various kinds of analysis to show gradual changes over a period

c) You can dissect the cumulative impact of positive and negative contributions to show how you arrived at a net value

d) You can use It to study and analyse various types of data including census data, inventory analysis, sales and inventory analysis and business performance analysis.

We hope, you have got enough reasons to move ahead and learn how to create Waterfall Charts and not miss an important skill. You may be asked if you know everything about waterfall charts if you're going to apply for job roles like a financial analyst or business analyst.
 

Major features of Excel Waterfall Charts  

Depending on the kind of data you select to present, each  Waterfall  Chart will have a somewhat distinctive appearance. However, the following features are likely to be present in your final Waterfall charts:

Floating column - The floating columns (also known as plot or plotted values) show the positive and negative changes made to the original value in order to immediately provide a visual into the state of a value over time.

Crossover - There are various situations when the values will move across the x-axis, depending on the data you're charting on your Waterfall Charts. The Waterfall Charts should automatically change to reflect movement across the axis; therefore, this is a significant feature.

Connector lines - The connections between the floating columns are depicted by the connector lines, commonly referred to as the datum. Connector lines can be advantageous to enhance the overall appearance of your Waterfall Charts, even though they may not be required for all Waterfall  Charts in Excel.

Spacers - The columns in a waterfall chart must be offset because they don't all start at zero. The spacer or padding is the term for this section.

Colour Coding  - You can colour code the columns based on your preference in Waterfall Charts. You can immediately distinguish between positive and negative numbers and give a quick visual of the movement over time by giving particular colours to the various column types.

Explore more on Microsoft Excel VBA and Macro; register for our Microsoft Excel VBA And Macro Training course now! Sales of XYZ company in 2021

Conclusion 

If you have come all the way here, you should have understood everything about Waterfall Charts and why they are important. We are hoping you are able to create your own Waterfall Chart in Excel after reading this blog. Comment below if you have any doubts related to this blog, and we will get back to you in no time.   

Want to learn how to create advanced formulas and macros in MS Excel? Sign up for Microsoft Excel Expert!