Waterfall charts are a powerful tool for visualizing changes in data over time. From analyzing financial statements to tracking project progress, waterfall charts can provide valuable insights into complex datasets. Excel is a popular solution for creating waterfall charts, but many users struggle to create charts that are both visually appealing and easy to understand.

In this step-by-step guide, you'll learn how to create an impressive waterfall chart in Excel that will help you communicate your data effectively. Whether you're a beginner or an experienced Excel user, our guide provides all the necessary tips and tricks to create a stunning waterfall chart to impress your audience. So let's dive in and learn how to create an eye-catching waterfall chart in Excel!

Waterfall charts 101

A waterfall chart, also known as a cascade chart, is a unique chart that illustrates how positive or negative values in a data series contribute to the total. It's an ideal way to visualize a starting value, the positive and negative changes made, and the resulting end value. In a typical waterfall chart, the first column represents the starting value, and the last column shows the end value—the columns in between represent the contributing positive or negative values.

Note: Other fun names for waterfall charts include Mario chart and flying bricks chart, because individual chart elements resemble an old arcade game. 

While every bridge chart is a type of waterfall chart, not all waterfall charts are bridge charts. Bridge charts are a specific type of waterfall chart characterized by the inclusion of subtotals. While in a traditional waterfall chart, columns typically represent incremental changes leading from the starting to the ending value, a bridge chart takes this further by connecting these columns with lines and integrating subtotals at key points. This approach shows the flow of individual values and highlights the cumulative impact of these values at various stages. The 'bridge' in the chart is formed by these connections and subtotals, offering a clear visual pathway from the initial to the final value and detailing how intermediate sums contribute to this progression.

By understanding these nuances, you can choose the right type of chart for your data visualization needs.

Uses of waterfall charts

Waterfall charts are popular in the corporate and financial environment because they are useful for visualizing the positive and negative movements within a measured quantity or KPI, such as your Monthly Net Profit or Cash Flow.

Other examples of quantitative analyses where waterfall charts are used include:

• Visualizing profit and loss statements
• Comparing product earnings
• Highlighting budget changes on a project
• Analyzing inventory or sales over a period of time
• Showing product value over a period of time
• Creating executive dashboards

In a nutshell, use a waterfall chart whenever you want to show how a starting value increases or decreases through a series of positive or negative changes.

Tip: While the most typical waterfall chart has a starting and ending value, you can also create subtotals as visual milestones in the series. These show up as full columns. For example, you might want to use Net revenue and Gross Income as checkpoints between Gross Revenue and Net income starting and ending values.

How to create a waterfall chart in Excel

Before Office 2016, creating waterfall charts in Excel was a notoriously difficult process. Note that I used "creating" and not "inserting". That's right - you did not insert a waterfall chart; you created it.

Let's start with the process of creating a waterfall chart👇

The easiest way:

This is what your waterfall chart could look like in just a couple of clicks:

Amazing, right?

If you don't have time to tweak the default Excel waterfall charts, as explained further in the guide, and would like to add those advanced features to your waterfall charts fast, Zebra BI for Office is just what you need. See just how easy creating a waterfall chart can be:

Compared to the other methods explained, this looks so easy it's scary! By the way, you can do this in Office 2019 and 2021, Office 365, and Office for Mac! It's also possible to do the same thing in PowerPoint, too.

And now, let's check how to do it with the native Excel charts👇

Excel 2016 way:

Microsoft decided to listen to user feedback and introduced 6 highly requested charts in Excel 2016, including a built-in Excel waterfall chart.

No more templates, additional series, formulas, or tinkering with the charts. Just 2 clicks and your awesome waterfall chart is inserted.

Or is it? 🤨

While adding waterfall charts in Excel 2016 is a significant step forward, the current functionality still leaves much to be desired.

Alternative 10 steps to a waterfall chart

Here are some ways to help you create better Excel waterfall charts and some still missing things. If you change your mind and want to do it more easily, download our free quickstart file and save time for better things.

1. Remember to set the totals

Let's say we want this data table visualized with a waterfall chart: EBITDA of our fictional company for the years 2015 and 2016 and the individual contributions of 7 small business units to the change from 2015 to 2016.

