“Waterfall” Chart in Microsoft Excel 2010

In this article we will learn how we can make waterfall chart in Microsoft Excel 2010.

This chart is also known as the flying bricks chart or as the bridge chart.  It’s used for understanding how an initial value is affected by a series of intermediate positive or negative values.

You can use this chart to show the revenue and expenditure of a business or even personal accounts.

Let’s take an example and understand how and where we can use this chart and how it will perform if we present the information in Water Fall Chart.

We have a data set where we have the revenue, cost of sales, etc.  Now let’s insert another column before column B and enter “Remaining Profit” as the title.

img1

This is calculated as follows

  • First we minus cost of sales from revenue earned.
  • So 2 million minus 4 hundred thousand gives us 1.6 million.
  • We get the same figure as the gross profit.
  • Then from this figure, we keep deducting each cost – management costs, cost of sales, operating costs, etc.
  • So we minus 240000, 180000, 300000 and 400000 from 1600000 to get our net profit of 480000.

img2

Now we just need to plot this and we will come up with an interesting looking waterfall chart.

  • We need to start with a stacked column chart and then alter it to suit our needs.
  • So we select our data, then go to Insert tab and select stacked column chart.

img3

  • Remove the legend as we do not require it.

img4

  • Now click on one of the blue columns which will result in all the blue ones being selected.
  • Right click and select Format Data series.  Click on the Fill & Line option and select No fill under Fill.  Under Series options, change the gap width to 0%.

img5

img6

It’s almost done.  Just a few things are left.  Change the fill color of revenue earned, gross profit and net profit to a green.  Now you can change the scale, add data labels, etc and format it further.

img7

img8

That’s it, a simple waterfall chart.  This can be really useful when you want to include it in your reports and show data from a different angle.

Users are saying about us...

  1. Great guide! But this way good for rare using of waterfall charts. If you create waterfall charts often and in large quantities, you should automate the creation process. You can do it easy if you install Waterfall Chart Studio Excel add-in.
    By the way, waterfall charts become the standard type in Excel 2016. But if you use older version of Excel, Waterfall Chart Studio will be useful for you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube