If you want to create a visual that shows how positives and negatives affect totals, you can use a waterfall chart, also called a bridge or waterfall chart. You can easily create and customize a waterfall chart in Microsoft Excel.
When to use a waterfall chart
You may be wondering if the data you have in your spreadsheet is appropriate for a waterfall chart. If you have an initial value with a positive and negative series affecting the final result, then a waterfall chart is for you.
Here are just a few common uses:
- Current accounts : use a starting balance, add credits, subtract debts and view the ending balance.
- Inventory : Enter a starting amount, add the shipments received, subtract the units sold and show the final amount.
- Products : View a starting total, subtract damaged units, add refurbished units, and show salable total.
- Come in – Use a starting amount, add income, subtract expenses, and show the remaining total.
Create a waterfall chart in Excel
If you have data that would fit perfectly into a waterfall chart for a useful visual, let’s get going! For this tutorial, we will use a checking account as an example.
Start by selecting your details. Below you can see that our data starts with an opening balance, includes incoming and outgoing funds, and ends with an ending balance. You should organize your data in a similar way.
Go to the Insert tab and the Charts section of the ribbon. Click the Cascade drop-down arrow and select “Cascade” as the graph type.
The waterfall chart will appear in your spreadsheet.
Now, you may notice that the starting and ending totals do not match the numbers on the vertical axis and are not colored as Total according to the legend. Don’t worry – this is a simple fix!
Excel recognizes the starting and ending amounts as parts of the series (positive and negative) rather than as totals.
To fix this, double-click the chart to bring up the Format sidebar. Select the bar for the total by double clicking on it. Click the Series Options tab in the sidebar and expand Series Options if needed.
Check the “Set as total” box. So, do the same for the other total.
You will now see that those bars correspond to the vertical axis and are colored as Total according to the legend.
Customize a waterfall chart
Like other types of charts in Excel, you can customize the design, colors, and appearance of your chart. If this isn’t something you’ve already done in Excel, here are the basics for customizing your chart.
If you want to start by editing the title, click the Graphic Title text box.
Double-click the chart to open the Format Chart Area sidebar. Then, use the Fill and Line, Effects and Size and Properties tabs to do things like add a border, apply a shadow, or resize the chart.
Select the chart and use the buttons on the right (Excel on Windows) to adjust the chart elements such as labels and the legend, or chart styles to choose a theme or color scheme.
Select the chart and go to the Chart Design tab. Then, use the tools in the ribbon to select a different layout, change colors, choose a new style, or adjust your data selection.
You can also move the chart to a new spot on the sheet by simply dragging it. And, to resize the chart, drag in or out from a corner or edge.