×

How To Build Waterfall (Bridge) Charts In Excel

By Chris Newman •  Updated: 10/28/22 •  7 min read
How to create Waterfall 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:

  1. Use the built-in Waterfall Chart type
  2. 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.

An example of a Waterfall chart in Excel.

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.

Waterfall chart example result in Excel.

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
Bridge data for waterfall chart in Excel.

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.

Inserting a Bridge chart in Excel.

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.

Converting a variance bar chart to a total or subtotal in Excel.

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:

Example waterfall chart in Excel with totals.

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:

  1. Use Excel’s preset colors based on your color palette, or
  2. Manually select each bar individually and change the bar color
Changing colors on a waterfall bar chart in Excel.

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…

Editing theme colors in Excel charts.

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.

Changing colors in Excel with themes.

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.

How to increase waterfall bar chart size in Excel.

To do this, you will want to adjust the Y-Axis minimum value. You can make this adjustment by

  1. Right-click the Y-Axis labels/values
  2. Select Format Axis in the Right-Click Menu
  3. In the Format Axis Pane, navigate to Axis Options
  4. Increase the Bounds Minimum until your desired appearance is achieved
How to modify Y-axis minimum value in Excel.

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.

How to remove Y-axis and legend with bridge chart in Excel.

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.

How to hide Axis values in bar chart with Excel.

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.

Keep Learning

Chris Newman

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.