This shouldn't be too hard. Click inside the data table, go to the "Insert" tab, click "Insert Waterfall Chart," and then click on the chart. Voila:

OK, technically, this is a waterfall chart, but it's not exactly what we hoped for. In the legend, we see Excel 2016 has 3 types of columns in a waterfall chart:

1. Increase
2. Decrease
3. Total

This is correct, but there are no Total columns in the chart, only Increase and Decrease. The first and last columns should be Total (start on the horizontal axis), and to set them as such, we have to double-click on each to open the Format Data Point task pane and check the Set as Total box.

You can also right-click the data point and select Set as Total from the list of menu options. 

Or, you can skip all this and use Zebra BI for Office to do the hard work for you.

Finally, we have our waterfall chart:

2. Ditch the clutter on your visualization

Data visualization best practice is to remove ALL elements from the visualization that are not absolutely necessary (if you're interested, you can learn more about this in our free webinar: Data visualization in depth).

Similar to other Excel charts, the default Excel waterfall chart also suffers from having too much clutter. The legend, the vertical axis, and labels, the horizontal grid lines - none of them contribute to the reader's better understanding of the data. If anything, they are a distraction.

So, let's remove all unnecessary elements and write our key message to the title. It’s a shame that the chart title cannot be inserted automatically from a cell.

Tip: To remove the distracting chart elements, right-click on each of them and then click "Delete".

Great, this is much better. However, it required additional work that would not be required if Excel defaults were better. We’ll show you how to do it better in Zebra BI for Office.

3. Break the axis to highlight contributions

This limitation is especially noticeable in waterfall charts because waterfall charts have essentially two different types of data:

1. Totals: usually the first and last column in a series. 
2. Contributions: the floating bricks comprise the “bridge” between the two totals.

A common problem is that contributions are often very small compared to totals. This is also apparent in our example (see the image above).

First, a point of order: this chart correctly visualizes the situation as the contributions ARE that small compared to totals. Our 2016 result is essentially the same as our 2015 result.

This visualization is also completely in line with IBCS Standards.

However, users (and their bosses) are sometimes more interested in contributions than totals and their relationship.

In this case, the only viable option would be to break the vertical axis and have the totals start at some value larger than 0. Let’s say 35,000. This highlights individual contributions but risks guiding unaware readers to false conclusions about the data.

You can again resort to using tutorials:

• http://peltiertech.com/broken-y-axis-in-excel-chart/
• http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html

Another somewhat simpler option is to do the following:

• Click on the chart to select it
• Re-add vertical axis: Go to Design >> Add Chart Element >> Axes >> Primary Vertical
• "Break" vertical axis: right-click on the vertical axis and click "Format Axis...", then under Axis Options, write "35000" under Bounds >> Minimum.
• Remove the vertical axis: right-click on the vertical axis and click "Delete"

This is the chart we end up with:

Now the contributions are much more prominent, but there's no obvious indication that the vertical axis does not start at zero, which is bad because the user does not draw the correct conclusion from the visualization.

4. Add relative contributions in percentages

When analyzing contributions, you're sometimes more interested in relative contributions (in percentages of the total) than in absolute contributions.

Unfortunately, if you want to do that in a default Excel waterfall chart, you're out of luck - you're stuck with displaying absolute contributions only. 

✔️ Look to the end of the article to see how easy this is to do in Zebra BI for Office.

5. Highlight differences between totals

Another thing you cannot do in an Excel waterfall chart is to display the total difference between 2015 and 2016 in our example.

You can see in the chart that the 2016 column is higher than the 2015 one (especially now that we cut the vertical axis). But by how much? You don't know the exact number unless you can do complex subtractions in your head. There’s also no way to display the relative percentage difference.

Since this difference between totals is essential, it's a major feature missing in Excel waterfall charts.

✔️ Of course, it's much easier to highlight the differences in Zebra BI for Office.

6. Use vertical waterfall charts

We know from the How to Choose the Right Business Chart article that horizontal charts (i.e. the charts that have a horizontal category axis) are used to display time-related data. For everything else, we should use vertical charts instead.

Waterfall charts are no exception. Strangely, in Excel 2016, there is no way to insert a vertical waterfall chart. While this feature has been requested, there's no indication of whether it will be implemented and when.

✔️ We prepared a demonstration in Zebra BI for Office, so you can see how to create an income statement with vertical waterfall charts.

7. Add (some) subtotals

Since we're visualizing income statements, some categories are sums of several other categories in a typical income statement.

For example, you can calculate a sum of all Operating Expenses (OpEx). This better visualizes the relationship between "Revenue" and "Earnings before interest and taxes" (EBIT). EBIT = Revenue - OpEx.

This is easy to do in a table - write a formula, and you're done.

When you create a waterfall chart in Excel? Not so much. It's apparently so hard to do it manually that no tutorial or template is available on the internet.

You can, however, enter subtotals and designate them as such in your waterfall chart. However, you need to calculate them yourself to ensure they are correct.

✔️ You can see how Zebra BI for Office automatically creates subtotals in this handy animated gif at the end of this article.

8. Customize your chart with colors

The default color scheme in Excel could be better. Visit the Chart Design tab and open the Change Colors gallery. 

Here, you can select a color palette. You can also choose a different theme on the Page Layout tab. To adjust the colors' use, click the Colors button and select Customize Colors at the bottom of the list. 

You can set it up to display positive values in green and negative values in red, which is a common approach in financial reporting. 

9. Turn connector lines on or off

Connector lines connect columns to show the movements in values in the chart. You can turn them on or off by right-clicking a data series to open the Format Data Series pane and checking/unchecking the Show connector lines box.

10. Scale your charts

Finally, we arrive at one prominent feature missing in Excel from the beginning: scaling multiple charts.

While this problem is not limited to waterfall charts, it's too important not to mention it here.

Making sure that all related charts in a report or dashboard are on the same scale is one of the most important concepts in data visualization.

If you don't synchronize scales, don't even insert the charts

All too often, you see two Excel charts side by side with completely different scales. While each is an adequate data visualization, you must ensure they are scaled once you put them side by side! Otherwise, don't insert the charts and leave the data in a table — or risk misleading/incorrect interpretation.

So, how do you synchronize scales of Excel charts? While the procedure is not particularly hard, it is time-consuming. It's a similar procedure that we used to break the axis.

Say we have these two default Excel waterfall charts, and we need to scale them:

The first step is to re-add the Vertical Axis on both charts.

1. Click on the first chart to select it
2. Re-add vertical axis: Go to Design >> Add Chart Element >> Axes >> Primary Vertical
3. Repeat for the second chart

This is what we have so far:

Now we have to adjust the scale of the right chart to be the same as the left. Right-click on the vertical axis and click "Format Axis...", then under Axis Options, write "600" under Bounds >> Minimum.

Remove the vertical axis from both charts (right-click on the vertical axis and click "Delete"), and we have our correct visualization:

OK, that wasn't too bad. What if you have a monthly report with 6 waterfall charts on it? Would you do this procedure for 6 charts every month when the data changes? I guess not.

Of course, you can automate this, but you have to use VBA to do it. If you don't want to use VBA, maybe this article from 2012 by Jon Peltier will help you.

3 steps to a perfect Excel waterfall chart with Zebra BI Charts (step-by-step guide)

Starting from chart selector:

Get Add-in (one-time only) -> search for “Zebra” and add “Zebra BI Charts”

1. Insert Add-in Zebra BI Charts -> Continue with a free license -> Chart Selector -> Contribution charts -> Bridge chart

2. Change data and define a waterfall chart, and let the chart tell your story

3. Add comments: Explaining the 'why' behind the data is necessary. After you get the variances that help you understand what is happening, you also want to present the why. Use integrated dynamic comments and point attention to the areas where it is needed.

Starting from your own dataset:

Create bridge waterfall charts from a single-value column | Zebra BI Knowledge Base

1. Get your dataset in Excel. Using Table formatting is recommended since modern Excel brings numerous benefits. Data inputs for this type of visualization are quite basic. Typically, they consist of one category column (Account) and one value column (Values). Additionally, you can also add a column with comments. Those will significantly improve your reader's experience and help you communicate meaningful insights clearly and concisely. Comment markers will ensure that the message in the comments is understood immediately.

2. Next, we need to click on the table (if formatted as an Excel table) or select the data range of all the cells and insert the Zebra BI Charts for Office add-in. By default, the column chart layout will be used. Zse the chart slider and click on it until the waterfall layout is shown. The standard behavior of the waterfall visual consists of increasing values resulting in rising green bars and decreasing values in falling red bars.

3. We must ensure every bar is correctly defined using the Invert and Result features. Both can be accessed by right-clicking on the category label. Bridge charts are also often used for comparing different data scenarios, like the previous year (PY), plan (PL), budget (BUD), forecast (FC), etc. Different scenario notations can be applied by right-clicking on the Result feature in this case.

Comparison: Why is doing waterfall charts better with Zebra BI?

• Easy to use (on-visual interactions) in just 3 steps instead of 10 with additional benefits.
• Not just faster and more interactive but more insightful. Comment and comment markers inside the visual will ensure that the message is clear immediately to everyone.
• Standardization: out-of-the-box formatting as per IBCS for consistency to reach actionable reporting.
• Automatically calculated and perfectly scaled variances.
• Interactive variances in "highlight differences" to show your growth rates between reference values (switch between real and also variances with just a click on the label).
• Responsive layouts: You can resize the visualization object, and the bridge chart will be automatically resized.

Combining everything mentioned will make your bridge chart more actionable and aligned with IBCS reporting standards.

Some more advanced things you can do much easier

You got it so far: everything is much easier using Zebra BI for Office instead of native Excel charts. See what else can be done 👇

Adding variances and difference highlights

The default Excel waterfall charts do not show crucial features for understanding your performance: variances (absolute & relative) and difference highlights. In Zebra BI for Office, both features are applied automatically.

You can also control whether an increase is positive or negative. We know that in some cases, like when it comes to costs, an increase is a negative development. You can right-click on the category where data should be inverted and select a custom calculation "Invert". This will automatically adapt the visualization to display the right context.

Another helpful feature we added is the difference highlights between starting and ending values. The difference highlight is added automatically and is on by default. By going to the settings in the global toolbar, you can also switch it off.

The previous example shows that the increase between EBITDA in 2020 and 2021 was 4.6k or 11.3%.

Creating an income statement with vertical waterfall charts

How about inserting a waterfall chart into an income statement? With the Zebra BI Tables for Office add-in, this is just a few clicks away.

The cool thing about this is that glancing at the waterfall chart lets you see how you're performing. The calculations like "Result" and "Invert" are reflected in the waterfall chart, and all categories get subtracted so that you can see the contribution of each to the final result.

Small multiples with waterfall charts

How about creating up to 200 waterfall charts at once?

Here are 6 waterfall charts in a few clicks:

You only need to select a single cell in your data and insert the Zebra BI Charts for Office add-in. Use the chart slider to get to the waterfall chart, use the custom calculation "Result" on the first and last entry, and Zebra BI will do the rest.

You will see the automatically calculated variances and highlighted differences. But, most importantly, no matter how many charts you have, they are all inserted within the same visual and automatically scaled between each other, which immediately puts the data into the right perspective.

Check this short video to see how easy is to create Waterfall charts with Zebra BI for Office:

Related Resources

Guide

March 7, 2023

PowerPoint Waterfall Charts: How To Create One That Doesn't Suck

Discover the art of creating dynamic waterfall charts in PowerPoint with our in-depth guide. Ideal for beginners and advanced users seeking to enhance their presentation skills!

Read more

Excel

Guide

August 24, 2023

The Ultimate 2023 Bar Chart Guide

Uncover the process of crafting effective bar charts. Dive deep into Excel and Zebra BI's rich capabilities for a superior data presentation.

Read more

Power BI

Guide

June 29, 2023

How to create a Horizontal Waterfall Chart in Power BI: Ultimate 2023 Guide

Discover how to create a Horizontal Waterfall Chart in Power BI for impactful, dynamic data visualization. Transform your reports with ease.

Read more

Excel

Guide

March 31, 2023

Stacked Bar Chart in Excel: How to Create Your Best One Yet

Stacked bar charts allow you to break down your data into segments, which provides your stakeholders with a comprehensive view of the information at hand. Learn how to create your best stacked bar chart in Excel with next-level features using Zebra BI for Office.

Read more