Waterfall charts, also known as bridge charts can provide a clear view of how an initial value is affected by a series of intermediate positive or negative values. In this post, I offer some tips on when to use them, how to shape the data, and how to fine-tune them for a final presentation.
The Basics
You might use a waterfall chart to show the profit and loss of a business, starting with revenue (a positive value), then subtracting costs (negative values) to arrive at net income. The bars in a waterfall chart are color-coded to distinguish between positive and negative values, making it easy to see how individual values contribute to the total. In essence, a waterfall chart is a special kind of bar chart (esp. stacked bar) that tells a story behind changes in some key metrics. Typically the chart starts out at a baseline (zero or an initial quantity), then shows the activities or changes until the last quantity (the final value).
When to use a Waterfall
Use this chart when there’s a start and final numeric metrics you want to present and highlight the activities or events in-between containing changes, especially increases and decreases. Waterfall charts are best used when you’re dealing with sequential data where the order of values is important.
They’re used to visualize a single series of data, showing how an initial value is affected by various positive and negative changes. This makes them ideal for understanding the cumulative effect of sequential data points, such as financial data over time. If you have multiple numeric data series, other types of charts might be more suitable. For instance, a stacked column chart or a line chart can effectively display multiple series of data on the same chart, allowing for easy comparison between different series.
Shaping the data
For waterfall charts, we need to shape the data so that there is one category column (labels) and one numeric column. If there are multiple numeric columns, it may not work correctly as it will only render the first numeric column. So, have all the numbers in a single column (either positive or negative) and the last row should be the final value.
Types of Waterfall charts
There are 3 main types of waterfall charts we see in business use.
Build-up Waterfall:
This shows how different segements contribute to the total and it’s shown as a build-up, like a stairs going up (left to right). This is essentially a stacked chart where the bottom parts are set to No Fill for subsequent bars. This is typically used for employee distribution in a large company (e.g. different departments, each dept showing number of employees or cost of employees, etc.)
Movement Waterfall:
This shows movement of different metrics and arrives at the final value. It starts with an initial value (e.g. starting income), then next bars show activities (e.g. expenses, revenue), and the last bar shows the final value (e.g. Net Income for the month).
Gap Waterfall:
This is used to show a build-up toward the total, and then use a gap bar between the Total and the Target. The gap shows by how much the target was missed or exceeded. This is typically used to highlight the total avaiable supply (of anything) vs supply gap for example, or revenue totals vs targets.
Example
We have a dataset on headcounts for a company’s campus for the year. Just showing the starting and ending headcount does not tell a good story. We can show the activities that happen between starting of measurement and the final value and show them as a Waterfall chart. Numbers that are negative are reductions in headcount, others are additions. The dataset is shown below:
Next, select the data range, and insert a Waterfall chart in Excel. The first version of the chart will look like this:
Make sure to set the Headcount Start and Headcount End as total, that’s a necessary theme for a proper Waterfall chart. To do this:
Click on the Headcount Start bar to select only that bar. Right-click->Format Data Point.
Then on right pane, check [x] Set as total. And do the same for the starting bar (Headcount Start).
Remove the horizontal gridlines, legend, and y-axis label.
Next, remove the connecting horizontal gray lines between bars. Click on a bar to select all bars, right-click->Format Data Series. On right pane’s Series options tab, Uncheck Show connector lines. This removes the unnecessary lines connecting the top of a bar to the next bar’s bottom.
In the same formatting pane, reduce the Gap Width to something smaller that looks right (e.g. 5%).
It’s a good idea to make the first bar and the last bar same color because you’re highlighting the endpoints and the bars in-between changes or activities which should be in different colors.
As needed, differentiate the positive bars from negative bars by color by selecting each and formatting them in the Format pane.
Finally, add the final touches:
Format Data Labels positions: Click on a data label value on chart to show the Format Data Labels pane.
Then choose [x] Center for all. Then select the first bar (Start) and set its label to Inside End. Repeat to Inside End for the last bar (End).
Edit the title to make it relevant.
Add callouts as needed to explain changes (reductions/increases). Add textboxes on chart, shapes (arrows, etc), as needed to tell the story.
Add Gridlines such as Primary Major Vertical to show the segmentation along x-axis clearly.
Reduce the gap between bars to 5%.
My final chart would looks like this:
The chart clearly shows the starting headcount (1200) and the ending headcount (1950), in-between it presents the events that occurred such as New Hiring, transfers internally into the campus and out of that campus, and attritions of employees. It also shows the callouts to explain the changes for some of the key events. That’s exactly what Waterfall chart is for.
The callouts are not a default option for a Waterfall chart in Excel at this time but you can insert Shapes->Callouts and edit them to tell a story. Also, notice that I reformatted the negative numbers (Transfer OUT, Attrition) for the bars in parentheses instead of showing the minus sign. That is entirely up to you.
I hope this was interesting for you!
Related:
