Excel 2020: Create Waterfall Charts
March 16, 2020 - by Bill Jelen
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.
The waterfall charts even work for cash flow charts that might go below zero.
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.