Waterfall chart: how to plot in Excel

This visualisation is also known as a waterfall chart, "Waterfall," bridge chart, or "bridge." It is made up of vertical columns: the first and last columns indicate the initial and final values, respectively, while the intermediate columns show the elements that influenced the outcome. It's usual to colour them in different hues to improve clarity.

This type of visualization is commonly used for several purposes:

• Show Process Dynamics: for instance, to illustrate the movement of funds, investment flows, and more.
• Compare Target vs. Actual: it helps in comparing the target value with the actual value, revealing where things went wrong.
• Understand Factors Influencing Change: it demonstrates how and due to what factors the initial value transitions to the final value, allowing for factor analysis.

Individual product sales, income and expenses, metric changes within a certain timeframe, and so on could all be included in the initial value being changed. Companies such as Apple, for example, employ waterfall charts to highlight the factors that contributed to changes in sales volume. It identifies which product lines contributed to revenue declines and which were more popular within a certain time period. This study helps to determine what needs to be improved in order to turn negatives into positives.

According to statistics, the revenue from sales for the second quarter of 2020 compared to the same period in 2019 remained almost unchanged — $58.015 billion compared to $58.313 billion, resulting in a growth of just 0.5%.

By examining data for each product line, it becomes apparent that sales levels decreased by 6.7% for iPhone, 10.3% for iPad, and 2.9% for Mac. However, other devices such as Apple TV, Apple Watch, Beats, iPod, and proprietary accessories experienced a 22.5% increase in sales, while services saw a 16.6% increase. As a result of these variations, the overall sum remains in the positive range, albeit to a minor extent.

Its purpose is to identify the causes (factors) that influence the actual outcomes of industrial, economic, and financial activities. The waterfall chart is used for visualizing this type of analysis.

This method is most commonly used for analyzing the fulfillment of production plans, investment control, and financial flows.

Let's consider another simple example of factor analysis. Let's say there is a planned profit value of 140,000 rubles. At the end of the year, the actual income amounted to 171,000.

The first and last columns represent the plan and the actual values, respectively. The intermediate columns represent the factors influencing the outcome. Green color indicates growth indicators, while red color represents decreases.

As we can see, due to the increase in the dollar exchange rate, the prices of components have risen, and other expenses have also increased significantly. However, thanks to the increase in production volumes and the opening of branches that brought in greater profit, the expansion of the product line, and the growth in demand for new products, the actual income at the end of the year exceeded the planned amount. The figures presented in the form of a chart make these indicators visual and simplify their analysis.

Simple "waterfall" charts can be created in regular Excel or by using the Think-cell add-in. For more advanced reports and larger datasets, working with the Power BI platform is much more convenient.

This visualization was introduced in Microsoft Office in the year 2016 and is, of course, available in all newer versions. However, it can also be created in older versions of Excel. It might take a bit more time, but the visual result will be indistinguishable.

Let's take an example of the steps involved in building a cascading chart in Excel 2013 or older versions of Excel.

In the initial version, we have 2 columns showing the change in cash flow from gross to final net profit.

Without having the required diagram in the program's functionality, we will use a classic histogram. However, you need to format the table in a specific way beforehand.

To do this, create additional columns and manually transfer the data from the "Amount" column: negative values go to the "Minus" column (without the sign!), and positive values go to the "Plus" column. Remove the "Amount" data related to the changes while keeping the indicator of the initial gross profit. This will give us a table on which we will base our chart.

The "Amount" column should not remain empty. In the cell below the gross profit indicator, enter the formula: =C3-D4+E3. To make sure everything is correct in your case, ensure that the cell order in the formula matches the example.

So, in our case, it looks like this. Place the cursor in the second cell of the "Amount" column, below the gross profit indicator. Enter the formula: reference the previous cell in this column → subtract the value from "Minus" in the active row → add the previous cell in the "Plus" column.
Drag the formula down the entire column to get the following:

