Create and customize a waterfall chart in Microsoft Excel

To create a visual that shows how positives and negatives affect the totals, you can use a waterfall chart, also known as a bridge or cascade chart. You can easily create and customize a waterfall chart in Microsoft Excel.

When to use a waterfall map?

You may be wondering if the data in your spreadsheet is suitable for a waterfall chart. If you have a starting value with a positive and negative range that influences the final result, then a waterfall chart is for you.

Here are just a few common uses:

Checking Bills: Use an opening balance, add credits, subtract debits and show the closing balance. Inventory: Enter a starting amount, add shipments received, subtract the units sold, and display the final amount. Products: Display a starting total, subtract damaged units, add refurbished units, and display the salable total. Revenue: Use a starting amount, add the income, subtract the expenses, and show the total amount.

RELATED: How to Automatically Generate Charts in Google Sheets

Create a waterfall chart in Excel

If you have data that fits perfectly into a waterfall chart for a useful picture, let’s get started right away! For this tutorial, we’ll use a checking account as an example.

Start by selecting your data. You can see below that our data starts with an opening balance, includes incoming and outgoing funds, and ends with a closing balance. You should organize your data in the same way.

Go to the Insert tab and Charts section of the Ribbon. Click the Waterfall drop-down arrow and choose Waterfall as the chart type.

The waterfall chart appears in your spreadsheet.

Now you may notice that the opening and grand totals do not match the numbers on the vertical axis and are not colored as Legend Total. Don’t worry, this is a simple solution!

Excel recognizes those starting and ending amounts as parts of the series (positive and negative) rather than totals.

To fix this, double-click the chart to display the Format sidebar. Select the total bar by clicking it twice. Click the Series Options tab in the sidebar and expand Series Options if necessary.

Check the box for ‘Set as total’. Then do the same for the other total.

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

Customize a waterfall map

Like other chart types in Excel, you can customize the design, colors, and appearance of your chart. If you haven’t already done this in Excel, here are the basics for customizing your chart.

To start changing the title, click the Chart Title text box.

Double-click the chart to open the Chart Area Format sidebar. Then use the Fill & Stroke, Effects, and Size & Properties tabs to do things like add a border, apply a shadow, or scale the chart.

Select the chart and use the buttons on the right (Excel on Windows) to customize 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 the colors, choose a new style, or adjust your data selection.

You can also move your chart to a new place on your sheet by simply dragging it. And to resize your chart, drag in or out from a corner or edge.

For help with other chart types, see how to create a bar chart in Excel or a combo chart.

Leave a Reply

Your email address will not be published.