Custom Charts in Excel :: Waterfall Chart

Posted: June 21, 2013 by MaxFraudy in Excel
Tags: , ,

A Waterfall Chart is a great way to present data when looking at the incremental contribution of individual elements in reaching from one milestone to the other. Let’s take an example below. The left most column shows the total sales for last year, the right most shows the total sales for current year and the segments in between show the incremental contribution of each region in current year over last year. The reds show a negative contribution and the greens show a positive one.

Waterfall Chart

Now let’s learn how to make this chart. First of all, arrange the data like below, calculating change in each element value from last year. Then sort the data on the Change column.

Raw Data

Raw Data

Now add two more columns – absolute values of change in the first column, and ‘base’ in the second column using the formulas mentioned. At this point, we have all the data that we need to plot on the chart. All that is left is to bring it in the right format. This can be done by arranging the data as below and plot a Stacked Column Chart using this data that will look like the chart given below the data:

Calculated Data

Calculated Data

Raw Chart

Raw Chart

On this chart, do the following:

  • Reduce the Gap series to No Gap
  • Remove the legend
  • For Base series – Right click -> Format Data Series -> Fill -> No Fill
  • For Positive series – Right click -> Format Data Series -> Fill -> Green
  • For Negative series – Right click -> Format Data Series -> Fill -> Red
  • If you want data labels showing %contribution to the change, add another series with calculated values

After having done this all, your chart should look like this:

Waterfall Chart

As usual, there is a Waterfall Chart attached that contains the actual chart. Feel free to download šŸ™‚

Previously in Custom Charts:

Advertisements
Comments
  1. Allen F Clark says:

    Interesting and useful article. You can add more data series for creating charts with crossing axis and subtotals. Like a http://fincontrollex.com/?page=products&lang=en

  2. Allen F Clark says:

    Interesting and useful article. Waterfall chart is really great way of visualization. You can add more data series for creating charts with crossing axis and subtotals. Like a http://fincontrollex.com/?page=products&lang=en

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s