How to create waterfall chart in Excel (bridge chart)

If you use Excel quite often, you know firsthand about the benefits of charts. Graphical representation of your data turns to be very helpful when you want to make a comparison or pinpoint a trend at a glance.

Microsoft Excel has lots of predefined chart types, including column, line, pie, bar, radar, etc. In this article we will go above and beyond creating basic graphs and have a closer look at a special chart type - the Waterfall chart in Excel. You will find out what a waterfall chart is and how helpful it can be. You will know the secret of creating a waterfall chart in Excel 2010 - 2016 and learn about different utilities that can do it for you in a minute.

So let's start brushing up on your Excel skills! :)

What is a Waterfall chart in Excel?

First let's see how a simple waterfall chart should look and when it can come in handy.

A waterfall chart is actually a special type of Excel column chart. It is normally used to demonstrate how the starting position either increases or decreases through a series of changes.

Use a standard Excel Stacked Column chart type to create a waterfall chart in Excel

The first and the last columns in a typical waterfall chart represent total values. The intermediate columns appear to float, and show positive or negative change from one period to another, ending up in the final total value. As a rule, these columns are color-coded for distinguishing positive and negative values. A bit further in this article you'll know a trick how to make the intermediate columns float.

A waterfall chart is also known as an Excel bridge chart since the floating columns make a so-called bridge connecting the endpoints.

These charts are quite useful for analytical purposes. If you need to evaluate a company profit or product earnings, make an inventory or sales analysis or just show how the number of your Facebook friends changed during that year, a waterfall chart in Excel is just what you need.

How to build an Excel bridge chart

Don't waste your time on searching a waterfall chart type in Excel, you won't find it there. The problem is that Excel doesn't have a built-in waterfall chart template. However, you can easily create your own version by carefully organizing your data and using a standard Excel Stacked Column chart type.

Let's create a simple sample table with positive and negative values to understand the things better. I'll take the sales amount as an example. If you look at the table below, you'll see that the sales grow during some months, and they fall during others as compared to the starting position.
Make a simple table with positive and negative values to provide data for a waterfall chart

Excel bridge chart will be a perfect way to visualize the sales flow over twelve months. But if you apply a Stacked Column chart template to these particular values now, you'll get nothing similar to a waterfall chart. So the first thing you should do is carefully rearrange your data.

Step 1: Rearrange the data table

You start with inserting three additional columns in your Excel table. Let's call them Base, Fall and Rise. The Base column will be a calculated amount that is used as a starting point for the Fall and Rise series in the chart. All the negative numbers from the Sales Flow column will be placed in the Fall column and all the positive numbers will be in the Rise column.
Insert three additional columns to modify the data table

I've also added the End row at the bottom of the Month list to calculate the sales amount for the whole year. Now move to the next step and fill in these columns with the necessary values.

Step 2. Insert formulas

The best way to complete the table is entering special formulas in the first cells in the corresponding columns and then copy them down to the adjacent cells using the fill handle.

  1. Select cell C4 in the Fall column and enter the following formula there: =IF(E4<=0, -E4,0)

    The formula says that if the value in cell E4 is less or equal to zero, the negative number will be shown as positive and the positive number will be displayed as zero.

    Note. If you want all the values in a waterfall chart lie above zero, you need to enter the minus sign ( - ) before the second cell reference (E4) in the formula. And two minuses will make a plus.
  2. Copy the formula down to the end of the table.
    Type in the formula in the first cell of the Fall column and copy it down with the fill handle
  3. Click on cell D4 and type in =IF(E4>0, E4,0).

    It means if the value in cell E4 is greater than zero, all the positive numbers will be displayed as positive and the negative ones as zero.

  4. Use the fill handle to copy this formula down the column.
    Enter the formula in the Rise column and copy it down to the adjacent cells
  5. Insert the last formula =B4+D4-C5 in cell B5 and copy it down; include the End row.
    Insert the formula to calculate base values

This formula calculates base values that will prop up the rises and falls to the appropriate height.

