Excel Charts - Waterfall Chart Tutorial

A waterfall chart shows a running total as values are added or subtracted. It's useful for understanding what additions and subtractions were made to a value to get it to its current value.

In the example below the Net Income increased by 14,937 from 2013 to 2014. A Waterfall Chart can be created to show what changes occurred to the values used in determining Net Income that created this increase.

Extrapolating the data, we want to highlight in our chart, will use the following data.

The values shown in red will be decreases. They increased in amount from 2013 to 2014 but they decrease the Net Income.

Creating the WaterFall Chart

1. Enter the data in a worksheet.

   

2. Select the cell range A1:B9
3. Click the Insert tab on the Ribbon. In the Chart group click on Recommended Charts. Select the Waterfall Chart. If it isn’t in the Recommended Charts, select the Waterfall Chart button.

   

   The WaterFall chart shows positive values in Blue and Negative Values in Orange. The legend shows that Totals are in gray. We don’t currently have any totals but we will modify our chart to show them.

   Looking at the data we can see that the Net Income after Tax is 14,937. However, looking at the chart’s vertical axis we can see that Net Income After Taxes is at the 90,000 line. The reason this has happened is because some of our values are actually totals. For example, Net Sales is a total from Sales – Sales Returns. The chart shows Sales at 30,000 then our income decreases by 1090, but then we add an additional 28,910 for Net Sales.

   

   Modify the Waterfall Chart to show Totals and Chart Title

   
   
4. Click on the Net Sales bar (called a Data Point). (All of the Data Points are selected). Click on the Net Sales Data Point again. (The other Data Points are dimmed). Right-click on the Net Sales Data Point then select Format Data Point.

   

   The Format Data Point pane is displayed on the right side of the spreadsheet.

   

   You can see that Net Sales has a value of 28,910 and it now matches with the vertical axis.
   
   Click on the Gross Profit Data Point. Select Set as Total.
   Click on the Net Income Before Tax Data Point. Select Set as Total.
   Click on the Net Income After Tax. Select Set as Total.
   Click anywhere outside of the chart to deselect it.
   

   

   The Net Income After Tax of 14,937 matches with the vertical axis and the Income Statement.

   We can now easily see what affected the changes between the 2013 and 2014 Income Statements.

   Sales	increase to Net Income After Taxes by 30,000
   Sales Returns	decrease to Net Income After Taxes by 1,090
   Cost of Goods Sold	decrease to Net Income After Taxes by 12,775
   Operating Expenses	decrease to Net Income After Taxes by 875
   Income Tax	decrease to Net Income After Taxes by 323

   Click on the Chart Title. Change the Chart Title to Changes to Net Income from 2013 to 2014