waterfall chart add in excel

Excel Waterfall Chart Add-In: Visualize Data like Never Before

By Laura Lewis
AD

If you're working with numeric data in Excel and looking to visualize it in a way that's both informative and visually appealing, you might want to consider using a waterfall chart. This type of chart is particularly useful when you want to display a running total that accumulates along the axis, often used in finance or data analysis to show how a net change builds up over time or across categories.

Microsoft Excel doesn't natively support waterfall charts, but you can create one using a combination of features and a bit of creativity. In this guide, we'll walk you through the process of creating a waterfall chart in Excel, step by step.

Understanding Waterfall Charts

A waterfall chart, also known as a catechol plot or flying brick chart, is a type of chart used to display a succession of values, where the cumulative effect is shown. It's particularly useful for visualizing data where the flow of the numbers is more important than their individual values.

AD
How to Create a Waterfall Chart in Excel
How to Create a Waterfall Chart in Excel
Waterfall Chart in Excel
Waterfall Chart in Excel
Excel Waterfall Charts (Bridge Charts) - Peltier Tech
Excel Waterfall Charts (Bridge Charts) - Peltier Tech

Waterfall charts typically consist of horizontal bars that 'fall' from left to right, with each bar representing a stage in the process. The total value is the sum of all the partial values along the way, and any positive or negative changes are clearly visible.

When to Use a Waterfall Chart

Waterfall charts are most effective when you want to show the cumulative effect of sequential positive or negative increments. They're commonly used in finance to show profit and loss, cost breakdown, or revenue streams. However, they can also be used in other fields where tracking changes over time or stages is crucial, such as in project management or sales analysis.

Create an Excel 2016 Waterfall Chart
Create an Excel 2016 Waterfall Chart
Interactive Waterfall Chart Dashboard - Excel Campus
Interactive Waterfall Chart Dashboard - Excel Campus
How to Create a Waterfall Chart in Excel (2 Easy Methods) - ExcelDemy
How to Create a Waterfall Chart in Excel (2 Easy Methods) - ExcelDemy

Here's an example where a waterfall chart can be useful: Imagine you're trying to illustrate how a product's final price is composed of its cost price plus sequential increases due to tariffs, packaging, and marketing. A waterfall chart would clearly show the cumulative effect of each increase, making the data easy to understand.

When Not to Use a Waterfall Chart

Waterfall charts are not suitable for all types of data. They're not well-suited for displaying simple tables of data, where each row represents a distinct entity. They're also not the best choice for showing data with a large range of values, as the visual impact of the chart can be diminished.

AD

Instead of a waterfall chart, you might want to consider using a standard bar chart, line chart, or other suitable chart type for these types of data. Always consider your audience and what type of chart would best illustrate your data to suit their needs.

How to Create a Waterfall Chart in Excel - Excel Dashboard School
How to Create a Waterfall Chart in Excel - Excel Dashboard School
Excel Waterfall Chart Template With Negative Values At Mallorysoraya
Excel Waterfall Chart Template With Negative Values At Mallorysoraya
How to create waterfall chart in Excel
How to create waterfall chart in Excel
How to Make a Stacked Waterfall Chart in Excel - Earn and Excel
How to Make a Stacked Waterfall Chart in Excel - Earn and Excel
Waterfall Charts in MS Excel - Printable Formats
Waterfall Charts in MS Excel - Printable Formats
Excel 2010 Waterfall Chart Template At Mallorysoraya
Excel 2010 Waterfall Chart Template At Mallorysoraya

Creating a Waterfall Chart in Excel

Now that we understand what waterfall charts are and when to use them, let's dive into creating one in Excel. Since Excel doesn't have a built-in waterfall chart option, we'll use a workaround involving stacked column charts and some manual formatting.

For this example, let's assume we have data in columns A and B, where A contains the stage names and B contains the values for each stage.

AD

Step 1: Prepare Your Data

Before creating the chart, ensure your data is arranged in the following format:

How to Make a Vertical Waterfall Chart in Excel (with Easy Steps) - ExcelDemy
How to Make a Vertical Waterfall Chart in Excel (with Easy Steps) - ExcelDemy
StageValue
Initial Value50
Addition 130
Addition 220
Addition 3-10
Final Value90

The final value should be the sum of all the stages before it. The values can be positive or negative.

Step 2: Create a Stacked Column Chart

1. Select your data (including the headers).

AD

2. Go to the 'Insert' tab, and in the 'Charts' group, click on 'Column' and select 'Stacked Column'.

3. Excel will insert a stacked column chart based on your data. The first stage (Initial Value) will be at the bottom, and all other stages will be stacked on top of it.

Step 3: Separate the Bars

To create the waterfall effect, we need to separate the bars. For this, we'll use negative values for the final value and then manually adjust the y-axis minimum and maximum values.

1. Change the 'Final Value' in your data from 90 to 900 (or any large positive number). This will create a tall bar at the end.

2. In your chart, right-click on the tall bar and select 'Format Selection'. In the 'Format Selection' pane, under 'Series', set the 'Fill' to 'No Fill' and change the 'Border Color' to the same color as your chart's background. This will give the appearance of the final value being 'cut off'.

Step 4: Adjust the Axes

1. Right-click anywhere in your chart and select 'Select Data'. In the 'Select Data Source' dialog box, click on the button labeled 'Switch Row/Column'. This will change your chart from stacking columns vertically to stacking them horizontally, which is more typical of a waterfall chart.

2. Right-click on your chart again and select 'Format Selection'. In the 'Format Selection' pane, under 'Axes', set the 'Minimum' and 'Maximum' for both the 'Horizontal' and 'Vertical' axes to 0. This will remove any negative values from your chart, giving it the waterfall effect.

Congratulations! You've just created a waterfall chart in Excel. This chart will make it easy for your audience to understand the cumulative effect of each stage on the final value.

Remember, the key to effective data visualization is choosing the right tool for the job. Waterfall charts are powerful, but they're not always the best choice. Always consider your audience and the story you want to tell with your data. With a little creativity and the right tools, Excel can help you create engaging and informative visualizations.

AD