Waterfall charts in Excel - Step-by-Step Guide

Introduction

Waterfall charts help you see how a starting value changes step-by-step to reach a final value. They show increases and decreases clearly, making it easy to understand changes in things like budgets or sales over time.

When you want to show how monthly expenses add up to a total budget.

When you need to explain how sales grew or shrank over several quarters.

When you want to visualize profit changes from revenue and costs.

When you want to break down the impact of different factors on a final number.

When you want to present step-by-step changes in financial reports.

Steps

Step 1: Select

- the range of cells containing your data including categories and values

The data range is highlighted and ready for chart creation

Step 2: Click

- the Insert tab on the Ribbon

The Insert tab options appear

Step 3: Click

- the Waterfall or Stock Chart icon in the Charts group

A dropdown menu shows chart types including Waterfall

Step 4: Select

- Waterfall chart from the dropdown menu

Excel inserts a Waterfall chart based on your selected data

Step 5: Click

- any column in the chart that should be a total (like starting or ending value)

The column is selected

💡 Right-click the column and choose 'Set as Total' to make it a subtotal or total column

Step 6: Use

- Chart Design and Format tabs to customize colors, labels, and styles

The chart updates visually to your preferences

Before vs After

Before

A table with categories like 'Start', 'Sales', 'Returns', 'Expenses', and 'End' with numbers in adjacent cells

After

A Waterfall chart showing columns for each category with increases in green, decreases in red, and totals in blue, visually illustrating how the start value changes to the end value

Settings Reference

Set as Total

📍 Right-click a column in the Waterfall chart

Marks a column as a subtotal or total to anchor the waterfall steps

Default: Unset as Total

Chart Title

📍 Chart Design tab > Chart Elements > Chart Title

Add or edit the chart title to describe the data

Default: Show

Data Labels

📍 Chart Design tab > Add Chart Element > Data Labels

Show values on each column for clarity

Default: None

Chart Colors

📍 Chart Design tab > Change Colors

Change the color scheme of the chart for better visual appeal

Default: Default theme

Common Mistakes

Not setting the starting or ending columns as totals

Excel treats all columns as changes by default, which can misrepresent the actual totals

Right-click the starting and ending columns in the chart and choose 'Set as Total' to anchor the waterfall correctly

Selecting incomplete data range missing category labels

The chart will not show meaningful labels, making it hard to understand the steps

Always include both the category names and their corresponding values when selecting data

Summary

Waterfall charts show step-by-step changes from a start value to an end value.

You create them by selecting data and choosing Waterfall chart from the Insert tab.

Remember to set starting and ending columns as totals for accurate display.