How to Create and Customize a Waterfall Chart in Microsoft Excel


Waterfall chart in Excel

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 cascade chart. You can easily create and customize a waterfall chart in Microsoft Excel.

When to Use a Waterfall Chart

You might be wondering whether the data that you have in your spreadsheet is appropriate for a waterfall chart. If you have a starting value with a positive and negative series that affects the final outcome, then a waterfall chart is for you.

Here are just a few common uses:

  • Checking accounts: Use a starting balance, add credits, subtract debits, and show the ending balance.
  • Inventory: Enter a starting amount, add received shipments, subtract units sold, and show the ending amount.
  • Products: Display a starting total, subtract damaged units, add refurbished units, and show the sellable total.
  • Revenue: Use a starting amount, add income, subtract expenses, and show the total remaining.

RELATED: How to Automatically Generate Charts in Google Sheets

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 right to it! For this tutorial, we’ll use a checking account as an example.

Start by selecting your data. You can see below that our data begins with a starting balance, includes incoming and outgoing funds, and wraps up with an ending balance. You should arrange your data similarly.

Go to the Insert tab and the Charts section of the ribbon. Click the Waterfall drop-down arrow and pick “Waterfall” as the chart type.

On the Insert tab, click Waterfall and select Waterfall

The waterfall chart will pop into your spreadsheet.

Example of a waterfall chart inserted

Now, you might notice that the starting and ending totals don’t match with the numbers on the vertical axis and aren’t colored as Total per the legend. Not to worry—this is a simple fix!

Example of a waterfall chart with no totals

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

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

Open the Format sidebar

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

Enable the Set as Total option

Now, you’ll see that those bars match up with the vertical axis and are colored as Total per the legend.

Example of a waterfall chart with proper totals

Customize a Waterfall Chart

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

If you’d like to start by changing the title, click the Chart Title text box.

Click the Chart Title to change it

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

Open the Format Chart Area sidebar

Select the chart and use the buttons on the right (Excel on Windows) to adjust Chart Elements like labels and the legend, or Chart Styles to pick a theme or color scheme.

Adjust the Chart Elements

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, pick a new style, or adjust your data selection.

Click the Chart Design tab

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

Drag a corner or edge to resize the chart

For help with other chart types, take a look at how to make a bar chart in Excel or create a combo chart.





Source link

Recent Articles

spot_img

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox