See the flow of your negative and positive data with a waterfall chart

 

by Kara Hiltz

Application:

Microsoft Excel 2016

Operating System:

Microsoft Windows

Download:

http://download.elijournals.com/excel/201602/waterfall.zip

Excel 2016 offers six new chart types, and the waterfall chart is one of them. We’ll show you when to use a waterfall chart and how to get the most from this visual aid for tracking inflows and outflows of data.

To create an effective waterfall chart, we’ll:

• Set up data that works best for the waterfall chart type.

• Create a waterfall chart.

• Designate the waterfall chart’s subtotals for accurate data depictions.

• Format the chart based on personal preferences.

Visual aids are crucial to effective data analysis, and that’s where Excel’s charts become valuable tools. With the release of Excel 2016, Microsoft introduced six new chart types. One of these new chart types, the waterfall chart, gives Excel users another option for charting data with constant positive and negative flows. Financial data, stock data, and any other data that fluctuates between inflows and outflows are prime candidates for a waterfall chart.

In this article, we’ll introduce you to the waterfall chart and show you how to make it work for you. In our example, a non-profit organization wants to demonstrate incoming donations versus outgoing expenses on a monthly basis. We’ll create a waterfall chart to show monthly income versus expenses, along with a running balance, as shown in Figure A.

A:

ime17b04A

Waterfall charts give you a visual comparison of how data increases and decreases.

Set up your data

You may need to change the way your data table looks before you insert a waterfall chart. The best way to structure your data is to include headings in the leftmost column and values in the next column to the right, as shown in Figure B.

Currency data should be formatted as currency in your data table. You will also need to ensure that your negative data is formatted as negative using a minus sign. (Negative data may also display in parentheses depending on your number format.)

Download:If you’d like to follow along with this article, simply download and extract the file MonthlyFinancials.xls from the URL given at the beginning of this article.

B:

ime17b04B

The rows containing only month names will show up as blank areas on our waterfall chart, but that creates a helpful visual border between monthly data.

Insert a waterfall chart

Once you’ve got your data set up, it’s time to insert the waterfall chart.

To generate a waterfall chart:

  1. Select any cell in your data range.
  2. Switch to the Insert tab.
  3. In the Charts area of the Insert tab, click on the Waterfall Chart icon, as shown in Figure C.

Excel inserts a default waterfall chart in your worksheet, but you’ll see right away that it needs some tweaking. The Running Balance values in our sample data are subtotals, but are shown as inflows in the chart. Next we’ll show you how to correct that issue.

C:

ime17b04C

The waterfall chart type has its own icon in the Charts area of the Insert tab.

Define the chart’s subtotals

Often when you’re working with data that fluctuates, you include markers that indicate subtotals. These subtotals allow you to compare the positive and negative data. In our example, we want to see where the non-profit organization’s balance lies after each month’s donations (income) and expenses.

By default, Excel’s waterfall chart treats all positive values as inflows, but we’ll need to change each data point that says Running Balance to a subtotal. You’ll see how correctly designating the subtotals makes a drastic differen[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage