How to Build a Waterfall Chart in Excel
Natively built into MS Excel since 2016, the waterfall chart (a data visualization tool) is gaining momentum among users from both academic and business sectors. It's one of the most visually descriptive charts that can add value to your presentation and aid your decision-making by giving context to the data it's reporting.
With applications in a wide range of scenarios—from helping interpret financial statements and catching risk factors to facilitating navigation of massive troves of census data—a waterfall chart makes analyzing critical datasets as effortless as possible.
In this article, we’ll dive deeper into how to build a waterfall chart in Excel. Later, we'll shed light on how building a waterfall chart with think-cell helps you communicate with your audience more effectively.
But first…
What is a waterfall chart?
The waterfall chart is a graphical representation of data that helps discern how an initial value of an item is increased or decreased by immediate positive or negative values.
Unlike a bar chart which only displays the total value of an entity in each category, a waterfall chart disaggregates and displays all unique factors and explains how their cumulative impact contributes to the total figure.
It's so named because the shape of the chart resembles a waterfall.
While the whole columns show the initial and final values, the floating columns in this chart represent the intermediate positive and negative values. These values can be either time-based or categorical.
Relatively extreme positives and negatives can cause the total value to go above or fall below the reference axis at different points.
When to use waterfall charts?
Waterfall charts were brought into the mainstream by McKinsey & Company—a global management consulting firm—that included these charts in its presentations.
Since then, it has been widely used by consulting firms, financial institutions and accounting departments as a more convenient way of tracking how a net realizable value is achieved through consecutive gains and losses over a certain period or between budgeted and actual costs.
Today, businesses in the banking, consulting, corporate, and academic sectors find different types of waterfall charts highly effective in facilitating financial, performance, trending, and quantitative analysis.
It can be a great way to get a closer look at sales earnings and sales pipeline, evaluate the profit or loss of a company over time, track the changes in operating costs (OPEX) of a company, understand cash flow statements, and more.
But, what are the reasons behind the increasing popularity of this visualization technique?
Benefits of using waterfall charts
Let's dive deeper into the benefits a waterfall graph can offer:
Sheds light on the progress of a project
When you want to track down the critical steps that are influencing your project output the most, especially when you have numerous elements contributing to the data you're analyzing, then using the waterfall chart can be a viable solution.
As we know, a waterfall chart breaks down an entire project or process into a sequence of steps and turns their progress into an interactive visual.
In other words, you can better understand how the interconnected steps impact the progress of your project over time, therefore specifying the sectors that need tweaking. With this easily discernible chart, you’ll no longer be bogged down by complex details.
Understanding the picture behind those numbers helps you route your efforts in the right direction. Plus, visualizing how the project has changed over time helps understand where it's gone off track.
Boosts success while reducing loss
Accessing accurate insights into all contributing factors is vital to driving measurable (and quick) ROI in your business.
And for a financial institution looking to drive success, understanding how and when performance can be boosted through positive changes while mitigating imminent risks is critical.
Thanks to waterfall charts, you can promptly act on your continuously changing data for improved performance.
Again, a waterfall chart can help quickly address risks by offering a deeper insight into the negative factors; the more time you take to solve a problem, the worse it can get.
With a waterfall chart in hand, you know you have the next step in place. Plus, you can efficiently allocate your time for each task. As a result, you can better concentrate on managing potential risks that could lead to decreased business performance.
Helps track effects of increments and decrements over time
When it comes to interpreting the reasons behind the net change in an entity's value between the opening and closing data points, a waterfall chart makes the most sense.
By building stacked bars, a waterfall chart shows the running total of an entity as values get added or deducted.
For anyone looking to hit a specific business goal, this feature is highly beneficial. For instance, let's assume you are considering investing in new stock and need to visualize the profit you can expect making on your stock investment. For this, all you have to do is input your stock value, the total time you'll hold it, and your expected rate of return (RoR) in a waterfall chart, and the chart will do the rest to exhibit the output.
Offering loads of advantages, waterfall charts have become a proven recipe for quickly understanding the progression of a project and figuring out when a project has gone off track.
However, Excel's standard waterfall charts come with some limitations.
Drawbacks of a waterfall chart
Let's take a look at some challenges of building waterfall charts in MS Excel:
- As we have already said, the waterfall chart is available by default with the latest versions of Excel. With no built-in option, creating a chart in Excel 2013 and earlier editions is a labor- and time-intensive task.
- Excel waterfall charts cannot reference cells for titling. Moreover, unlike other Excel charts, it doesn't support dynamic titles. For dynamic titling, you have to delete the default title, add a text box, and connect it with a cell.
- Even though you can hide or show the connectors in an Excel waterfall chart, you cannot modify their format, e.g. the color.
- The biggest shortfall of the built-in waterfall chart with Excel is that sometimes, it may show errors when the final figure is a negative value.
But how to build a waterfall chart in Excel?
Let's dig deeper into the process of creating a waterfall chart in the latest Excel versions:
Step 1: Create a data table
The first step to building a waterfall chart in Excel is to create a data table. Since the 2016 version, building a data table has become seamless—no need to add the rise, fall, or base columns. For a build-up chart, ensure the data table starts with the initial value followed by the positive and negative changes in the subsequent columns.
Step 2: Select your data
Now select the data you want to build a chart with; then, click the Insert tab and the Charts menu. Next, go to the Waterfall chart drop-down menu, pick Waterfall as the required chart type, and a waterfall chart will be inserted in your Excel spreadsheet.
Step 3: Format data points
Instead of considering the initial and ending data points as totals or subtotals, Excel identifies them as part of the sequential positives and negatives. If the starting and ending bars/segments "float", aren't marked as total according to the chart legend, or don't correlate with the numbers in the vertical axis, you'll have to format them.
For this, double-click a data point to bring up the Format Data Point task pane. Now, check the Set as Total box.
Now you know how to build a basic waterfall chart with Excel.
Best practices for building a waterfall chart
You can add more value to your graph and build an impressive chart by following these best practices:
- Apply subtotaling as visual checkpoints in your chart, and make it easily discernible.
- Add more detailed labels to your data columns.
- Color the segments to make each change more discernible.
How can think-cell help?
Now that you know how to build a waterfall chart in Excel, you might be wondering if there’s an easier way to build a chart that doesn’t require so much clicking and formatting.
If you need to make professional-looking PowerPoint presentations with stunning charts that can impress your audience while significantly saving you time, think-cell can seamlessly integrate your Excel data into stunning PowerPoint presentations.
Preferred by 89 of the top 100 US blue chip companies, think-cell offers 40+ charts, including waterfall, and dozens of data-based visual enhancements while saving 70% of your working time on charts compared to PowerPoint.
Let’s see how think-cell helps create better waterfall charts
Unlike other third-party software products for PowerPoint, think-cell enables data visualization of even the most complex data using only native PowerPoint charts.
And compared to the built-in waterfall chart in MS Excel, the think-cell waterfall tool offers more functionality, which makes it an effortless tool to work with.
For example, when you create a waterfall chart in PowerPoint, you need to set totals by selecting each data point separately. The waterfall chart in Excel can't reflect these changes made in PowerPoint. Even changing a single value in the floating columns requires you to recalculate all values in the total and subtotal columns, making the process cumbersome and lengthy.
In contrast, building bar and column waterfall charts with think-cell is a breeze—you don’t even have to calculate total values or use formulas. All you have to do is type an “e” (for “equals”) in the total and subtotal cells. All e-segments are computed and automatically updated by the think-cell system as data changes.
Plus, unlike Excel waterfall charts, think-cell allows you to easily change the color and style of the connectors.
Impress your audience with clear messaging and industry-standard visuals. Request a demo today to learn more about think-cell waterfall charts.