WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Waterfall Chart in Excel

Waterfall Chart in Excel

What Do You Mean By Waterfall Chart in Excel?

Waterfall chart in excel is also known as Bridge chart in excel, which is a special type of column chart which is used to show how the start position of a certain data series changes over time be it a growth or decrease, the first column in the waterfall chart is the first value while the last column in the waterfall chart is the final value and in total they represent the total value.

Explained

The Waterfall excel chart was introduced in 2016. The chart demonstrates how the value increases or decreases through a series of changes. By default, the positive and negative values are color-coded. In 2013 or earlier versions of Excel, the Waterfall chart type is not present. However, in this article, you will also learn how to create a waterfall chart if you are using an older version of Excel.

Waterfall chart in excel

Uses of Excel Waterfall Chart

A waterfall chart is widely used for visualizing financial statements, comparing earnings, and analyzing sales or product value over a period of time. It is also used for analyzing profit and loss and visualizing inventory. They became popular in the late 20th century when McKinsey and Company first presented them in a presentation to their clients.

You can download this Waterfall Chart Excel Template here – Waterfall Chart Excel Template

How to Create a Waterfall Chart in Excel 2016?

Now, for example, you want to visualize the data on a waterfall chart.

You can select the cells A3:B14 and click on chart type and select the Waterfall chart.

click on chart type

A waterfall chart for this data will look like—

waterfall chart

The chart will show the revenue generated in each month of the year.

You can also build a similar chart using older versions of Excel. Let us see how.

First, create five columns A:E – (1) Months (2) Decrease (3) Increase (4) Revenue.

The Start value will be in 3rd row and End in the 16th row, as shown below.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Waterfall chart - illustration 1-1

For the Decrease column, use the following syntax –

= IF( E4 <=0, -E4, 0) for cell C4 and so on.

Waterfall chart - illustration 1-2

For the Increase column, use the following syntax –

= IF( E4 >= 0, E4, 0) for cell D4 and so on.

Waterfall chart - illustration 1-3

For the Base column, use the following syntax –

= B3 + D3 – C4 for cell B4 and so on.

Waterfall chart - illustration 1-4

The data will look like –

Waterfall chart - illustration 1-5

Now, select the cells A2:E16 and click on charts.

Waterfall chart - illustration 1-6

Click on Column and then plot a Stacked Column chart in excel.

Click on Column

The chart will look like this.

waterfall chart 2

Now, you can change the colour of the Base columns to transparent or no fill, and the chart will turn to a waterfall chart, as shown below.

waterfall chart 3

Create a Waterfall Chart in Excel Older Versions 2007, 2010, 2013

Suppose you have the net cash flow for your company for each month of the year. Now, you want to see this cash flow over a Waterfall graph for better visualization of cash flow throughout the year and see which period faced the most crisis. The net cash flow data is shown below.

waterfall chart example 1

To plot this chart, simply select the cells B3: C16 and click on the waterfall chart to the plot.

waterfall chart 4

If you have an earlier version of Excel, you can use an alternative method to plot.

Firstly, make five columns, Time, Base, Decrease, Increase, and Net Cash Flow, as shown below.

waterfall chart example 1-1

Now, fill in the details for the Start. The Base value will be similar to Net Cash Flow, Decrease and Increase will be 0.

waterfall chart example 1-2

For Jan, the increase will be given as –

= IF ( F23 >= 0, F23, 0) for cell E23.

waterfall chart example 1-3

For Jan, the decrease will be given as –

= IF ( F23 <= 0, -F23, 0)        for cell D23.

waterfall chart example 1-4

For Jan, the base will be given as –

= C22 + E22 – D23

waterfall chart example 1-5

Now, simply drag them to the rest of the cells.

waterfall chart example 1-6

Now, you can select the cells B21: E35, and click in Charts -> Columns -> 2D Stacked Column.

2D Stacked Column

The chart will look like this.

waterfall chart 5

Once the chart is prepared, you can set the Base fill to no fill, and you get this chart, as shown below. Also, you may change the colour of the Start and End bars.

You can adjust the thickness of the bars and Gaps in between to better visualization using the following steps –

Right click on the chart -> Select Format Data Series -> Decrease Gap Width.

format data series

Now, Finally, your chart will look like this.

waterfall chart 6

In the chart, you can observe that there was an increase in the Net Cash Flow during the year, and the Net Cash Flow was maximum during July. However, during Aug – Sep, the decrease was huge.

Pros

  • The Waterfall excel chart is simple to create in Excel 2016+.
  • Good visualization of change in values over time.
  • Good visualization of negative and positive values.

Cons

  • The waterfall graph type has limited options in Excel.
  • Making this chart becomes cumbersome when using a 2013 or lower version of Excel.

Things to Remember

  • It shows a cumulative effect of how the sequentially introduced affect the final value.
  • It is easy to visualize positive and negative values.
  • This is a built-in chart type in Excel 2016.

Recommended Articles

This has been a guide to Waterfall Chart in Excel. Here we discuss its uses and how to create Waterfall Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Gantt Chart Examples
  • Create a Bubble Chart in Excel
  • Area Chart in Excel
  • Pie Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Waterfall Chart Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More