Waterfall Chart Basics - Working with Positive Numbers - Part 2
By: Taylor Croonquist
Explore Waterfall Chart Basics, learn how to work with positive numbers.
Inserting a Stacked Column Chart:
Follow these steps to get started:
- Inserting a Chart: From the Insert tab (highlighted in red), select the Chart button (highlighted in blue), as shown in Figure 1 below.
Figure 1: Select the Chart button
- Selecting the Stacked Column Chart: From within the Insert Chart dialog box,
select the Stacked Column Chart (this is what we will manipulate to create the Waterfall Chart),
which inserts the chart onto your slide and opens up an Excel spreadsheet.
Figure 2: Select Stacked Column Chart
Note: Be sure to select the Stacked Column chart, and not the 100% Stacked Column chart.
Setting up the Numbers:
The most important step to building a Waterfall Chart is setting up the numbers in Excel correctly. Throughout this tutorial, I will use the below data points to create the Waterfall Chart (see Figure 3).
Figure 3: Data points to create the Waterfall Chart
- Prepping the Spreadsheet: With the above data inserted into the Excel spreadsheet starting at cell A8, the first step is to calculate the three data points we need for the Waterfall Chart; what I will refer to as the Base, the Value and the Total (see Figure 4 below). I recommend always calculating them to the right of your original data points.
Figure 4: Calculate the data points
Note: I've formatted the first and last numbers in the Base column (D10 and D14) with a grey fill, and hardcoded the number 0 into each cell. Whenever building Waterfall Charts, the first, last and any in-between sub-totals, always need to start with a base value of zero.
- Calculating the Value: Let's continue with the first row of information for our Waterfall Chart. We want to make the Value equal to our original number, so in cell E10, type in the formula "=B10" as shown in Figure 5 below.
Figure 5: In cell E10, type in the formula "=B10"
- Calculating the Total: For the Total, we simply want to add up the Base and the Value. So in cell F10, type the formula "=E10 + D10" as shown in Figure 6 below.
Figure 6: Adding the Base and the Value
- Filling in the Values and Totals: With the Value and Total calculated for the first row, we can then fill in the rest of the Values and Totals. So selecting the first two cells, E10 and F10, copy the formula down to row 14. You can do this by selecting the small square in the lower right-hand corner and dragging it down to F14 (see Figure 7).
Figure 7: Copy the formula down to row 14
- Calculating the 'Base': For the remaining Base numbers, we want to make them each equal to the previous data point's Total. So select cells D11 through D13, and type in the formula "=F10".
Figure 8: Select cells D11 through D13
And then holding the Ctrl key down, hit Enter, to input the formula into all of the selected cells, as shown in Figure 9, below.
Figure 9: Hold Ctrl and hit Enter