Step 3. Create a standard Stacked Column chart

Now your data are well-organized and you are ready to build the chart itself.

  1. Select your data including the column and row headers, exclude the Sales Flow column.
  2. Go to the Charts group on the INSERT tab.
  3. Click on the Insert Column Chart icon and choose Stacked Column from the drop-down list.
    Click on the Insert Column Chart icon to open the drop-down menu

    The graph appears in the worksheet, but it hardly looks like a waterfall chart. Take the next step and turn the stacked column graph into Excel bridge chart.
    Choose Stacked Column from the drop-down menu to apply it to your data

Step 4. Transform the column graph into a waterfall chart

The time has come to know the secret. You just need to make the Base series invisible to get a waterfall chart from a stacked column.

  1. Click on the Base series to select them, right-click and choose the Format Data Series… option from the context menu.
    Right-click on the Base series and choose the Format Data Series option from the menu

    The Format Data Series pane immediately appears to the right of your worksheet in Excel 2013 / 2016.

  2. Click on the Fill & Line icon.
  3. Select No fill in the Fill section and No line in the Border section.
    Select No fill and No line in the Format Data Series pane to transform the column graph into a waterfall chart

    When the blue columns become invisible, just delete Base from the chart legend to completely hide all the traces of the Base series.
    Make the Base series invisible to get a waterfall chart

Step 5. Format Excel bridge chart

Let's finish up with a little formatting. First I will make the flying bricks brighter and highlight the Start and End values in the chart:

  1. Select the Fall series in the chart and go to the FORMAT tab under CHART TOOLS.
  2. Click on Shape Fill in the Shape Styles group.
    Click on Shape Fill in the Shape styles group on the Format tab to change the column color
  3. Pick the color you want in the drop-down menu.

    Here you can also experiment with the column outline or add special effects to them. Just use the Shape Outline and Shape Effects options on the FORMAT tab to make changes.

    Then you should do the same trick with the Rise series. As for the Start and End columns, you need to color-code them individually, but they should be of the same color.

    When you are done, the chart should look like the one below:
    Color-code the columns in the chart to make it more readable

    Note. Alternatively, you can change the color and outline of the columns in the chart by opening the Format Data Series pane or choosing the Fill or Outline options in the right-click menu.

    Then you can remove excess white spaces between the columns to make them stand closer to one another:

  4. Double-click on one of the chart columns to bring up the Format Data Series pane.
  5. Change the Gap Width to something smaller, like 15%. Close the pane.
    Double-click on any column to display the Format Data Series pane and reduce the gap width

    Now the holes in the bridge chart are patched.
     Change the gap width in the Format Data Series pane to remove excess space between columns

    When you look at the waterfall chart above, some of the flying bricks seem to be of the same size. However, when you refer to the data table, you'll see that the represented values are different. For more accurate analysis I'd recommend to add data labels to the columns.

  6. Select the series that you want to label.
  7. Right-click and choose the Add Data Labels option from the context menu.
    Select the series, right-click and choose the Add Data Labels option to mark columns with the values

    Repeat the process for the other series. You can also adjust the label position, the text font and color to make the numbers more readable.

    Note. If there is an apparent difference in column size and the specifics aren't important, you can omit the data labels, but then you should add a Y-axis for better data interpretation.

When you are done with labeling the columns, just get rid of unnecessary elements such as zero values and the legend. You can also change the default chart title to something more descriptive. Please take a look at one of my previous blog posts how to add titles to Excel charts.

My waterfall chart is ready! It looks completely different from the commonly used types of charts and it is very readable, isn't it?
Add data labels to the columns in the chart to make it complete

Add-ins for creating waterfall charts

As you can see, it is not difficult at all to manually create a waterfall chart in Excel 2016 - 2010. But if you don't want to mess with rearranging your data, you can use special add-ins that can do all the work for you.

