How to Create Waterfall Charts in Excel to Show Running Balance Over Time

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

A Waterfall chart (sometimes called a Bridge chart) shows a running total over a period of time as values are added or subtracted. Often this is used to show the net balance over a series of months or years as income and expenses are accounted for.

What does a Waterfall Chart look like?

Each type of column (revenue, expense and balance) on the waterfall chart are colour coded. Normally, the balance is rooted to the horizontal axis while the income and expense figures float.

1. How to set up the data for a waterfall chart

Consider the following balance sheet data:

How to set up the data for a waterfall chart
  • The balance figures for the cumulative balances are shown in GREEN.
  • Revenue figures are shown in BLACK.
  • Expense figures are shown in RED.

In between each balance figure there can be one or more positive and/or negative values which represent the activity between each balance point.

2. How to create a Waterfall Chart

1.  Select a single cell in your data to select the entire range or select the specific data range you wish to use.

2.  Click the Insert tab |

3.  Click the Waterfall, Funnel, Stock, Surface, or Radar Chart icon in the Charts group, then choose Funnel.

Alternatively, click Recommended Charts | All Charts tab | Waterfall.

Where to find the Waterfall chart option amongst the Chart Types

Your initial chart will look similar to this:

Waterfall Chart

4.  Right-click the last balance column and choose Set as Total. This resets it back to zero (the baseline).

5.  Working right-to-left, repeat the previous step for every BALANCE column on the chart.

6.  To remove the intermediate labels between each balance, while retaining the columns, click twice (slowly) on each data label then click Delete.

Your chart will now look similar to this.

Waterfall Chart

The tall grey bars that are grounded on the axis are the cumulative totals. The blue and orange bars in between are the positive and negative events that happened in between.

3. How to change the colours of a waterfall chart

1.  On the Page Layout tab, click the Colors button in the Themes group..

2.  Click Customize Colors at the bottom.

Waterfall Chart

3.  Accent 1, Accent 2 and Accent 3 are the most important as these correspond to the three main colours on the Waterfall chart. Set your preferred accent colours.

4.  Type a name for your custom theme.

4.  Click OK.

4. Other embellishments for waterfall charts 

It is always worth checking out the Chart Styles and the Quick Layouts on the Chart Design ribbon for different configurations and better arrangements of the data..

Waterfall Chart
Waterfall Chart

5. What next?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

POPULAR EXCEL POSTS

Radar charts in Excel


Sunburst charts in Excel


Box plots (Box & Whisker charts) in Excel


Waterfall charts in Excel


Funnel charts in Excel


VLOOKUP de-mystified


How to fix broken formulas fast


3 Ways To Insert a Degree Symbol In Excel


11 little-known sorting secrets (wait until you see 5, 6 and 9)

«« SEE ALL EXCEL POSTS »»

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)
Read More
How to master the VLOOKUP formula in Excel
Read More
How many rows and columns does Excel have?
Read More
Creating New Custom Excel Templates

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>