How to create a waterfall chart in Excel? Waterfall charts, also called bridge graphs, are an excellent way to summarize a variance analysis for business reviews. For example, understanding an EBIT walk graph is, for a lot of people, much easier than reading through big tables with numbers. In the example you see here, which was made in an older version of Excel, I am showing an EBIT walk from prior year to current year. To get from prior year EBIT of 100 to current year EBIT of 114, you look at the sum of the positive drivers in green (volume, mix, productivity, and foreign exchange impact), partially offset by the negative drivers in red (selling price, and raw materials inflation). You can do something similar for headcount, market share, and many more key performance indicators. In this waterfall chart tutorial, let me show you the two ways you can build a bridge chart, and then you can choose which method works best for you! In older versions of Excel, the only way to construct a waterfall chart, was to spread your source data over multiple columns. In this example, I have a column with the number for the initial value (prior year EBIT), a column with the number for the final value (current year EBIT), a column with the size of the green positive floating bars, a column with the size of the red negative floating bars, and the key to constructing the waterfall chart in older versions of Excel: the column specifying the height of the invisible bars. This waterfall chart is technically speaking a stacked column chart. The green and red floating bars are stacked on top of invisible bars that are set to “no fill” and “no outline”. You can see this more clearly when I temporarily set the color of the invisible bar to white. My floating green volume bar of 12 is actually a stacked bar of 112 in total height: 100 invisible, with 12 in green on top. As volume is the first element in this variance analysis, I set the value for the invisible bar at 100 which is the same height as the EBIT prior year bar. For the floating red price bar of 7, the process is slightly different: take the total height of the stacked previous bar of 118 and deduct the size of the red bar to come up with the height of 111 for the underlying invisible bar that supports the floating red price bar. This method of building a waterfall chart still works in Excel 2019. Simply select the data arranged in columns, go to Insert – Charts, and select a stacked column chart. This waterfall chart has a much more current design compared to the old one. Widen it a bit for the labels at the bottom to become horizontal. Click on the “Invisible” data series in the legend, right click, and set this to no fill and no outline. Set the “minus” floating bars to red. Set the “plus” floating bars to green. Change the current year EBIT bar to the same color at the prior year EBIT bar. Then get rid of the legend altogether, and add EBIT Walk as the chart title. We can then go to Format Axis to start the vertical axis at 80 and end at 120, in increments of 10. We can also add data labels to each of the data series: start point, increases, decreases, end point. The end result of the waterfall graph looks much better than the old layout! In Excel 2016, Excel 2019 and Excel 365, there is another way to build a waterfall chart, which doesn’t take as much upfront data preparation, as you can simply have your labels in the first column and the values in the second column. Starting point, pluses and minuses, and the ending point, all in one simple dataset! Select the data, then go to Insert – Charts – All Charts – Waterfall. You can now select your preferred layout from the options in “Chart Styles”. Let me stick to the default one on the left. What Excel has done for you in the new waterfall chart template, is to recognize increases and decreases. That part went well, and saved you time versus having to prepare the data in the stacked column chart approach that we discussed earlier. You will have to take some formatting steps now to make the waterfall graph work properly. First of all, Excel has plotted all of your data as either an increase or a decrease, which is obviously incorrect. Double-click on the EBIT prior year bar, right-click, and set as total. Same thing with EBIT current year: double-click, right-click, set as total. Excel now understands there are three categories (increase, decrease, total) instead of just two. Then adjust the colors. Click on “Increase” in the legend, right-click, and set this to green. Click on “Decrease” in the legend, right-click, and set to red. Click on “Total” in the legend, right-click, and set this to blue. You can now get rid of the legend, if you don’t want to display it. Let’s add a chart title to the waterfall graph. Data labels are already displayed, we don’t have to adjust anything there. What we can do is to format the axis to start at 80 and end at 120, for dramatic effect. Those are the two ways available in newer versions of Excel to create a waterfall chart: the old and time-tested way of the stacked column chart on the left with the floating bars on top of invisible bars, and the new more efficient way of using the Waterfall chart option on the right. The old method of building a waterfall chart in Excel required upfront data preparation. The new method still requires a bit of work, but that’s just on the waterfall chart formatting side. I think the new method is a lot quicker and easier, and plan to use it going forward! Let me know which one you prefer and why in the comments below! Whichever way you choose, the waterfall chart is the best way to present the outcomes of your variance analysis. Want to learn more about business, finance, accounting and investing? Then subscribe to the Finance Storyteller YouTube channel, and start watching the next video in the recommendations on the screen. Thank you.