Jon Peltier offers to use his time-saving Peltier Tech Chart Utility to automatically create waterfall charts and other custom graphs from raw data. You can choose to make either a standard Waterfall Chart or a Stacked Waterfall Chart. It is not necessary to enter any formulas, just select your data, click the Waterfall Chart command in the Ribbon, set a few options, click OK and Excel bridge graph is ready. In addition to custom charts, the add-in provides you with different Chart, Data and General Tools to make your work in Excel easier.
Click on the Waterfall icon in the Custom Charts group on the Peltier Tech tab to choose the desired type

You can even create a waterfall chart online and receive it as an Excel file by email. It is possible thanks to a great waterfall-chart online service. You just need to submit your data, specify your email address and wait less than a minute while your chart is generated. Then check your inbox. The waterfall chart is sent to you in an Excel file. You can then change the title, labels, colors, etc. like in any other Excel chart. However, you cannot change the numeric data. In this case you will have to create a chart again. You should also follow some rules of submitting your data if you want your waterfall chart to look as expected.
Create a waterfall chart online and receive it as an excel file by email

The more complex chart you want to build, the more complex formulas you have to enter when rearranging your data. And the chance of getting an error is increasing. In this situation the Waterfall Chart Creator add-in for Microsoft Excel can help you save your time and effort. With this add-in you can create, change and update multiple waterfall charts at a time. It allows you to specify colors, solid or gradient fill, show values and position, and gives you many other options. You can also customize the default settings and colors for new charts.

Now you've got a collection of waterfall charts in Excel. I hope it won't be a problem for you to manually create your own version of a bridge graph. Otherwise, you can take advantage of Excel waterfall chart add-ins.

Thank you for reading.

If you are curious of other means of data visualization, the following articles may also be interesting for you:

