How to Create a Stacked Waterfall Chart in Excel?

A Stacked waterfall chart in Excel helps visualize how individual components contribute to changes in a total value across categories or over time.

In this blog, you will learn:

Table of Content:

1. Overview
2. Video Tutorial:
3. Why You Need a Stacked Bridge Chart in Excel?
4. Ideal Use Cases
5. Methods for Creating a Cascade Chart in Excel
6. How to Create a Stacked Waterfall Chart in Excel with Multiple Series?
7. Tips And Best Practices
8. FAQs
9. Wrap Up

Overview

A Stacked waterfall chart in Excel shows changes in values over time or between multiple data sets. It can show the cumulative effect of a data series or compare multiple data series.

In this chart, we use a combination of stacked bars and columns to show the data points. The bars and columns are placed side by side to build the chart.

The chart also includes a total bar, which shows the sum of all the data points in a category or series. Also known as a Waterfall chart, it displays the bridge that connects the starting point to the endpoint.

It’s a great way to illustrate the contributions of different values to a total sum. Data analysis gets easier by displaying the individual items as bars and the total sum as a total bar.

Video Tutorial:

Here’s a quick video showing how to make this chart in Excel from scratch. The video discusses the following details:

Why You Need a Stacked Bridge Chart in Excel?

A Stacked bridge chart in Excel is used to visualize how sequential positive and negative values contribute to a final total. It helps in understanding the impact of individual factors or categories on the overall outcome, making it easier to track changes and see how various elements accumulate over time.

These types of charts are particularly useful for illustrating financial performance, project progress, or any situation where cumulative effects need to be presented.

Ideal Use Cases

• When visualizing changes in values over time or between multiple data sets.
• It is ideal for data sets with multiple series. You can use it to show the cumulative effect or compare multiple data series.
• Comparing performance across different categories or periods.
• To analyze financial performance or to compare sales of different products or services.
• To compare the performance of different departments or divisions within a company.
• It’s a great way to compare the contributions of different values to a total sum. Thus, a great data visualization tool to use when analyzing data that you can break down into several parts.
• When you need to explain a large amount of data to your audience.

Methods for Creating a Cascade Chart in Excel

Manual Method

You can manually build a cascade chart in Excel to visualize sequential changes across multiple categories. Follow these steps:

1. Prepare Your Data: Create a table with your values and include extra columns for Start, End, Increases, and Decreases.
2. Calculate Values: Set the initial Start value. For each following row, calculate the new Start by adding the previous row’s Increase and subtracting the Decrease.
3. Insert a Stacked Column Chart: Highlight your data, go to the Insert tab, and select Stacked Column from the Charts group.

Format the Chart:

• Hide the base: Right-click the Start series (bottom segment of the bars) and set the fill to No Fill to create the floating effect.
• Mark totals: Right-click the End bars and set them as totals.
• Adjust colors: Assign distinct colors for increases and decreases to improve readability.

Using an Add-In

Add-ins like ChartExpo make it much easier to generate cascade charts in Excel:

1. Install the Add-In: Download and install the preferred chart add-in.
2. Select Your Data: Highlight the dataset you want to visualize.
3. Create the Chart: Open the add-in’s toolbar, select the chart, and follow the prompts to generate the chart.
4. Customize if Needed: Many add-ins allow additional customization, such as changing colors, adding totals, or modifying labels directly within the chart.

How to Create a Stacked Waterfall Chart in Excel with Multiple Series?

First, you will need to organize your data into columns and rows. Each row should have a different series of data. Once you organize the data, you can create the chart. Upon creating the chart, you can customize it by adding labels, changing the colors and font sizes, or adding a legend.

How to Install ChartExpo on Excel?

1. Open your Excel application.
2. Open the worksheet and click on the “Insert” menu.
3. You’ll see the “My Apps”.
4. In the office Add-ins window, click on “Store” and search for ChartExpo on my Apps Store.
5. Click on the “Add” button to install ChartExpo in your Excel.

ChartExpo charts are available in Microsoft Excel. Please use the following CTA’s to install the tool of your choice and make creative data visualizations in Excel in a few clicks in your favorite tool.

Example

Here is the cash flow data for a certain business. Let’s create a Chart of this data:

• To get started with ChartExpo, install ChartExpo in Excel.
• Now Click on My Apps from the INSERT menu.

• Choose ChartExpo from My Apps, then click Insert.

• Once ChartExpo is loaded, you will see a list of charts available.

• Then, scroll through the numerous charts until you see your required chart.

• After highlighting data from the sheet, click on the option “Create Chart From Selection“.

• The result will be an image that looks like the one below.

Insights

• The total revenue of $56.6k is derived from sales of $36.87k and services of $19.69k.
• Mobile sales are the major contributor at $20.03k.
• PCs accounted for the majority of services at $9.08k.
• Most of the revenue was consumed by Taxes and Marketing, which amounted to $11.44k and $9.64k, respectively.
• Transportation was the least spending at $4.05k.
• Ultimately, your net income was $12.18k.

Tips And Best Practices

• Make sure the chart is easy to understand by including a total bar and using a consistent scale.
• Create a visually appealing chart using a consistent font size, color scheme, and layout.
• Ensure you have organized the data in columns and rows. You should have one column for each data point and one row for each series.
• Format the data correctly. Use a consistent font size and color scheme to make the chart easier to read.
• Use consistent units. Different units can make your chart confusing. Consistent units will make it easier for your audience to navigate the chart.
• Identify the overall trend. Your Chart should help you see the overall trend of your data. Look for a pattern in the data, and see if you can identify a trend.

FAQs

What types of data can you visualize with a Stacked Waterfall Chart in Excel?

It is commonly used to visualize and analyze financial data, such as profit, revenue, expenses, and cash flow. This is also useful for visualizing and analyzing datasets with multiple categories. For example, expenditures by department or revenue by product category.

How to use the Stacked Waterfall Template in Excel?

To use this chart Excel template, first open it and enter your data into the predefined categories and subcategories. The template automatically builds the chart based on your inputs.

Customize colors and labels as needed to highlight positive and negative values. This template makes it easy to visualize complex data and track how different components contribute to overall changes.

Wrap Up

Now, you have learned how to create this chart in Excel. It’s a great way to compare the contributions of different values to a total sum.

ChartExpo is a great resource for creating a Stacked waterfall chart in Excel. It offers a variety of charts and diagrams to choose from and provides step-by-step instructions for creating the chart.

These charts help you to visualize the cumulative effect of positive and negative values. Creating a Chart is a great way to visualize data intuitively.

In addition, it’s also a great way to identify areas of improvement quickly. For efficiency, ensure the data is in columns and rows, and the chart is easy to interpret.