Open In App

How to Create Waterfall Chart in Excel (Bridge Chart)

Improve
Improve
Like Article
Like
Save
Share
Report

A waterfall chart is used to visualize data and understand the cumulative effect of sequentially introduced positive or negative values. For this article, we’ll see the sales data of a company from January till December. It helps to see the changes that occur between a starting point and an ending point.

What is a Waterfall Chart in Excel?

A Waterfall Chart is a type of column chart. The waterfall is one of the different types of charts in Excel. It is used to state how the starting position either increases or decreases through a series of changes. 

This Chart is also known as the Excel Bridge chart since the floating columns connect the endpoints. These Charts are quite useful for analytical purposes. If you need to evaluate some kind of data such as student performance, a company profit, or earnings.

How to Create a Waterfall Chart in Excel 

Step 1: Create a data table. 

So, let’s create the data table with a Start Value as shown below.

Data table of sales data of a company

Now, make three additional columns as shown below.

End row added.

Note that I’ve added an END row after the months.

In the FALL Column, we’ll enter the formula: =IF(B2<0,-B2,0) and then apply it to all the cells of the column. 

Note: If you want all the values in the waterfall chart to lie above zero, you need to enter the minus(-) sign before the second cell reference (B2) in the formula and two minus signs will make a plus.

Enter data in FALL column

Similarly, add the formula =IF(B2>0,B2,0) to the Column, RISE. It means if the values in cell B2 is greater than 0 then, all the positive number will be displayed as positive and negative as zero. displayed

Enter data in Rise column

Now, add the formula =B4+D4-C5 in cell B5 and apply it to all cells till the end of the column including the END row.

Insert data in Base column using formula B4+D4-C5

Step 2: Create a Standard Stacked Column Chart

Now select your entire table, excluding the SALES Column, and go to Insert. Locate the Stacked Column Chart under the Column Charts section and click on it.

Creation of standard stacked column chart

It will look something like this

Standard column chart created.

Step 3: Conversion to a Waterfall Chart

convert the chart into waterfall chart.

Click on any of the blue bars to select them all. Then right-click and select Format Data Series and set the following values as shown.

Format data series selected.

The final result will look like this

Final result of waterfall chart.

Now, let’s make a couple of more tweaks to make our Waterfall chart more presentable.

Click on any of the Bars once and then right-click. Select Format Data Series and set the Gap Width to a small in the range of 5-25%

Formatting waterfall chart

Now, on your chart select the transparent bar just above END and double-click on it. Then, go to Fill > Solid Fill > Fill Color > Color > Green

You’re all set. Your Waterfall Chart will now look like this.

Waterfall Chart

You can try experimenting will colors, labels, etc., and see what works out best for you!

How to Build an Excel Bridge Chart

Excel doesn’t have a built-in waterfall Chart template. But you can easily create your own version by carefully organizing your data and using a standard Excel Stacked Column chart type. Follow the below Steps to build an Excel Bridge Chart.

Step 1: Rearrange the Data Table

Step 2: Insert Formulas

Step 3: Create a Standard Stacked Column Chart

Step 4: Transform the Column graph into Waterfall Chart

Step 5: Format Excel Bridge Chart

Features of the Waterfall Chart in Excel

  • Connector lines
  • Floating columns
  • Cumulative data points
  • Representations of negative values
  • The total is displayed as a final data point 

FAQs

How to do a Waterfall Chart in Excel?

Step 1: Select your data 

Step 2: Click Insert > Insert Waterfall or stock chart > Waterfall chart.

What are the benefits of using the Waterfall Chart?

The main advantage of using a waterfall chart is that it has a clean and uncomplicated format which makes performance analysis quite easy and helps the user to observe the cumulative effect of individual changes.

What is Pocket Price Waterfall Chart?

In the waterfall chart, only two highlighted columns are present (Start and finish), and the Pocket Price Waterfall chart has many highlighted columns.

What are the Connector lines in the Chart?

Connector lines Connect the end of each column to the beginning of the next column to maintain the flow of data in the chart.



Last Updated : 06 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads