How to create a waterfall chart in Excel
345.3 هزار بار بازدید -
4 سال پیش
-
How to create a waterfall
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.
⏱️TIMESTAMPS⏱️
00:00 Why waterfall charts are a useful reporting tool
00:30 How waterfall charts are built up
01:07 Build a waterfall chart in any version of Excel
04:04 Create a waterfall chart in Excel 2016, Excel 2019 or Excel 365
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 #bridgechart and then you can choose which method works best for you!
In older versions of #Excel, the only way to construct a #waterfallchart, 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.
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.
⏱️TIMESTAMPS⏱️
00:00 Why waterfall charts are a useful reporting tool
00:30 How waterfall charts are built up
01:07 Build a waterfall chart in any version of Excel
04:04 Create a waterfall chart in Excel 2016, Excel 2019 or Excel 365
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 #bridgechart and then you can choose which method works best for you!
In older versions of #Excel, the only way to construct a #waterfallchart, 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.
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.
4 سال پیش
در تاریخ 1399/02/05 منتشر شده
است.
345,365
بـار بازدید شده