“Waterfall” for the “poor”

Waterfall Chart, also known as the Bridge Chart, is a convenient way to show the cumulative effect of the growth or decay of data in the initial and final set value. From the 2016 version this chart has become an integral part of Excel, but it can also be done by creating specific formatting of data series combinations. It can be said that this text will be about “Waterfall” chart for “users with cheaper tickets” …

We will show how to use such chart in the case of tracking profits in one calendar year. To create it we first need a table of data with the initial value (profit status as of December 31, 2014), the value of the increase / decrease in profit per month and the final value (profit status as of 01.01.2016). In front of this column we will insert three new columns. The first one is to call the basis and it serves us to record the cumulative profit value. We will call the other a loss and there are absolute values of loss (from the column cash flow), and the third we will call the gain and there are absolute values of gain (also from the column cash flow). Cells in the first column (base) contain a formula that represents the sum of the value of previous month’s profit with a gain from the current one, from which the loss from the current month is further subtracted.

When, by copying the formula, we fill in the table with the values you need to select the entire table except the last column. Then, by clicking the F11 button, we automatically create a chart in the new worksheet. Next we need to click on the Design tab, and choose the Change Chart Type option. A dialog box for choosing a chart type will open, where you should select the Stacked Column type.

Further, we need to hide the cumulative value of the base. We will do this by selecting a series of basics, and then we put the series and frame color to be white. We will do this for all columns except for the first one (beginning) that we will paint in blue. Further, we can change the color series loss (eg to be red) and gain (eg to be white). The final value should also be colored in blue to highlight the initial and final state.

At the end, we can add several more visual elements to make our chart look beautiful. First, the size of the space between the columns can be reduced to make them closer to one another. We can delete the value of the base from the legend, add the title, remove the background lines on the chart. Finally, it would be convenient to add column names with the values of each series, remove the redundant series, and put the prefix “-” in front of the negative ones. Once we have performed the mentioned actions we will get a typical “waterfall” chart.