Last November my husband and I were on our honeymoon in Maui. One of the main activities we wanted to do was hike near the waterfalls. We dreamed of swimming in the cascading water at sunrise.
When you hear the word “waterfall”, it is usually what you imagine. However, today we are going to talk about another type of waterfall – the waterfall chart.
Waterfall charts are a data visualization resource that can help you collect and track important data such as traffic goals and lead generation. Below, let’s take a look at what a waterfall chart is, how to read one, and how to create one.
What is a waterfall chart?
A waterfall chart, also known as a waterfall chart or a bridge chart, is a chart that shows you how an initial value is affected by intermediate values - positive or negative – and results in a final value. Typically, waterfall charts are used to analyze data sequentially.
In marketing, a waterfall chart can show the number of leads, traffic sources, or blog views over a period of time. Specifically, you can use a waterfall chart to show how your blog traffic has increased or decreased over the past year, giving values month after month.
You can potentially use line charts, bar charts, and even bullet charts to display this kind of data. But waterfall charts have the advantage of showing your wins as they are affected by losses over time.
Why use a waterfall chart?
You should use a waterfall chart instead of other types of charts when trying to visualize data that is experiencing both gains and losses. This is especially useful if you want to see how a loss affects a later value.
One of the reasons that waterfall charts are effective in marketing is that they give context to the data they report. More data visualizations suffer from ignoring the circumstances that cause numbers to decline or increase, such as seasonality.
For example, let’s say you create a waterfall chart of your Twitter followers over time. Rather than using a line graph that shows your total number of users over time, a waterfall chart shows how much you’ve lost – and how that affects subsequent numbers.
At first glance, these graphics can be difficult to read. Below, let’s take a look at how to read a waterfall chart.
How to read a waterfall chart
Reading a waterfall chart will seem foreign to you at first if you’ve never done it.
However, it is important to remember that you are reading it sequentially, from left to right.
For example, let’s say you track blog traffic from month to month. On the far left, you will have the total traffic from the previous year. Then you include the gains and losses month after month for the entire year. At the end of the graph, you will see the total traffic for the year.
This is what it looks like:
Notice how each value ends where the previous value ended or started. In January there was a gain of 5,000 visitors, but in February and March there was a loss of 2,000. The April traffic value takes this into account starting from the figure of -2,000 and amount from there.
Essentially, a waterfall chart is supposed to show you where you started and where you ended up, with details on how you got there. In this example, you can see which months got the most traffic versus which months lost traffic. It might help you see seasonal adjustments, while keeping the big picture in mind.
Now you might be wondering, “This chart seems difficult to create. How can I create mine in Excel? Below we’ll go through the simple process to create your own waterfall chart.
How to create a waterfall chart in Excel
- Create a table with the values you want to display in your waterfall chart.
- Highlight the columns and rows containing your data.
- Go to Insert > [Waterfall chart symbol] > Cascade.
- Format your waterfall chart colors under “Chart Design” in the top ribbon.
- Adjust the caption if necessary.
- Add more values over time to keep tracking progress.
Don’t you really know how to do it? Below we include a template and additional instructions.
Bonus: You’ll also find instructions for creating waterfall charts in Google Sheets, in case that’s your favorite spreadsheet.
Waterfall Excel Template
1. Create a table with four columns.
Before you can create a waterfall chart, you need to create an array of values that you want to represent on your chart.
For example, are you tracking blog traffic numbers? Or maybe you are looking for leads generated by a certain marketing campaign? Either way, before you can create a waterfall chart, you need to put your data together.
All you need to do is open Excel or Google Sheets and start entering your data manually. When entering your data, be sure to note the difference between positive and negative values. To indicate a negative value, simply add a minus sign in front of the number.
For this model, we will be tracking blog traffic. Note: All figures are arbitrary and do not reflect traffic to a blog.
Create four columns. The first two columns will not have a title. In cells A2 to A15, write START, then every 12 months, then END.
In cell C1 write “GAIN” and in cell D1 write “LOSS”.
From there, place your traffic numbers. What traffic are you starting with? Write this in cell B2, next to START. Then, for each month, write down how much you won or lost.
Add everything together, including negative values, and place them in cell B15, next to END.
2. Highlight all of your data, then insert your waterfall chart.
Now that you have your values, highlight the table you just created. In Google Sheets, go to Insert → Graphic → Waterfall chart.
This will create a waterfall chart and the chart editor will appear on the right side. When the Chart Editor appears, make sure “Waterfall Chart” is selected under Chart Type.
In Excel, go to Insert → [Waterfall chart symbol] → Cascade.
Your chart will be automatically created based on the values of our model.
3. Format your waterfall chart.
At this point all the hard work is done. All you have to do is format your graphic and make sure it looks the way you want it to.
In Google Sheets, click the three dots in the top right corner of the chart and tap Edit Chart. You will come to the chart editor. Here you can choose the colors of your bars, adjust your legend or add gridlines. Most likely, the main thing you’ll want to do here is adjust your caption.
In Excel, you click on the chart and then choose “Chart Design” and “Format” from the top ribbon to make the chart look the way you want.
The process of manually creating a waterfall chart can be tedious. Fortunately, you can also create a waterfall chart using a dedicated dashboard tool. For example, HubSpot offers marketing offers dashboard and reporting software that you can use for create graphics. Here’s how.
How to create a chart in HubSpot
1. Access the analysis tools.
Once logged into your portal, hover over the Reports tab and click on Analysis Tools.
2. Choose what you want to track.
Then you will choose what you want to track. You might want to analyze blog traffic like we did in the example above. Or maybe you want to look at the analytics for a certain campaign.
Either way, you can choose what you want to track in the analytics tools.
3. Choose the type of chart.
Finally, all you have to do is choose the style chart you want. Currently, you can choose from an area chart, column chart, line chart, or combo chart. You’ll want to choose “Column,” which is closest to the waterfall chart format.
Now that you have a waterfall chart, it’s time to analyze it.
To repeat, here’s how to understand your waterfall chart:
- A waterfall chart shows a series of negative and positive values. Each value has an impact on the value that follows it.
- For example, if one week you lose 3 leads, the next value will take this into account. If you gain 5 leads, the waterfall chart will use the value -3 as a starting point, so your end point is a gain of 2 leads.
- Each column is color coded to distinguish positive values from negative values.
And here’s how to analyze it.
Examine the time ranges with the greatest losses.
Which months or weeks did you experience the biggest losses? It is important to see and understand these numbers to get the most out of your chart. From there, you can troubleshoot or come up with a new strategy for those months.
Look at the time ranges with the biggest payouts.
Conversely, look at the time slots that saw the biggest gains. You’ll want to emulate what you’ve been doing during those months – or research the trends that gave you a boost during those times.
Look at the net change over the entire time range.
From start to finish, what difference did you see? Was it a positive or a negative difference? Could your business have seen better results?
Look at the week to week gains and losses after implementing a new strategy.
After you’ve created a new strategy, it will be helpful to use a waterfall chart to see its impact on you from week to week, whether you have more positive or negative results.
Use a waterfall chart to better analyze your performance
To make sure you are looking at the full scope of your analyzes, we recommend that you create a waterfall chart. It is a useful data visualization tool that can help you understand your analyzes sequentially. You no longer need to rely on typical line charts – with a waterfall chart you will understand your wins and losses over time on a much more granular level.
Editor’s Note: This post was originally published in May 2011 and has been updated for freshness, accuracy, and completeness.