It's from this table that we'll create the waterfall chart. Select the table and choose "Insert" → "Waterfall Chart". You'll get a chart like in the image 2:

Excel automatically colored the data from the "Total" column in blue, negative values ("Minus") in orange, and positive values ("Plus") in gray.

We have to conduct some changes on this histogram. Finally, we want to see the first and last bars, as well as the orange and grey numbers on our graph.
However, the blue bars (save the first and last) must be made invisible.

• Click on any of the blue bars.
• Ensure that they are all selected.
• From the "Format" menu, choose "Shape Fill," then "No Fill."

Then, manually select only the first and last bars (by double-clicking on each of them), and using the same method, restore their original fill color.

Let's use the traffic light rule and make positive values green, and negative values red. To do this, click on any orange bar, go to the "Format" menu, choose "Shape Fill," and select red. Then, do the same to change the gray bars to green.

The "Waterfall" chart is ready. Now, let's edit it and make it visually appealing.

For better clarity, let's make the bars wider so that the visualization visually resembles a bridge connecting the initial and final values. One of its names is the bridge chart, as its concept and appearance correspond to the idea of connecting two end supports.

To do this, use the "Format Data Series" menu and reduce the gap width to 20%.

Now let's work with the text elements. Write a title and make it large, then remove the legend. Increase the font size of the X-axis labels: it's important that they remain horizontal for readability. Add data labels and manually remove those that relate to the non-filled parts. Increase the font size of the labels.

Finally, we игшде a traditional waterfall chart that visually depicts all stages of cash flow. You may compare not only the starting and ending values, but also which elements contributed to the outcome: what led to profits and what led to losses.

Important note: when you click on the link, you might see an incorrect visualization (due to Google Drive quirks). In the "File" menu, click "Download" and select Microsoft Excel (XLSX): work with the downloaded document, as everything will be displayed correctly there.

It's lot easier here. Choose the data range in the table on which you wish to base the chart. Select the "Waterfall" symbol from the main ribbon. You should obtain a clear result (Figure 2).

True, it, like any other automatically generated visualisation, requires modification and refining.

We go through the same processes as we did in previous versions of Excel. alter the colour of the columns, alter the title, and remove the legend.

Important: we must individually change the colour for each column in this case, or else they will all be recolored. Make sure the axis labels are horizontally positioned and not at an angle.

Important note: when you follow the link, you will see an incorrect visualization (this is a quirk of Google Drive). In the "File" menu, click "Download" and choose Microsoft Excel (XLSX): work with the downloaded document.

The video provides a clear step-by-step guide to building the waterfall chart. This is a lesson from my older course. In the new training program, it's also available, but in better quality and considering the modern capabilities of the software.

Unfortunately, Microsoft has yet to realise its goal of answering the query "How to Create a Waterfall Chart in Excel?" immediately and effortlessly.

It is far more convenient to employ additional tools when creating sophisticated pivot charts.

Think-cell is widely used by businesses to produce waterfall charts. It is a tool that allows you to easily build 2D charts in PowerPoint from Excel data straight on the presentation page. It is professional, accurate, and in accordance with company requirements. You can also do it 3 to 5 times faster than in Excel.

Think-cell features simple parameter settings. The visualisations are synchronised with the data in Excel. Labels and summary values are recognised automatically. Shapes and text blocks can be readily connected.

But what about large datasets, pivot tables, and the requirement to generate reports fast and painlessly? Microsoft designed the Power BI analytics platform for skilled professionals and businesses that value their time.

Power BI is a collection of programmes and services that enable you to quickly extract information from any data source, consolidate it into a single data model, execute essential calculations, make charts, and generate reports based on that data.

Power BI allows you to create a Waterfall chart faster than Excel. You must import data from your table into the Power BI platform. Then, using the "Visualisations" panel, pick the element and set particular settings. Add the desired factor to the "Distribution" area to complete the comprehensive chart.

Data preparation and storage may be tough for beginners. We thoroughly address all of the subtleties throughout the course. I describe how to reduce data preparation workload and give advice on whether to produce the chart directly in the Power BI platform or use other interfaces, depending on the situation.

This user interface is simple. To install this (or another) chart type that is not included in the basic functionality, navigate to the "Visualisations" menu and select "More visualisations."

A page with numerous additional visualizations from partners will open. Using the search in the upper right corner, find the "Simple Waterfall" chart and install it. A new icon will appear in the list of visualizations (Figure 2).

Now you need to add this visual element "Simple Waterfall" to the canvas. Specify the categories for analysis, define the colors for positive and negative factors, mark the final and intermediate values, add tooltips and a context menu. As usual, don't forget to pay attention to fonts, axis labels, data labels, and remove any unnecessary elements.

Zebra BI is the most powerful data visualization technology for creating waterfall charts. It offers an interface that extends the functional capabilities and allows you to create perfect financial reports.

For example, in Power BI, comparing data from different categories isn't very convenient. With Zebra BI, you can do it in a matter of minutes. You simply add another parameter to the existing element.

Automatically, a series of visual elements is generated with the same scale and axes, making them easy to compare. With just one click, you can switch between breakdowns by days, months, quarters.

Several values frequently outnumber the others. These columns may stand out and misrepresent the differences when making a waterfall chart. Zebra BI includes a "column break" tool that aligns everything with a single click.

It is recommended that you try creating a waterfall chart in both Excel and Power BI to understand how to do so and compare the ease of the procedures.

A more complex chart, such as one with several negative components, columns that "pop out" and distort visibility, and intermediate totals, is desirable. Perform analysis based on several criteria, combine comparable graphs, review data from various angles, and, most importantly, do it as rapidly as possible.

You'll quickly realise that making a Waterfall chart in Excel isn't as simple as using a dedicated tool. Traditional methods of chart generation are greatly outperformed by Power BI.

To master Power BI to its fullest, we offer guidance through my online course. In just 2 months (or maybe even 1), you'll acquire skills to create interactive dashboards, make clear and beautiful analytical presentations, understand the nuances of each type of chart, and be able to generate reports of any complexity in 15 minutes before a meeting.

This type of visualization is a simple and clear way to understand how a series of positive and negative changes impact the initial value.

● Simplicity of execution. Creating a Waterfall chart in older versions of Excel requires some effort, but in newer versions or specialized programs, the chart can be created with just a few clicks, as we saw earlier.

● Clarity and comprehensibility. Thanks to color coding, it's immediately clear which factors have a positive and which have a negative impact on the final result.

● Ability to compare target and actual values (plan/actual) while simultaneously seeing the factors that influenced their difference.

● Wide range of applications. Despite not being as popular as traditional histograms, the Waterfall chart can be applied in various situations: from monitoring sales of specific product categories to controlling financial and investment flows.

● Ability to analyze only specific data formats: you need to have initial and final values (or target/actual), as well as intermediate values that lead to the final result.

● Not suitable for more detailed analysis, as you can't filter or display additional data.

● Need for additional interfaces or add-ins. The capabilities of most programs by default only allow for creating the simplest variations. I've demonstrated how to create a Waterfall chart in both older and newer versions of Excel and explained the additional interfaces needed in Power BI to create more complex and diverse charts.

● We've seen that "Waterfall" (also known as "Cascade," "Bridge," or "Bridge" chart) is a convenient and visual way to represent the process of changing a certain indicator, taking into account the influencing factors.

● We've learned how to emphasize the reasons for changes in an indicator.

● We've explored what factor analysis is and how to visualize it using this type of visualization.

● We've examined how to create a Waterfall chart in Excel (in different Excel versions) and in Power BI, as well as how to perform a plan-fact analysis and use additional interfaces.

We believe that the peak of Waterfall chart's popularity is still ahead: while not everyone is using it right now, its convenience, clarity, and ease of implementation will help it secure a worthy place among the leading chart types.