Microsoft Excel: how to create and customize a waterfall chart

Microsoft Excel: how to create and customize a waterfall chart

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.

On the Insert tab, click Waterfall and select Waterfall

The waterfall chart will appear in your spreadsheet.

Example of a waterfall chart inserted

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!

Example of a waterfall chart without totals

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.

Open the Format sidebar

Check the “Set as total” box. So, do the same for the other total.

Enable the Set as total option

You will now see that those bars correspond to the vertical axis and are colored as Total according to the legend.

Example of a waterfall chart with corrected totals

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.

Click on the chart title to edit it

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.

Open the Format Chart Area sidebar

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.

Adjust the elements of the chart

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.

Click the Graphic Design tab

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.

Drag a corner or edge to resize the chart

For help with other types of charts, take a look at how create a bar chart in Excel or create a combination chart .

Similar Posts

Leave a Reply

Your email address will not be published.