149 responses to "How to create waterfall chart in Excel (bridge chart)"

  1. Joe says:

    Why couldn't you include the actual worksheet from this tutorial?

  2. Dookie says:

    What he said.

    Also, my series are showing zero's across the other series. How can I address this?

  3. Jeff says:

    How do you get the end series to show up?
    I agree having the actual spreadsheet would be a big help.

  4. Joe says:

    Great Tutorial, thanks for taking the time and showing this first of all.

    Dookie - I got rid of the zeros by clicking on each individual data point and deleting them. Can the original poster please advise if this is the method that was used as well?

    Jeff - The end series is shown in the example above as the data in cell B17 which is "7375" Must be included in the data used on the graph.

    Thanks again

  5. Dean Johnson says:

    This is brilliant. Saves purchasing a 3rd party add-in to achieve the same thing.

    To manually color the first and last bars, click it once (this will select the whole series), then click it again (this will select the single bar). Right click and choose 'Shape fill'.

    Well done Ekaterina. Thanks for sharing.

  6. samir says:

    Thank you very mach

  7. Anonymous says:

    How to build the chart we should use to show what would have happened if we had made the change in 2013

  8. Nick C-O says:

    Great tutorial, thank you. Thanks also for explaining exactly what this chart is for.

    • AB says:

      I managed to get the negative sign by going into 'Format data labels'> Number> Type (in 2013 version or Format Code in 2007 version) (default says General), add a - in front of the General.

  9. Sam says:

    Thanks for a great Tutorial !

    One Question, how are you getting minus sign in the data labels for negative values? All the values in fall column are positive?

    Thanks,
    Sam

    • AB says:

      I managed to get the negative sign by going into 'Format data labels'> Number> Type (in 2013 version or Format Code in 2007 version) (default says General), add a - in front of the General.

      • Anonymous says:

        Thanks a lot, it worked !

        Also, is there a way to get data labels on the top and outside the bars? Excel does not provide a default option for this.
        Usually suggested method is to create a total series on top of stacked columns and hide the bars. In this case that will also not be possible

  10. ST says:

    How did you get the negative sign to be shown on the data label values? Also my numbers are different starting the month of May. I checked my formulas few times and it looks fine but my end value is 6835! Thanks

  11. Scott says:

    Thank you, very straight forward

  12. Ian Christie says:

    Thank you for this. Ridiculous deadline has been met...

  13. Tee says:

    This was very helpful! Can you please do one on how to create the same chart but this time with various components that make up the data. For ex. lets say there were 4 different departments that makes up your sales number of 4,000. How do you show the 4 different departments on 1 bar with different colors and what each of their sale numbers were?

  14. Ashok Kr Prasad says:

    Its amazing. Wonderfully explained. Going through this step by step even a layman can prepare the Waterfall Chart. Thanks a lot.

  15. Mark says:

    I was wondering how you get the blocks to go below the x axis if the data is made up of many negative values. Currently if there are lots of negatives then they just sit on the x axis and don't go below.

    • Howard Rubin says:

      To get negative bars below the x axis, you need 2 further datasets. A green one for ascending bars and a red one for descending bars. Then set the number values so bars that cross the x axis are made in 2 pieces: One piece above the x axis and one below. HTH

  16. Sowrirajan says:

    Excellent tutorial. Thanks for the time taken to prepare and explain. god bless you!!!

  17. Riddhish desai says:

    Awesome...Thanks for such nice explanation and guidance. It help me a lot.

  18. ramunas says:

    and what if the base is <0? I think this dos'nt work

  19. tina kim says:

    Dear all,
    I have been trying to drawn the waterfall chart as your tutorial but one thing i should ask about the data labelled of negative in fall column, in my spreadsheet it could not have minus sign. How can i fix it?
    Thank you for your fast reply

  20. caroline says:

    I found that in summing the 'base' column it was adding all 3 items together rather than minusing the 'fall' figure, I counteracted this by adding a double minus symbol in the equation.

  21. Atif says:

    Thank You!

  22. Ward says:

    Great job. This was incredibly helpful!

  23. Matt says:

    Thanks for this, had never even heard of this kind of graph until I got asked to produce one. Good, simple instructions.

  24. philip says:

    Thank you! It is very easy to make waterfall chart with you help.

  25. Prakash GK says:

    Thank you! Very well explained.

  26. Gabor says:

    Hello,
    First of all, thanks for the great tutorial.
    I have following problem:
    the Rise column appears appropriately, beginning always at base level, but the Fall column always begins at zero level. I tried to change the sequence between Base, Fall and Rise but the Fall always is below the 2 others.
    How can I move the Fall chart above Base?
    Thanks for your answer in advance!

  27. Gabor says:

    How can I separately show the Start and End balances from the increase or base numbers? If I enter the balances in the Rise column,they´ll have the same colour as rises in the chart. If I enter them into the base column, they will disappear.
    Thanks for your help!

  28. Alf says:

    Hi Ekaterina,

    Thanks for your tutorial. Unfortunatelly it does not work for negative values:-(

    David

  29. Gurdip says:

    It doesn't seem to work with negative values when the base starts at zero. Anyone else having this issue?

  30. jag says:

    Ekaterina - thank you for the great tutorial. I really enjoyed creating a waterfall graph to highlight a GM bridge by significant customers. Could not have done this without your Tutorial.

  31. babu says:

    I have a waterfall chart but in the first column only the fill the start and end is filled with a line a dark line on top any idea how is done
    Any idea?

  32. Satish says:

    Hello

  33. Mohammed H says:

    Dear Jeo
    can you please send us link of video.

    thanks

  34. Vinoth T says:

    hi,

    its really useful every one and I want all chart how to make it easily I need some more explanation other its good work thanks to all and am working fully excel and PowerPoint I want to know some technical steps that's useful to my work thanks that's all thanks to all dude.

  35. Vinoth T says:

    dude can you please send all chart making step...

  36. ray says:

    how can a water fall chart be created using all positive values

  37. RichHare says:

    What an excellent article, perfectly articulated and demonstrated, this is impressive work.

    Thank you for taking time to publish this.

  38. Krisana says:

    It solves my problem. Thank you.

  39. Mohammad Shabbir says:

    Thank you very much , the way it is explain its fantastic

  40. Anonymous says:

    Brilliant, thank you.

  41. Anonymous says:

    thanks.It really helped

  42. Poshela says:

    Much appreciated. Your article has made it so simple to understand the waterfall graphs

  43. Sara says:

    Thanks, great help

  44. Dr. R. Ranjan says:

    What an excellent article on Waterfall chart, perfectly articulated and demonstrated, this is impressive work.

  45. Adam says:

    Great article! Thank you.

  46. tracey says:

    How can I get rid of the start column completely, I do not want it to show up in my chart, but great chart, it's really simplified waterfall charts for me :-)

  47. Tony says:

    Thanks for the tutorial, very easy to follow (for us excel dummies!)

  48. Stranger says:

    Vey useful

  49. Anonymous says:

    Awesome article!!!!!

  50. Still needs some work says:

    So, to fix the "zero display" issue, you can create another column to chart (or enhance what's there - up to you), with an "IF" statement that replaces zero values with "NA()" (without the quotes).
    So, if the original calculation is in "I3", a formula in another column (to be charted) with this formula results in "#N/A" showing in the cell, ant that will not get charted:
    =IF(I3=0,NA(),I3)

    Also, if my chart had the dates in the first column on the zero axis, but there were was nothing below zero; so to make the dates as labels at the bottom, click on the Y-axis, Format axis, Axis options, and make the Minimum "Fixed" and = 0.

    As others have said, a downloadable version of the spreadsheet would be helpful, especially if it incorporated some of the comments above. (Otherwise, that was / is a really long vacation :) ).

  51. Vineet Jain says:

    Dear Ekaterina Bespalaya - Thanks for sharing this tutorial; so easy for even excel dummies to follow. Enjoy your vacation to the Max.

  52. Mike says:

    Outstanding & easy to follow!

  53. ganesh says:

    Great Post

  54. Pavle says:

    Dear Ekaterina, thank you for sharing this. I agree will all positive comments.

  55. John says:

    Yes, very helpful guide. Can anyone advise on how to include connector lines between the monthly values
    (------) Thanks.

  56. Z says:

    Very very very useful. Takes minimum time to understand and execute
    Thank you

  57. Lee says:

    Thank you so much for sharing, this really helped. God bless!

  58. Binaya panigrahi says:

    Thanks alot....very nicely explained a complex chart....awsome....cherss...... :)

  59. Grover says:

    Excellent, crystal clear explanation! Thank you so much.

  60. sandip says:

    Hello Thank you for your note.just getting confuse with end data how to populate that in graph.

  61. Jennifer says:

    Thank you so much for publishing this!! Awesome detailed instruction for something a little bit intricate!! Really needed to do some of these charts - deadline today and never did them before. Thanks again!

  62. Gary says:

    When you have several consecutive negative values forcing the base to become negative the falls do not appear below the x axis and show as positive values. Is there a fix for this please?

  63. Kasper says:

    Thanks for this easily understood tutorial. I have one question which I think still has not been resolved. How did you manage to remove the "base legend" without removing the invisible bar chart? When I try to remove my similar bar chart it makes the floating bars fall to the ground...

  64. nakull thakur says:

    thanks for the information

  65. Abhinov says:

    Very Well explained..I developed PowerPoint/Excel plugin for creating Waterfall chart just like that..Contact me at abhinovpankaj1@gmail.com for more details.

  66. Foram Gandhi says:

    Thank you for the simple, yet effective explanations and illustrations.

  67. Naledi says:

    How about in S.A

  68. Rakhee says:

    Thank you - very helpful and user friendly.

  69. Nick Finia says:

    Very NICE!!! Thank you!!

  70. Muneer says:

    I love you, you saved my day at office.

  71. JK says:

    Still getting confused with the last bar, the "end" column, cannot select and format on its own, can only select the entire series of "hidden" bars that are hidden/underneath the incremental changes, i.e. the rise/fall bars. Using Excel 2007 (no, cannot upgrade)

  72. Chris says:

    This is great thank you so much!! I had a question. If I wanted to split the fall or rise into three stacked groups as opposed to one how would I do that? For example, January would have Group A=+1500, Group B=+1500, Group C=+500 = 4000 all in one bar.

    Thanks again! :)
    -Chris

  73. Amar Nayak says:

    Is there a way to break the Bar chart in waterfall bar chart. One of the value in my data set is quite big and everything else looks very small. So I would like to break the Y axis and the big bar so that all bars are properly visible.

  74. Shubhangi says:

    Great tutorial

  75. Prasad says:

    Thank you So much this was the most easiest explanation i found on creating waterfall graphs.

  76. Amy Dann says:

    Hello , thank you for this helpful demonstration it has taught me alot . My chart is populated and looks exactly as yours does other than the series fall on my chart is showing as 0s instead of the minus numbers . how can i change this without having to enter all of the figures manually please? Many Thanks Amy

  77. Tuton Paul says:

    Very good and easy explanation , Thank you.

  78. Chandan Kumar Pandey says:

    Hi All,

    I want Create Hours Base Graph. can you tell me how can create this.

    Question Example:- 10:00 Am 01:00 PM Total Hours ? Graph Chart?

  79. Sara says:

    It worked well for me.Thanks a ton!

  80. Reid says:

    very helpful, thanks

  81. Bhanu says:

    Thanks much for wonderful tutorial. Nobody could teach the technique in such an easy to understand manner.

    Will go through all other tutorials.

    Thanks a lot , you are doing an awesome work.

  82. Arun says:

    Hi,

    Need to create a waterfall chart with price growth & volume growth data.

    Can u plz guide?

    Arun

  83. khalil says:

    Genius. thank you. it works perfectly.

  84. Ian says:

    Thank you for a clear and concise explanation.

  85. Elson says:

    Very usefull. Congrats

  86. BJo says:

    Great explanation. It would be useful to know how to deal with negative figures as the above method doesn't seem to work for them.

  87. Dan Treview says:

    Not to nit-pick, but "=IF(E4<=0, -E4,0)" is the same thing as "=-MIN(0,E4)" and the latter is easier to understand. I see people use IF statements for this all the time, but learning MIN and MAX make writing out and auditing the formulas a lot easier. Just FYI.

    Also, it looks like Excel 2016 finally has built-in waterfall chart support. I haven't tried yet so if someone has, please post your experience with it. I hate manually doing this (rather, I hate executives that require these).

  88. kk says:

    if my start value is Zero, how to do the waterfall? thanks

  89. Sergio says:

    This is a great and simple solution. Many thanks.
    But I always use bridges in situations where I have to show 'negative' going into 'positive' ie, EBITDA improvement.
    The current solution will not work for negative to positive positions, I think. Do you have any tutorial that would address this case?
    Best

  90. George says:

    Need a solution for water falls that go negative!

  91. Vipul Shah says:

    Thank you first of all for this great tutorial and simple to use instructions. When I tried to create my WF chart, after I started with all positive values, and then when the negative values started and the first negative data set "added" to the total of the positive values - that is the stacked column actually went higher rather than dropping - the rest of the negative values after that - it dropped and the final ending number was proper to scale - but the first negative value - the column actually went up instead of dropping - Not sure what I am doing wrong? Please help if you are able to.

  92. Harrison Delfino says:

    I use MarketXLS for this and it's great.

  93. Akin says:

    Thanks for sharing!

  94. Klein says:

    Oh my god this was amazing!!! Thank you so much!

  95. Lisa says:

    This is awesome!! It really sorted me out. Thank you.

  96. Steve Miller says:

    Thank you very much. Great explanation. Just what I needed.

  97. Ricardo says:

    Thanks!!

  98. Sasha says:

    Brilliant and very helpful step-by-step tutorial. Many thanks.

  99. Alexander Menezes says:

    Thank you for taking out time to explain the tutorials. It is excellent and easy to use. Thanks.

  100. Sunil says:

    Works like a charm. Thanks

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)