Last November, my husband and I were on our honeymoon in Maui. One of the main activities we wanted to do was a hiking tour near the waterfalls. We dreamt of swimming in the cascading water at sunrise.
When you hear the word “waterfall,” that’s typically what you imagine. However, today, we're going to talk about a different kind of waterfall — the waterfall chart.
Waterfall charts are a data visualization resource that can help you gather and track important data such as traffic goals and lead generation. Below, let's review what a waterfall chart is, how to read one, and how to create one.
In marketing, a waterfall chart could display the number of leads, traffic sources, or blog views over a period of time. More specifically, you could use a waterfall chart to showcase how your blog traffic has increased or decreased in the last year, giving values month over month.
You could potentially use line charts, bar charts, and even bullet graphs to show this type of data. But waterfall charts have the advantage of showing your gains as they’re impacted 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 experiences both gains and losses. It’s especially useful if you want to see how a loss affects a subsequent value.
One of the reasons that waterfall charts are effective in marketing is because they give context on the data it's reporting. Most data visualizations suffer from ignoring circumstances that result in a fall or rise in numbers, such as seasonality.
For instance, 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 many you lost — and how that impacts subsequent figures.
At first glance, these charts can be difficult to read. Below, let's review how to read a waterfall chart.
How to Read a Waterfall Chart
Reading a waterfall chart will seem foreign at first if you've never done it.
However, it's important to remember that you're reading it sequentially, from left to right.
For instance, let's say you're tracking blog traffic from month to month. On the far left, you'll have the total traffic from the previous year. Then, you'll include the gains and losses month over month for the entire year. At the end of the chart, you'll see the total traffic for the year.
Here's what that looks like:
Notice how each value ends either where the previous value ended or began. In January, there was a gain of 5,000 visitors, but in February and March, there was a loss of 2,000. April’s traffic value takes that into account by starting from the -2,000 figure and going up from there.
Essentially, a waterfall chart is supposed to show you where you started and where you ended up, with details of how you got there. In this example, you can see which months gained the most traffic compared to the months that lost traffic. This could help you see seasonal adjustments, while also keeping the big picture in mind.
Now, you might be wondering, "That chart looks difficult to make. How can I make my own in Excel?" Below, we'll review the simple process to creating your own waterfall chart.
Not sure how to actually get it done? Below, we include a template and further instructions.
Bonus: You’ll also find instructions for creating waterfall charts in Google Sheets, in case that’s your preferred spreadsheet software.
Waterfall Excel Template
1. Create a table with four columns.
Before you can build a waterfall chart, you'll want to create a table of values that you want represented on your chart.
For example, are you tracking blog traffic numbers? Or perhaps you're looking at leads generated from a certain marketing campaign? Either way, before you can create a waterfall chart, you'll need to gather your data.
All you have to do is open Excel or Google Sheets, and begin manually entering your data. When you enter your data, make sure you denote the difference between positive and negative values. To denote a negative value, just add a minus sign in front of the number.
For this template, we’ll track blog traffic. Note: all numbers are arbitrary and not reflective of traffic to any blog.
Create four columns. The first two columns will have no headings. In cells A2 to A15, write START, then all 12 months, then END.
In cell C1, write “GAIN,” and in cell D1, write “LOSS.”
From there, place your traffic numbers. How much traffic are you starting with? Write that in cell B2, next to START. Then, for each month, write how much you gained 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 → Chart → Waterfall chart.
This will create a waterfall chart and the Chart Editor will show up on the right-hand side. When the Chart Editor comes up, make sure that “Waterfall Chart” is selected under Chart Type.
In Excel, go to Insert → [Waterfall chart symbol] → Waterfall.
Your chart will automatically be created based on the values in our template.
3. Format your waterfall chart.
At this point, all the hard work is done. All you have to do is format your chart and make sure it looks how you want.
In Google Sheets, click on the three dots in the upper right-hand corner of the chart and hit Edit Chart. You'll get 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 to adjust your legend.
In Excel, you’ll click on the chart, then choose “Chart Design” and “Format” on the top ribbon to make the chart look the way you want it to.
The process of creating a waterfall chart manually can be a hassle. Luckily, you can also create a waterfall chart using a dedicated dashboard tool. For example, HubSpot offers marketing dashboard and reporting software that you can use to create charts. Here's how.
How to Create a Chart in HubSpot
1. Go to analytics tools.
Once you log in to your portal, hover over the Reports tab and click into the Analytics Tools.
2. Choose what you want to track.
Next, you'll choose what you want to track. Perhaps you want to analyze blog traffic like we did in the example above. Or maybe you want to review analytics for a certain campaign.
Either way, you can choose what you want to track in the Analytics Tools.
3. Choose the chart type.
Lastly, all you have to do is choose the style chart you want. Right now, you can choose between an Area chart, Column chart, Line chart, or a Combination chart. You’ll want to choose “Column,” which is closest to the waterfall chart format.
Waterfall Analysis
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 impacts the value after it.
- For example, if one week you lose 3 leads, the next value will take that into account. If you gain 5 leads, the waterfall chart will use -3 value as a starting point, so that your ending point is a gain of 2 leads.
- Each column is color-coded to distinguish positive from negative values.
And here’s how to analyze it.
Examine the time ranges with the greatest losses.
Which months or weeks did you see the greatest losses? It’s important to see and understand these figures to get the most out of your chart. From there, you can troubleshoot or come up with a new strategy for those months.
Examine the time ranges with the greatest gains.
Conversely, look at the time ranges that saw the greatest gains. You’ll want to emulate what you did during those months — or research trends that gave you a boost during those times.
Examine the net change over the entire time range.
From start to end, how big of a difference did you see? Was it a positive or negative difference? Could your business have seen better results?
Look at week-to-week gains and losses after implementing a new strategy.
After creating a new strategy, it’ll be helpful to use a waterfall chart to see how it impacts you from week to week — whether you’re seeing more positive or negative results.
Use a Waterfall Chart to Better Analyze Your Performance
To ensure you're examining the full scope of your analytics, we recommend creating a waterfall chart. It's a worthwhile data visualization tool that can help you understand your analytics sequentially. No longer do you have to rely on typical line graphs — with a waterfall chart, you’ll understand your gains and losses over time at a much more granular level.
Editor's Note: This post was originally published in May 2011 and has been updated for freshness, accuracy, and comprehensiveness.
