MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Create Waterfall Charts


March 16, 2020 - by Bill Jelen

Create Waterfall Charts. Photo Credit: Jonatan Pie at Unsplash.com

For 12 years, I worked at a company doing data analysis. One of my regular tasks was to analyze the profit on sales proposals before they went out the door. I did this with a waterfall chart. For me, the waterfall chart never would have to dip below the zero axis. I used a few tricks to make the columns float and drew the connector lines in by hand, using a ruler and a black pen.

Excel 2016 introduced a built-in Waterfall chart type. Select your range of data and create the chart. In the chart below, three columns are marked as total: Net Price, Gross Profit, and Net Profit. Excel won't automatically know which columns should be totals. Click any column to select all columns in the chart. Then single-click one total column. Right-click and select as Total. Repeat for the other columns that should touch the X-axis.


A pricing waterfall chart starts out with a tall List Price column on the left, then a floating column for Discount gets you to Net Price. Then floating columns for expenses like Material, Labor, and Overhead. Finally, on the right, you get to net profit. This type of chart illustrates how a change in discount leads to smaller profit.

The waterfall charts even work for cash flow charts that might go below zero.

Another version of a waterfall chart is to show Cash Flow. The Cash Flow balance for December 31 is on the left. Each month, there is a forecast of positive cash flow or negative cash flow. In this particular chart, some months have the cash balance go negative (buying lots of material for a project that won't be invoiced for 90 days). While there were tricks in the past to create waterfall charts, this particular example where one or more months go negative were particularly tricky in the past and are easy now.

Tip

To change the color for Increase/Decrease or Total: Click on the legend and then click on one item in the legend. Press Ctrl+1 to open the Format panel for that series and choose a new Fill Color.

Title Photo: Jonatan Pie at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.