Does the mere thought of visualizing your project’s financial progress make you dizzy? Feeling like cascading toward chaos instead of cruising with clarity? Let’s stop that waterfall of worries right here!
Despite what the name might suggest, waterfall (also known as cascade) charts are not exclusive to adherents of the Waterfall project management method. These versatile visualization tools are a lifeline for any project manager looking to keep a keen eye on those pesky profit and loss factors.
In this article, we’ll show you how to create a waterfall chart in Excel, point to some of Excel’s less obvious snags, and offer practical alternatives to keep you afloat. By the end, you’ll be able to paddle through your financial data with the elegance of a seasoned kayaker, taking your project management prowess to a whole new level. How To Create a Waterfall Chart in Excel: Steps & Templates
Waterfall charts are an excellent way to visualize how a starting value is affected by a series of positive and negative changes I often use waterfall charts in Excel to show the impact of credits and debits on an account balance, to display inventory or revenue fluctuations over time, and to present proposed versus actual budget numbers
In this comprehensive guide, I’ll walk you through how to make a waterfall chart in Excel using your own data. I’ll also provide tips to customize the look and layout of your waterfall chart. Follow along with the step-by-step instructions and screenshots below to learn exactly how to create and format waterfall charts like a pro!
What is a Waterfall Chart?
A waterfall chart displays a running total as positive and negative values are added or subtracted It allows you to clearly see the cumulative effect of a series of adjustments on an initial value
Waterfall charts typically have the following elements:
-
Starting value This is the initial or baseline value for your data series,
-
Intermediate values: These are the positive and negative changes. Intermediate values usually “float” above the horizontal axis.
-
Total values: Significant subtotals or totals like net income can be anchored to the axis.
-
Ending value: This is the final value after all adjustments.
-
Connector lines: These visually connect each intermediate value to the next.
The columns in a waterfall chart are often color-coded – for example, using green for positive values and red for negatives. This makes it easy to visually distinguish between increases and decreases at a glance.
When to Use a Waterfall Chart
Waterfall charts are perfect for showing:
- Account balances with credits and debits
- Budgeted amounts versus actual expenses
- Changes in inventory quantities over time
- Revenue with positive and negative adjustments
- Profitability from sales and costs
- Headcount changes with new hires and attrition
Essentially, anytime you want to demonstrate how an initial value is impacted by a series of intermediate positive and negative changes, consider using a waterfall chart. The visual representation makes it easy for viewers to grasp the cumulative effect.
Waterfall Chart Data Setup
Before creating a waterfall chart, your data must be properly structured. Here are some tips for setting up your waterfall chart data in Excel:
- Put the starting value in the first row.
- List intermediate values in subsequent rows.
- Input positives as normal numbers and negatives as negative numbers.
- Show significant subtotals or totals on their own rows.
- End with a final value or ending balance row.
- Include labels in the first column to identify each data point.
Your data should have at least one starting value, a mixed set of intermediate increases and decreases, and an ending value. Subtotals are optional.
Here is an example dataset with numbers representing the monthly changes in inventory for a retailer:
| Label | Value |
|---|---|
| Starting Inventory | 10000 |
| January Receipts | 5000 |
| January Sales | -3000 |
| February Receipts | 7000 |
| February Sales | -4000 |
| March Receipts | 4000 |
| March Sales | -5000 |
| Ending Inventory | 15000 |
This sample data has the initial inventory quantity, positive new receipts, negative sales, and final inventory amount. It contains all the necessary components to create a waterfall chart.
How to Make a Waterfall Chart in Excel
Creating a waterfall chart in Excel is super easy thanks to the built-in waterfall chart type. Just follow these steps:
-
Select your data: Highlight the data you want to include in the waterfall chart. Be sure to include columns with the labels and values.
-
Insert a Waterfall chart: Go to the Insert tab > Charts group, click the Waterfall chart icon, and select Waterfall from the drop-down menu.
![Insert Waterfall Chart][]
-
Set totals to axis: If your data contains totals like Ending Inventory, double-click the total column to open the Format Data Point pane. Check the Set as total box. ![Format Total][]
-
Customize design: Use the Chart Design and Format tabs to customize colors, add titles, change layouts, and more.
And that’s all there is to it! In just a few clicks, you’ll have a waterfall chart generated from your data.
Here is an example waterfall chart created from the sample inventory data:![Sample Waterfall Chart][]
Now let’s look at some key ways you can customize the look and feel of your waterfall charts.
Waterfall Chart Design Tips
Waterfall charts have a distinct look with floating columns and connector lines. You have many design options to create stylish, meaningful charts that fit your presentation needs.
Show or Hide Connector Lines
The default waterfall chart includes connector lines that visually link each data column. To toggle these on or off:
- Right-click any data column.
- Choose Format Data Series.
- Check or uncheck the Show connector lines box.![Show Connector Lines][]
Change Column Colors
Make increases and decreases pop by using red and green column colors.
- Select the waterfall chart.
- On the Design tab, pick a premade color theme.
- Or manually select red fills for negatives, green fills for positives.![Column Colors][]
Show Data Values
Display the data numbers within each column for clarity.
- Select columns.
- Right click and toggle Value on or off.![Column Values][]
Add Data Labels
Include data labels outside the columns to make your waterfall chart more informative.
- Click chart and select Format tab.
- Under Data Labels, choose a label position like Outside End.![Data Labels][]
Adjust Chart Scale
Modify the vertical axis minimum or maximum to better fit your data range.
- Double click the vertical axis.
- In the Format Axis pane, adjust the Minimum, Maximum, or Major Unit values.![Chart Scale][]
Change Number Format
Display currency, percentages, or other number formats for clarity.
- Select data labels.
- Pick a number format like Currency or Percentage in the ribbon.![Number Format][]
Take advantage of these design options to highlight important details and make your waterfall chart easier to interpret.
Create a Waterfall Chart by Hand
While the built-in waterfall chart type makes it easy to generate the initial chart, you can also create a waterfall chart manually using stacked columns and connector lines. This allows for more customization flexibility.
Follow along to see how to build a waterfall chart from scratch in Excel:
-
Insert stacked column chart: Highlight data, insert a stacked column chart on the Insert tab.
-
Add connector lines: Right click a column, select Format Data Series > check Connector Lines.
-
Color columns: Set positives to green fills, negatives to red fills.
-
Position totals on axis: Double click totals, check Set as Total in Format Data Point pane.
-
Design tweaks: Add titles, data labels, number formats, etc.
Constructing the waterfall chart manually takes more work but provides full creative freedom. The individual columns, connector lines, colors, positioning, and labels can all be adjusted.
Troubleshooting Waterfall Charts
Waterfall charts are one of the easiest chart
Uses of Waterfall charts
Waterfall charts have a broad range of applications, making them a versatile tool in every project manager’s toolbox across various fields.
Here are the main areas where these charts are used:
- Finance: Clearly show profit and loss statements
- Retail and sales: Compare earnings from different products
- Manufacturing and logistics: Track changes in inventory over time
- Marketing and product management: Show how a product’s value changes over time
- Executive decision-making: Often used in executive dashboards to present key stats and changes to stakeholders
Waterfall Project Management Template by ClickUp
Imagine trying to tackle a huge jigsaw puzzle. You wouldn’t just dive into the pile of pieces all at once, right? You’d probably start with the edge pieces to build a border and then gradually fill the middle.
With ClickUp’s Waterfall Project Management Template, you take one task, smash it out of the park, then move on to the next.
Each task is boxed out with its own details—how long it’s supposed to take, how far you’ve gotten, and any other bits and bobs you need to keep in mind. Tasks can have five statuses to track your progress, from “to do” to “done.” You can easily see what’s ticked off and what’s next on the agenda.
If you’re just dipping your toes in the project management pool or getting the hang of the Waterfall methodology, no worries. This template is as friendly as they come, with four views, including a calendar. It’s like your project road map, showing you all the stopovers and the final destination.
How to create a waterfall chart in Excel
How do I create a waterfall chart?
Start by selecting your data. You can see below that our data begins with a starting balance, includes incoming and outgoing funds, and wraps up with an ending balance. You should arrange your data similarly. Go to the Insert tab and the Charts section of the ribbon. Click the Waterfall drop-down arrow and pick “Waterfall” as the chart type.
How to create a waterfall chart from a stacked column in Excel?
You just need to make the Base series invisible to get a waterfall chart from a stacked column. Click on the Base series to select them, right-click and choose the Format Data Series… option from the context menu. The Format Data Series pane immediately appears to the right of your worksheet in Excel 2013 / 2016. Click on the Fill & Line icon.
How do I create a waterfall chart using a stacked bar chart?
To build a custom waterfall chart using a stacked bar chart, follow these steps. Open a blank Excel worksheet and input your data with initial and final values along with the incremental changes. Highlight your data range and insert a stacked bar chart from the Insert tab by pressing Column, then selecting the Stacked option.
Does excel have a waterfall chart template?
The problem is that Excel doesn’t have a built-in waterfall chart template. However, you can easily create your own version by carefully organizing your data and using a standard Excel Stacked Column chart type. Let’s create a simple sample table with positive and negative values to understand the things better.
