Pinterest Pixel

Create an Excel 2016 Waterfall Chart

Waterfall Charts are one of the many new Charts available only in Excel 2016. When I was... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Waterfall Charts are one of the many new Charts available only in Excel 2016.

When I was working at General Electric, I had to create Excel 2016 Waterfall Charts on a monthly basis using formulas….ouch!

They are very popular in the current corporate environment as they graphically show the positive and negative movements within your Monthly Net Profit or Cash Flow.

In this article, we will discuss the following concepts regarding an Excel 2016 Waterfall Chart:

Let’s look into each of these points one-by-one!

 

What is a Waterfall Chart?

Waterfall Chart is an advanced type of Column Chart.

They are very useful in that they show how the initial value is affected by a series of positive and negative expenses through a series of changes.

The first and last column represents total starting and ending values whereas the intermediate columns represents a series of changes from one period to another.

This is how an Excel 2016 Waterfall Chart looks like:

Create an Excel 2016 Waterfall Chart

As you can see the columns are color-coded to distinguish between total, positive and negative values.

Here, Green Bars is for the total column, Blue Bars are for positive values and Red Bars are for negative values.

See also  Area Chart: Highlight Chart Sections

They are also called Excel Bridge Chart and they show how the intermediate columns connect the start and end columns.

When to Use Waterfall Chart?

The main purpose of creating Excel 2016 Waterfall Chart is to show the change in value from the starting point to the final result.

They are mainly used in business applications but can be used to illustrate any process with additions and subtractions based on a starting value.

It can be used to evaluate the following :

  • Change in company’s profit
  • Change in inventory levels
  • Change in product’s earnings
  • Budget change in a project
  • Tracking demographic changes
  • Change in cash flow

Let’s take a look at how to create a Waterfall chart in Excel 2016.

 

How to Create an Excel 2016 Waterfall Chart?

In this example, I show you how easy it is to insert a Waterfall Chart using Excel 2016.

NB: If you do not have Excel 2016 installed, there are a couple of great Excel add-ins that allow you to insert these charts in Excel 2013 and prior:

The Waterfall Chart Creator by TheSpreadsheetGuru

Peltier Tech Charts for Excel by Jon Peltier

See also  Excel Chart Line Styles

Want to know how to create a Waterfall Chart?

 

Example 1:

Watch it on YouTube and give it a thumbs up!

Create an Excel 2016 Waterfall Chart | MyExcelOnline

Follow the step-by-step tutorial on how to create a Stacked Waterfall Chart Excel 2016 and make sure to download the Excel Workbook and follow along:

download excel workbookWATERFALL-CHART.xlsx

 

STEP 1: Enter the values in your workbook with the Starting Total e.g. January Income.

Then add the positive and negative values, like direct & indirect expenses.

Finally enter the Ending Total e.g. February Income, which will Sum all of the above values.

Create an Excel 2016 Waterfall Chart

 

STEP 2:Highlight all the data and go to Insert > Recommended Charts

Create an Excel 2016 Waterfall Chart

Create an Excel 2016 Waterfall Chart

 

STEP 3: Select All Charts > Waterfall > OK

Create an Excel 2016 Waterfall Chart

 

STEP 4: Double Click on the Starting Totals column (e.g. January Income) and this will bring up the Format Data Point dialogue box.

“Check” the Set as Total box

Create an Excel 2016 Waterfall Chart

NB: This will set this column’s value so it starts on the horizontal axis at zero and will not “float”

 

STEP 5: Now select the Ending Total with your mouse and once again, “Check” the Set as Total box

Create an Excel 2016 Waterfall Chart

 

Now you have your beautiful looking Excel 2016 Waterfall chart and you can quickly point out to your management where the variances have occurred…

See also  How to add Trendline in Excel Charts

Create an Excel 2016 Waterfall Chart

Example 2:

We will use the Waterfall Chart to understand the income statement overview and change analysis.

Create an Excel 2016 Waterfall Chart

STEP 1: Select the Data Table

Create an Excel 2016 Waterfall Chart

STEP 2: Go to Insert > Waterfall Icon > Waterfall Chart.

Create an Excel 2016 Waterfall Chart

STEP 3: Waterfall Chart will appear on the sheet.

Create an Excel 2016 Waterfall Chart

Net Sales, Gross Margin, Operating Income, Income before taxes, and Net Income are all either Total or Subtotal values, not additional values.

As you can see Excel has incorrectly considered all the columns as additional values (color-coded as either blue or red). Let’s fix it!

STEP 4: Right Click on Column and select Set as Total.

Create an Excel 2016 Waterfall Chart

Net Sales is now shown as Total Value (Green Color).

STEP 5: Let’s do the same for Gross Margin, Operating Income, Income before taxes, and Net Income.

Create an Excel 2016 Waterfall Chart

STEP 5: Double Click on Chart title and rename it.

Create an Excel 2016 Waterfall Chart

STEP 6: To change the layout of the Waterfall Chart, Go to Chart Design > Select a Chart Style.

Create an Excel 2016 Waterfall Chart

Your edited Waterfall Chart is ready!

Create an Excel 2016 Waterfall Chart

 

Conclusion

In this article, you have learned that the Waterfall in Excel 2016 is an excellent way to display how individual data contributes to an overall total.

See also  Stock Line Chart Using Excel

It is used to display how a net value has been arrived by analyzing the cumulative effect of positive and negative values based on a starting point.

You can go through the details regarding the new charts available in Excel 2016 by clicking here.

 

Create an Excel 2016 Waterfall Chart

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!