Waterfall charts provide a clear visual breakdown of how an initial value transitions to a final amount through various positive and negative changes. Excel makes building these charts easy with just eight simple steps. This comprehensive guide is ideal for analysts, accountants, finance professionals or students seeking to learn the fundamental process of assembling beautiful waterfall charts to track key financial metrics.
By following the steps outlined here, anyone can create powerful visualizations that enhance financial understanding, analysis and presentations.
What is a Waterfall Chart in Excel?
A waterfall chart is a special type of column chart used to show how an initial value transitions into a final value through a series of intermediate positive and negative changes. Each change is represented by a horizontal column stacked on top of each other, either going up or down depending on the positive or negative value.
The key benefit of waterfall charts is that they allow you to visualize the individual components of an overall net change rather than just the initial and final values. This helps with understanding where changes are occurring within the data.
The common waterfall chart examples applications include showing profit changes due to factors like revenues, costs, taxes, etc., presenting how cash balances change from opening to closing balances and analyzing budget variances by breaking down cost components.
Benefits of Using Waterfall Charts
Waterfall charts make it extremely easy to visualize financial data changes. Instead of just seeing the starting and ending values, you can see all the ups and downs in between. This makes waterfall charts very useful in several ways:
- You can showcase exactly how a final profit, loss, budget or cash flow number was arrived at by showing each component of the calculation visually through bars stacking on top of one another.
- It becomes simpler to analyze which factors are driving positive increases or negative decreases. One can quickly identify the key elements leading to a good or bad financial outcome.
- Presenting data changes in a waterfall format makes it easier for audiences to grasp insights rather than gazing at a column of numbers. Charts simplify complex financial stories.
- By displaying both gains and declines through colored bars, performance analysis becomes more intuitive. You can easily highlight which business units or processes are profitable and which ones are loss-making visually.
- As opposed to trying to analyze an entire financial model, waterfall charts allow viewers to comprehend the complete picture at a high level or deep dive into the detailed numbers as needed.
Steps to Creating a Waterfall Chart
Here are the simple 8-step process to make a waterfall chart in Excel.
Step 1: Organize Your Data
Like any good chart, a waterfall chart is only as good as the underlying data. So begin by organizing your financial data properly in Excel. The minimum data needed is:
1. Initial value
2. Intermediate values causing increases and decreases
3. Final value after all increases/decreases
Optional data fields like categories, labels and color-coding cells also help enhance waterfall charts by making them easier to read.
Step 2: Set Up Categories and Values
On your Excel worksheet, define at least 2 columns: One for categories and one for values.
- The categories column should have the labels for what’s causing each change
- The values column will contain the dollar amounts of changes
Make sure your initial, intermediate and final values are in chronological order vertically down this table. Insert new rows for additional data points.
Step 3: Insert a Stacked Column Chart
Highlight your 2-column data table, then navigate:
Insert > Recommended Charts > All Charts > Stacked Column Chart
For the first time, a default stacked column chart will appear, showing your data changes visually. Each coloured block represents an intermediate increase or decrease leading to the end value.
Step 4: Format the Data Series
Now click on any of the colored blocks within the stacked columns then:
1. In the Format Data Series pane, fill in meaningful Series Names like Revenue, Costs etc. This will help identify what each color block stands for.
2. For intermediate blocks, choose Paint or Border color fills to color code increases versus decreases. For example, use green fill for revenue gains and red border for cost rises.
3. For initial and final data blocks, choose neutral colors, such as grey or white fills, to visually distinguish them from intermediate changes.
4. Hide any legend that’s not relevant.
Step 5: Add the Final Touches
To polish up your waterfall chart:
1. Add Data Labels to showcase values for each change segment numerically. Toggle them centrally or outside the end of blocks using the Label Position button.
2. Remove the vertical gridlines and any unnecessary chart elements for a clean, simple look.
Your core waterfall chart is now ready! Now, let’s enhance readability.
Step 6: Label Data Points
To explicitly display starting, ending and sub-total values:
1. Click on the initial block then add a text box with “Starting Value” label
2. Do the same for the final block value: “Ending Value” label
3. Add data point labels equal to the preceding sub-total values at major increases and decreases intersections. This is done by adding text boxes with value formulas.
Step 7: Fine-tune the Chart’s Appearance
Further beautify your waterfall chart by:
1. Increasing font sizes and using boldface for text boxes
2. Applying white fills for text box backgrounds to contrast against color blocks
3. Removing excess white spaces between columns for a compressed look
4. Aligning label positions so the distance between brackets is consistent
At the end, you’ll have an Excel waterfall chart with clean columns showing the data story, clear data point labels, and legible text.
Step 8: Add a Trendline or Reference Line (Optional)
Two optional tips to highlight insights:
1. Reference Line: Add a straight reference line set against a historical benchmark or target value to identify performance gaps
2. Trendline: Insert a trendline to showcase the direction of change over time when a waterfall chart contains multiple time periods
And that’s it – following these 8 fundamental steps will enable you to create simple yet effective waterfall charts within Excel to track, analyze and present financial data.
Conclusion
As you can see, Excel provides an easy way to make beautiful waterfall charts through an intuitive step-by-step process. Whether you want to showcase profit drivers, cash flow levers or budget variance analyses, waterfall charts enable simple and visual representation of changes in financial metrics.
Now that you’re equipped with this helpful guide, open up your Excel dashboards and use waterfall charts to uncover financial insights! Doing so will enhance your analytics, allow intuitive presentations to senior executives and aid in making better data-driven decisions for your organization.