How to Create a Waterfall Chart in Microsoft Excel?

What is a Waterfall Chart in Excel?

A Waterfall chart is a wonderful way to visualize running total values. It is also known as a bridge chart or a cascade chart. The other names for a waterfall chart include flying bricks chart or Mario chart. It analyzes the cumulative impact of values when they are added or subtracted from an initial total to arrive at a final total. For example: –

Waterfall Chart
Waterfall Chart

Closing Balance = Opening Balance + Addition + Movement + Reclassification + Discovery + Extension + Production

The gradual positive or negative changes in the intermediate values will impact the total of the closing balance

The picture above depicts the opening balance and closing balance of reserves for a company. The net reserves increased by 300 (5800 – 5500). They net increase in reserves could be attributed to various reasons line Addition, Movement or Reclassification etc… The Opening Balance and Closing Balance values are also called as total values.

Application of Waterfall Chart in Excel

A Waterfall chart has many applications including data visualization and analysis of complex problems. Finance is the one of the main sectors where Waterfall charts are used extensively to understand the effect of positive and negative values on the net results. For example: to calculate the net revenue and further to arrive at the final profit of the company, a lot of investments, fixed and variable operating costs will have to be deducted. A waterfall chart can be used here to find out the cumulative impact of theses costs on the total profitability of the company.

Another example where a Waterfall chart can be effectively used could be tracking your expenses. For example you can use a waterfall chart to arrive at the balance in your bank account at the end of the month by subtracting all the expenses from your opening balance at the beginning of the month. The picture below depicts a simple example.

How to create a Waterfall Chart in Microsoft Excel?

This article describes the creation of Waterfall chart in Excel 2016.  The picture below depicts the calculation of net income for the year 2019.

 Waterfall Chart in Microsoft Excel?

Steps to create a Waterfall chart in Excel 2016:

  • Launch the excel file with data
 excel file with data
  • Select the data and click on “Insert” from the menu and click on “Recommended Charts” option. Select “Waterfall” from the list. Click OK.
Recommended Charts
  • The default chart will be displayed in the excel worksheet.
 default Waterfall chart
  • The Totals and major sub-totals are represented by full columns. For example:
    • Totals include Sales and Net Income
    • Major Sub-Totals include Gross Margin, Operating Income, Income Before Tax
  • Right click on the Totals and Sub-Totals and click on “Set Total” option from the menu.
Set Total
Set Total
  • The Totals and major-sub totals will be represented now as full columns.
columns
  • The default color applied to the “Increase” , “Decrease” and “Total” can also be customized by creating a new color scheme.
  • Click on “Page Layout” from the menu. Click on “Colors” from the “Theme”. Click on “Customize Colors”
Customize Colors
  • Assign different colors to Accent 1 (Increase), Accent 2 (Decrease) and Accent 3 (Total).
11
  • The new color theme will be applied to the chart.
Waterfall chart

It shows the cumulative effect of a series of positive and negative values. You can use to show the cumulative effect of sequential values.

You may also like: