Waterfall Chart

Excel waterfall chart (bridge chart) shows how a start value is raised and reduced, leading to a final result.

Before diving into the details, we want to clarify that Excel 2013 does not support the waterfall chart by default (as a built-in chart type). In this case, you must invest more time in creating the chart. In Excel 2016 and above, the waterfall chart is ready to use when your Office package is installed.

Table of contents:

  1. What is a Waterfall chart?
  2. The logic behind the Waterfall Chart
  3. How to create a waterfall chart in Excel 2016, Excel 2019, or Microsoft 365?
  4. How to create a waterfall chart in Excel 2010 or Excel 2013?

What is a Waterfall Chart?

The Waterfall chart visualizes a starting value and the cumulative effect of a series of positive and negative values, leading to a final value. The bridge chart provides insight into the individual components (cost, sales, etc) or influences contributing to a change between the starting and the ending data points.

The logic behind the Waterfall Chart

Now we know that the Waterfall chart displays how positive or negative values in a data series contribute to the total. Before we start creating a bridge chart in Excel, take a closer look at the main parts of the chart.

  1. Starting value: The chart begins with a column (starting point) representing a baseline or initial value.
  2. Intermediate steps are a series of positive and negative changes. If we add a positive value (for example, the sales are higher than the last month, a rising column shows it. Sometimes, we have negative changes; the height of the next column corresponds to the negative change. Each new column begins where the last one is finished, forming steps.
  3. End Point: The last column of the bridge chart shows the final amount, which is the start amount adjusted by all the changes in between.

You can use color-coded stacked column charts to recognize positive and negative values. This chart type is popular when you want to create P&L reports or dashboards. This tutorial is a part of our chart templates series.

How to create a waterfall chart in Excel 2016, Excel 2019, or Microsoft 365?

Steps to create a waterfall chart in Excel:

  1. Select the range that contains two columns (labels and values).
  2. Select the Insert Tab.
  3. Under the Charts Group, choose the Waterfall Chart icon to insert a new chart.
insert a waterfall chart excel

Your chart is ready, but take a closer look at the details. The default chart is a very basic implementation. For example, you can not use calculated fields. Furthermore, subtotals are missing by default. To improve the chart, you have to apply additional customizations.

default chart

Add Subtotals

In the example, you want to add a calculated field to summarize the data between January and June. What are subtotals? Subtotals are visual checkpoints (milestones) in the chart and make the graph easily readable. Insert a new row and calculate the subtotal using the =SUM(F3:F8) formula. The chart will reflect quickly.

adding subtotal

To change the inserted bar to subtotal, you can apply the ‘Set as Total’ function.

Using the ‘Set as Total’ command

Steps to create Subtotals for the Waterfall chart:

  1. Select the bar that you want to convert to a subtotal
  2. Right-click on the selected bar
  3. Select the ‘Set as Total’ command
how to set as total

There is much room for improvement!

improved waterfall chart

How to create a Waterfall chart in Excel 2010 or Excel 2013?

Sometimes, you must insert a waterfall chart but have an outdated Excel version. No problem with that; use the following step-by-step tutorial and build a chart.

#1 – Create a helper table

First, create a helper table and insert the following columns: ‘Cumulative,’ ‘Start/End,’ ‘Before,’ and ‘After.’ After that, use the following formulas to calculate the values:

  • In cell D3, apply the =SUM($C$3:C3) formula.
  • Start value (E3) = D3, End Value (E16) = D15
  • Before series: =D3 and copy the formula down
  • After series: =D4 and copy the formula down
create a helper table

Press the Control key, hold down, and select four columns like in the picture below.

#2 – Insert a stacked column chart

Next, locate the Insert Tab on the ribbon and insert a stacked column chart.

insert a stacked column chart

The result looks like the picture below:

waterfall chart fundamentals

#3 – Change the Chart Type and create a combo chart

Now we have three stacked column series, but we need columns only for the Start / End values, so change the chart type. Right-click on the Chart and chooseChange Chart Type.’ Use line charts for the Before and After series. Next, we will use the combination chart to create the waterfall chart. Do not forget to leave the ‘Secondary Axis’ checkbox unchecked.

change chart type

#4 – Add Up / Down Bars

We need to apply a little trick to create “bars” from the line chart. First, select the line chart series. Next, select the “Chart Design” Tab and add new chart elements, Up / Down Bars.

add up and down bars

#5 – Format the chart

To get the bars closer, decrease the gap between the data points. Use the ‘Format Data Series’ task pane. Then, under the ‘Series Options,’ add 100% to the gap width.

modify gap width

Finally, hide the line series. Then, under the ‘Series Options’, apply ‘No line’. The last step is to apply blue for the start and end bars, green for the positive bars, and red for the negative bars.

waterfall chart for excel 2013

Bridge Chart Add-in for Excel

If you have special requirements, use our advanced chart add-in. UDT fully supports the horizontal and vertical waterfall chart. You can build your bridge chart in seconds.

How to insert a complex waterfall chart quickly without manual data entry? First, let’s see another example that uses multiple subtotals that we want to calculate using Excel.

Select the range, then click on the UDT Tab. Next, select the Waterfall Chart icon, choose your style (horizontal or vertical), and click the icon. The add-in will calculate the subtotals, and you will get the result ASAP. Great, it is not? The chart is compatible with Excel 2013 and newer versions.

UDT-waterfall

Advanced Techniques of Waterfall Charts in Excel

Building on the comprehensive overview of waterfall charts in Excel, this extended addition aims to dive deeper into the nuances of waterfall chart creation, offering advanced techniques, practical tips for efficiency, and ideas for leveraging these charts in various analytical contexts. Waterfall charts, with their unique ability to visualize the cumulative effect of sequential data points, are indispensable tools for financial analysis, project management, and performance tracking.

Customizing Waterfall Charts for Enhanced Clarity and Impact

  1. Color Coding for Immediate Insight: Beyond basic positive and negative color schemes, consider using additional colors to distinguish between different types of data points (e.g., revenues vs. expenses, or planned vs. actual values) to make the chart more intuitive at a glance.
  2. Data Labels for Detail: Adding data labels that show the value of each step or the change from the previous step can provide clarity. Custom formatting of these labels can highlight key information, such as unusually large increases or decreases.
  3. Dynamic Waterfall Charts: Integrate your waterfall chart with Excel Tables or defined Name ranges that dynamically update. This ensures that your chart reflects the latest data without manual adjustments, ideal for dashboards or recurring reports.

Leveraging Waterfall Charts in Business and Project Management

  • Financial Statements and Analysis: Use waterfall charts to break down the components of financial statements, such as the progression from gross to net income, or to detail variances between budgeted and actual figures. This visual representation can significantly enhance the comprehensibility of complex financial data.
  • Project Milestones and Performance Tracking: Waterfall charts can effectively track project milestones and budget utilization over time. They visualize stages of project completion or investment against returns, offering a clear picture of project health and progress.
  • Operational Analysis: In operations, waterfall charts can illustrate process flows, efficiency gains, or losses, and the impact of various operational changes over time. This helps in pinpointing areas of improvement and in making data-driven decisions.

Efficiency Tips for Creating and Using Waterfall Charts

  1. Template Creation: If you frequently use waterfall charts with similar structures, create and save a template. This can significantly speed up the process of generating new charts, ensuring consistency across presentations or reports.
  2. Use of Add-ins and Tools: For those using versions of Excel that do not natively support waterfall charts, or for users seeking more flexibility, consider third-party add-ins that offer enhanced functionality. These tools can save time and provide additional customization options.
  3. Integration with Dashboards: Waterfall charts can be powerful components of Excel dashboards, providing at-a-glance insights into financial health, project status, or performance metrics. Link your waterfall charts to dashboard controls like slicers or drop-downs to make them interactive.

Navigating Challenges and Limitations

  1. Handling Complex Scenarios: When dealing with complex data sets or scenarios that involve numerous intermediate steps or categories, carefully plan your chart setup to avoid clutter and ensure readability. Grouping smaller categories or using annotations can help maintain clarity.
  2. Performance Considerations: Complex or data-heavy waterfall charts can impact Excel’s performance. Regularly review and optimize the underlying data and chart settings, especially when integrating into larger models or dashboards.
  3. Educating Stakeholders: While waterfall charts are highly informative, they may be unfamiliar to some stakeholders. Including brief explanations or legends can aid in interpretation and ensure that your charts effectively communicate the intended insights.

Conclusion and Further Exploration

Waterfall charts are a dynamic tool in Excel’s visualization arsenal, offering nuanced insights into data that would be difficult to achieve with other chart types. By mastering the advanced techniques and applications of waterfall charts, Excel users can elevate their data analysis, reporting, and decision-making processes.

Exploring beyond basic usage to incorporate dynamic data sources, customize visual elements, and integrate charts into broader analytical frameworks opens up new possibilities for data visualization and interpretation. Whether for financial analysis, project management, or operational review, waterfall charts provide a structured, clear, and impactful way to present data-driven stories.

Download the practice file.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.