Excel Waterfall charts are now available to Office 365 users via the Insert > Charts menu. It makes light work of building what was a laborious chart to create in earlier versions of Excel. However, it has some limitations, so you may prefer the manual approach required by users of earlier Excel versions anyway. I’ll cover both methods in this tutorial.
Waterfall charts help us visually understand the cumulative effect of positive and negative values over time or categories. A common use is in financial analysis to track how profit or cash flows are arrived at.
The Office 365 Excel Waterfall Chart above uses a new charting engine and as such it’s not as customizable as regular Excel charts you might be used to. For the most part it isn’t a problem, but I really dislike the category labels rotated vertically and the only way to make them horizontal is to make the chart ridiculously wide.
Another annoyance is you can’t adjust the plot area size, only the overall chart area can be adjusted. There are some other issues, but I don’t want to nit pick 😉
Enter your email address below to download the sample workbook.
Insert Excel Waterfall Charts
For Office 365 users creating a waterfall chart is easy, you just need some data.
Tip: Avoid empty rows as these will appear as gaps in the chart. Also, negative values like expenses must be entered as negatives for Excel to plot them correctly.
Select the data in cells A5:B19 > Insert tab > Waterfall chart:
Set the subtotals and totals; one by one right-click on the columns in the chart > ‘Set as total’:
You should see that the positive, negative and total columns are automatically formatted with different colours.
Remove the gridlines and vertical axis as we don’t need them if we have data labels. Just left-click to select them and press the Delete key. I’d even go as far as to remove the legend and title in income charts as the horizontal axis and labels are self-explanatory.
Excel Waterfall Charts in Earlier Versions
Now, if you don’t have Office 365 don’t despair as you can build Excel Waterfall Charts with a few clever tricks.
You can even build a waterfall bar chart and align it to your P&L like so:
We’ll look at a waterfall column chart first.
Manual Waterfall Column Chart Data
You’ll need 3 extra columns (C:E as shown below) to support the chart:
Column C contains the totals. This series will be a column chart.
Columns D and E support Up/Down bars series. Column D contains a running total of the balance before the current row, and column E contains a running total of the balance as at the current row. These values are the bottom and top ends of the up/down bars.
Creating Manual Excel Waterfall Charts
Step 1: Select data in cells A5:A19 then hold CTRL and select cells C5:E19
Step 2: Insert the chart; Insert tab > Line chart. It should look like this:
Step 3: Change Chart type for subtotal and totals; select ‘Series 1’ markers in the chart > right-click > change series chart type > select ‘Clustered Column’ from the list (Excel 2013 onward dialog box shown below):
Step 4: Add up/down bars; select either of the Series2 or Series3 lines in the chart > click the + widget > check the box for ‘Up/Down Bars’:
Note: Excel 2007 and 2010 users go to Chart Tools: Layout tab, click the Up-Down Bars button, and select Up-Down Bars from the menu.
Step 5: Hide both lines and markers; select the line > CTRL+1 to open the format pane > Set the Line to ‘No line’ and the Marker to ‘None’.
Step 6: Set the colour fill for the up/down bars; left click once to select them, then right-click to open the mini toolbar and select a new Fill and Outline colours, or use the Fill Colour palette on the Chart Tools tab:
Repeat colour formatting for the totals and positive values as required.
Step 7: Add data labels; from the Chart Tools: Format tab select ‘Series1’ from the ‘Current Selection’ drop down as shown below:
Excel 2013 onward; click the + widget button to the right of the chart > Data Labels.
Excel 2007 and 2010; Chart Tools: Layout tab > Data Labels.
This will add labels to the subtotal and total columns.
Step 8: For the Up/Down bar labels you need to take a slightly different approach which is only available in Excel 2013 onward; select Series2 from the Chart Tools: Format tab drop down. Then click the + widget to the right of the chart > Data labels > More Options… > Label Options: Value from Cells > select the values in column B:
Note:For Excel 2007 or 2010 users there is no easy way to add labels. Adding labels to the chart will result in a mess which you have to tidy up. To tidy them up select each label box with 2 single left-clicks, then click in the formula bar and type = then click on the cell containing the label value in the chart source data table and press ENTER. Repeat for remaining labels.
Step 9: Tidy up; remove gridlines, vertical axis, title and legend. You should end up with something like this:
Thanks to fellow Excel MVP, Jon Peltier, for sharing this technique. If you want an easy way to build waterfall charts then check out Jon’s Chart Utility that builds waterfall charts and more with the click of a button. It’s available for PC and Mac.
Waterfall Bar Charts
As I mentioned earlier, I don’t like vertical labels on the horizontal axis. They’re difficult to read and that defeats the purpose of a chart in the first place.
One option is to use the Excel Camera Tool to take an image of the chart and rotate it on its side. However, the Camera image isn’t always crisp, plus you can’t rotate the value labels. Swings and roundabouts. ☹
The waterfall bar chart (shown below) solves these problems. It requires a slightly different calculation for the 3 series because the ‘Balance’ series is the stepping stone for the visible portion of the bars and is actually hidden in the chart.
In the image below, I’ve shown the ‘Balance’ series portion of the bars in pale grey, but later we’ll set the fill colour to ‘None’ so the ‘Change’ portion of the bars appear to float.
Creating Manual Waterfall Bar Charts
Step 1: Select data cells A5:A19 > hold CTRL and select cells C5:E19
Step 2: Insert the chart; Insert tab > Stacked Bar Chart
Step 3: Fix category order; double click the axis labels > in the Format Axis Options > check ‘Categories in reverse order’
Step 4: Hide the balance series; select series 1 in the chart i.e. the ‘Balance’ series > set the file and border to none:
Step 5: Set bar fill colour; one at a time select the positive value columns, in my case ‘Sales’ and ‘Consulting’, and set the fill colour to something different. I’ve chosen blue.
Step 6: Formatting [Optional]; remove the title, legend, gridlines and vertical axis and delete them.
Step 7: Position the chart; align the chart to your P&L and cover columns D:E with the chart like so:
Notice I added an empty row for the 'Expenses' header on row 12. Another benefit of this chart is that you can get away with empty rows in your source data because you're using the labels in column A for the chart axis labels.
The chart will treat them correctly because the underlying values are still negative, but they’ll appear like a normal P&L that doesn’t display negative values with a minus sign:
If you liked this please click the buttons below to share.