Creating Waterfall Charts - Download Available
Thursday, December 22, 2016
Posted by: Amy Spencer
This article, written by Craig Houmand, CPA, is featured in the January 2017 issue of The Journal Entry.
Download the Waterfall Template
It’s been said that a picture is worth a thousand words. So, how do you translate a collection of numbers into a concise eye-appealing graphic? Use a waterfall chart.
Simply, a waterfall chart is a data visualization of the cumulative effect of positive and negative values. Usually, the initial and final values are represented by whole columns, while the intermediary values are floating columns. The columns are color coded so you can distinguish positive from negative numbers (e.g., green represents an increase; red represents a decrease).
Waterfall charts are insightful for a variety of scenarios, from visualizing profit and loss statements to highlighting budget variances on a project.
Creating a waterfall chart in Office 2016 is fairly easy to do. First, select your data table. Next, click Insert>Insert Waterfall or Stock Chart>Waterfall.
However, creating a waterfall chart in a version earlier than Office 2016 is somewhat challenging. Basically, it’s about using a two-dimensional column chart and making the base series invisible. However, if your data ever crosses over into negative territory, this floating column chart approach fails. To overcome this problem, you need to use up-down bars while hiding the line chart series.
Of course, the easiest way to make a waterfall chart in Excel is to use a pre-made template. A template is helpful if you don’t have a lot of experience building waterfall charts.
I’ve created an Excel waterfall template workbook that is both easy to use and handles values crossing the x-axis. You can download this template here.
Once you’ve downloaded this file, open a sample worksheet. Enter your data into the input table and see the corresponding waterfall chart update automatically. Depending on your reporting need, you can hide a row (or rows) in the graphic by clicking on corresponding drop-down buttons.
After updating a waterfall chart to meet your needs, save your file. Next, copy and paste your chart into a PowerPoint presentation, dashboard, or report as an image.
The downloadable workbook includes the three examples highlighted in this article. You can easily modify one of these spreadsheets for your particular application.
If you have Office 2016, the workbook outlined here works well with this software suite. After experimentation, you may find this template meets your reporting requirements well enough that you do not need to construct a waterfall from scratch.
1. See https://support.office.com/en-us/article/Create-a-waterfall-chart-in-Office-2016-8de1ece4-ff21-4d37-acd7-546f5527f185
2. See http://peltiertech.com/excel-waterfall-charts-bridge-charts/
About the Author
Craig Houmand, CPA works as a corporate planning adviser at Dominion Questar in Salt Lake City, UT and is a member of the UACPA's Business Valuation Conference committee. He can be reached at firstname.lastname@example.org