Waterfall charts are very effective at showing positive and negative values of change. Their use is for specific cases and is frequently relied upon in certain industries. Those mostly come from insurance sectors, financial services, or human resources.

In this article, we are going to explain everything related to waterfall charts: their functions, when to use them, their benefits and drawbacks, and how to structure one of your own.

A golden rule over data visualization is that every bar chart has a common baseline of zero. With waterfall charts, it only applies to the first and last bars, referred to as the "before" and "after" values. 

The baseline of the component bars is completely different and is dependent on whatever the running total is. To properly explain this, the baseline for the middle bars is the end of the previous one, and you will see the waterfall chart as a staircase moving down and up, connecting different pillars. 

At first sight, it can be confusing as we aren't accustomed to seeing bars floating in space. To complicate things, those component bars are at the top of the initial value bar and sometimes at the bottom (depending if they were positive or negative changes). 

Going even further, waterfall charts can have multiple periods of time. The same chart could be divided by quarters, with these sections having their own waterfall chart and the next one starting from the previous "final value." It can be confusing, and that's why we are going to break down all of its components.

Now, let's add three columns between those two. One of them will be the baseline number, and the other two columns will hold positive values and negative values separately. Name it as you like, but introduce anything to avoid confusion. 

Then, add the last row as "end" or "total," which will calculate the income and sales amount for the whole year.

Introduce the Formulas

It’s time to insert the formulas to complete the table. You will need to select the first cell of each column and introduce the corresponding formula in the function tab and copy them down to the rest of the corresponding cells below. Let’s go step by step:

• Select the first cell of the “Fall” column and write the formula =IF(E4<=0, -E4,0). The formula says if the value in cell E4 is less or equal to zero, the negative value will be displayed as positive, while the positive value will be shown as zero. Then, drag down the formula to the rest of the column.

• Select the first cell of the “Rise” column and write the formula =IF(E4>0, E4,0). This means that if the value in cell E4 is above zero, all the positive values will be shown, while the negatives will be displayed as zero. Also, copy and drag it down to the rest of the column.

Note: Cell E4 refers to the first cell of the “Income” or “Sales Flow,” which are the numbers that we are analyzing. 

• Lastly, insert the formula =B4+D4-C5 in cell B5 (the first corresponding cell under the "Base" tab), copy it, and drag it down, including the "End/Total" row. This formula will calculate base values that prop up the falls and rises to the appropriate height.

Set the Data Table as a Stacked Column Chart

With the data well-organized, it is time to build the chart itself:

• Select all the data, including column and row headers, and leave the “Income/Sales Flow” column.
• Then, go to the "Insert" tab within the Excel tools and select the "Charts" group.
• Select the "Insert Column Chart" icon, and once on the Chart Design tab, choose "Stacked Column" from the drop-down menu.

You will be able to see a new graph with all your data stated, but it doesn't resemble a waterfall chart. That's what we are going to do in the next step, as we will be turning the stacked column graph into the so-called "bridge charts."

Transform the Stacked Column Into a Waterfall Chart

To do this and start actually making a waterfall chart, you need to make the “Base” column series invisible. 

• Select the "Base" series, then right-click and choose the option "Format Data Series" from the drop-down menu. It will appear in a new pane at the right of the spreadsheet.

• On this new panel, click the “Fill & Line” icon (a bucket dropping liquid), and select “No Line” in the Border section and “No Fill” in the Fill section.

Now that the blue columns are gone and we have left our "flying-bricks chart," we can delete the "Base" legend to hide all traces of the "Base" series completely.

Customize Your Chart and Do Some Formatting

Let's do some cleaning and adjust this bridge chart to look more like our desired waterfall chart, highlighting our values (more so with the Start and End values).

• Double-click the "Fall" series on the chart and select the "Format" tab under "Chart Tools."
• Click over the "Shape Fill" option in the Shape Styles section.
• There, you can pick the color that you want for your chart.

You can experiment with the column outline, too, and add some effects to them. This is done by using the “Shape Effects” and “Shape Outline” options on the Format tab to make changes. 

When you are done customizing the “Fall” series, you can do the same with the “Rise” series. Just remember to color-code them differently and clearly to make a correct comparison and assessment.

After this, you can get rid of white spaces between each column to make them stand closer and have a better look for measuring:

• Double-click on any of the chart columns so you can bring up the "Format Data Series" panel.
• Change the gap width to something smaller and close the pane. Now you will see that each column is close to one another.

• You can also clean the connector lines in the "Format Data Series" panel by unchecking the "Show Connector Lines" box.

Some flying bricks seem to be the same size. However, when looking at the data table, the represented values will differ. If you want a more precise analysis, you can add data labels to the columns.

• Right-click on the series you want to label and choose "Add Data Labels" from the menu. Repeat this process with the other series.

After you are done with the data label, you can remove unnecessary elements like zero values and legend. We recommend changing the chart title to something that would be more descriptive. With all set and done, this is how it should look!

It is a simple but very effective template, following the example that we ran before to make your own waterfall chart. Even more, it has not only the chart already displayed but also the data table shown at the side.

You can download it for free from the TeamGantt website.

Vertical Waterfall Chart Template

What about a change of look? Instead of doing everything over the X-axis, let's switch things and drop them over a vertical axis. This could ease the performance analysis at first glance by just rotating the view. 

The calculations of “Invert” and “Result” are reflected in the waterfall chart. All categories get subtracted, too, so you can see the contribution more clearly of each to the final result. 

You can download it for free from the ZebraBi site.

Multiple Waterfall Charts Template

With this template, you only need to click on a single cell in your data and insert the Zebra Bi Add-in integrated. Then, you can use the slider to get to the waterfall chart, select the custom calculation "Result" on both the first and last entry, and the Add-In will do the rest. 

It doesn't matter how many charts you have. They will be inserted within the same visual and automatically scaled between each other and put into the right perspective. 

You can also download it from the ZebraBi website.