How To Build Waterfall (Bridge) Charts In Excel
Waterfall/Bridge charts are commonly used in the finance world to show up and down variances between two periods of time. Typically, these comparisons walk you through the ups and downs of “Prior Year vs. Current Year” or “Forecast vs. Budget”. If you are unfamiliar with these sorts of charts, you can read my article explaining what exactly a Waterfall Chart is used for.
In Excel, there are two ways to build a Waterfall Chart. You can either:
- Use the built-in Waterfall Chart type
- Build your own using a stacked bar chart
In this article, we will be exploring Excel’s built-in bridge chart object called the Waterfall Chart.
Which Waterfall Method To Choose?
Benefits To Using Excel’s Native Waterfall Chart
- Quicker to build
- Easy to change bars to totals/endpoints
- Toggle on/off connector lines
Disadvantages To Using Excel’s Native Waterfall Chart
- Available only in Excel 2016 or later
- Limitations to interacting with VBA code (many simple formatting changes are unavailable to access)
- Cannot create a split-level waterfall or add additional chart series
- Cannot format connect lines
- Positive/Negative Series Fill Color tied to Theme Color pallete
- No Formula in Chart Title or Data Labels
- Negative Data Labels can’t be moved to the top of the bar
- X-Axis stays at the bottom even when bars cross below zero
- Limited axis and series formatting options
Using The Waterfall Chart Object In Excel
Waterfall Chart End Result
Here’s what we’ll be building in this section of the tutorial. By the end of this section, you’ll have an awesome-looking bridge chart using Excel’s built-in Waterfall Chart type.
Setup Your Bridge Data
Before you create your Bridget Chart, you will want to have your data set up in your Excel spreadsheet.
The key points you will need to display will be:
- Start/Endpoints
- Variances
- Any Subtotals you would like to callout
In the example we will be using, a headcount forecast will get charted.
You can see in the Headcount Forecast data set image that there are beginning and ending headcount figures displayed along with all the headcount variance events that have occurred throughout the period.
There has also been a subtotal called “Current Headcount” that has been placed in the middle of the bridge to divide actual headcount actions and projected headcount movements.
How To Insert The Waterfall Chart Type
Once you have your data walk-forward set up in your spreadsheet, simply highlight both the labels and numerical values (should be a 2-column range).
Then go to the Insert tab in Excel’s Ribbon and find the chart button that looks like a waterfall chart. Within that button’s menu, you should easily find the Waterfall Chart button that you can then click to insert your chart object.
After you have clicked the Insert Waterfall Chart button, you should immediately see the chart appear on your spreadsheet. From here, we will need to carryout a few manipulations to get the chart formatted properly.
How To Set A Total
Excel will not attempt to guess what your endpoints/total bars are and will instead treat all numbers as a variance. This means you will need to go through and manually set certain bars as totals.
You can convert a variance bar to a total or subtotal bar by selecting the individual bar and right-clicking it. In the Right-Click menu, select Set as Total and your bar will be converted.
You should also notice that as you convert your variance bars to totals that the bar color will change.
In our example, we have two total bars and a subtotal bar that we will need to set as a total. The final result should look like below:
Wrap X-Axis Text
For some reason, the Waterfall Chart type does not wrap text automatically. Instead, it attempts to make two rows of alternating text and cuts off longer text. This looks very unprofessional.
To work around this, go back to your source data table and manually add line breaks to your text using the keyboard shortcut Alt+Enter.
If you are using a formula to create your labels, you can use the function CHAR(10) to add a line break (example: = “Beginning” & CHAR(10) & “Headcount”)
Change Bar Colors
The chart coloring of the Waterfall Charts in Excel is one of the most frustrating parts of the entire creation process. Unlike most Excel charts where you can select a series and change the fill color for all the bars, you have two options for coloring:
- Use Excel’s preset colors based on your color palette, or
- Manually select each bar individually and change the bar color
If you wish to customize your own preset for your Waterfall Chart colors, you will need to create a Theme Color Palette.
You can create a custom theme palette by going to Page Layout >> Colors >> Customize Colors…
While creating the color palette, you really only need to modify 3 accent colors which will feed into your Waterfall Chart.
- Accent 1 – Provides the color for your increase bars
- Accent 2 – Provides the color for your decrease bars
- Accent 3 – Provides the color for your Total bars
I personally like to have a green color for my good variances, a red color for my bad variances, and a blue color for my total bars. Here are the color codes I’m using in this tutorial:
- Red – RGB(224, 76, 65) | HEX: E04C41
- Green – RGB(61, 177, 130) | HEX: 3DB182
- Navy Blue – RGB(46, 62, 81) | HEX: 2E3E51
After you have selected the colors you want, you can give your theme a reference name and click the Save button.
You can then implement your theme by selecting the name within the Colors menu button.
Increase Variance Bar Size (Zoom In)
A lot of times with Waterfall charts the variance bars are very small compared to the total bars. While this is proportionally correct, from an appearance perspective, you may want to focus in on the variances themselves, not their proportion to the total.
To do this, you will want to adjust the Y-Axis minimum value. You can make this adjustment by
- Right-click the Y-Axis labels/values
- Select Format Axis in the Right-Click Menu
- In the Format Axis Pane, navigate to Axis Options
- Increase the Bounds Minimum until your desired appearance is achieved
Removing The Y-Axis & Legend
In my opinion, the Y-Axis and Chart Legend are excess noise that takes attention away from the chart. I recommend deleting both in most cases.
If you are worried about your audience’s reaction to disproportioned bar sizes (starting at 900 instead on 0), you can insert a white box and cover up the numbers in between the two endpoint labels of the axis.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
I Hope This Helped!
Hopefully, I was able to explain how you can build a Waterfall (Bridget) chart in your Microsoft Excel spreadsheets. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.