What is a Waterfall chart?

A waterfall chart is a great way to show variance analysis from one point to the other. These charts help easily visualize the drivers which are responsible for the change. For Example. how the profit or revenue of a company grew from one period to the other.

They can also be used to tell the story for non-financial data. Like change in number of Full-Time employees from one period to another or reason for increase or decline in production from one time to the other.

Waterfall charts are available in excel 2016 and newer versions of excel. It is not available in earlier versions of excel.

Example

We have the profit and loss account of ABC Limited as shown below.

We want to create a waterfall chart showing how revenues have translated to Earnings before Interest and Tax (EBIT).

To insert a waterfall chart, Click anywhere on the data. Then go to the Insert Tab. Click on Recommended Charts. Select All charts and then select waterfall chart.

Click OK.

You get the default waterfall chart in excel.

In this chart, we see 3 series. Blue for the Increase, Orange for the Decrease and Grey for the Total. But, when we see the chart we find that there are only two series depicted on the chart. Blue and Orange. This is because Excel is not able to recognize the Sub-Totals (Gross Profit) and Totals (EBIT).

We need to tell excel which data points are Totals and Sub-totals. To insert a Sub-total for Gross profit, Click on the Gross Profit column twice, so that the other columns get faded out. Then Right click and select Set as Total (as shown below).

Similarly, repeat the step for EBITDA and EBIT. Once you tell excel which columns are the Totals. You get the Third series (Total) in your waterfall chart in Excel (See below).
Now we can format the chart. Remove Gridlines, legends (as we do not need them), remove the y-axis and give a Title to the chart. Also reduce the font size so that the x-axis is able to accommodate the labels.

Once the formatting charts in Excel is done, we have a chart like this.

Limitations of Excel inbuilt Waterfall chart?

Creating a default waterfall chart is very easy and can be done with just a few clicks. However, the inbuilt waterfall chart has some limitations. Let us discuss them one by one.

Available in only newer versions

The first limitation of this chart is that it is available only in newer versions of excel. So, if you are still working in Excel 2013 and earlier versions then you don’t have access to this inbuilt waterfall chart.

Read more: 10 MOST USED FINANCIAL FUNCTIONS IN EXCEL

Chart title cannot be made dynamic

The second limitation is that the title in this chart cannot be made dynamic. In other excel charts, If you click on the title and then link it to a cell by clicking on the formula bar, the chart title becomes dynamic. But here if you click on the title, excel doesn’t allow you to click on the formula bar.

In case you want to make the title dynamic. Then delete the default title and insert a text box and link it to a cell in the excel.

Formatting of the Connector lines cannot be changed

The formatting of the connector lines in this chart cannot be altered. If you click on the connector lines, excel gives you the option either to keep or remove them but doesn’t give the option to change the color or style of the connector lines.

Can’t handle negative values

This is a big limitation of this chart. In case the total is negative then we can’t use the inbuilt waterfall chart.

In the above profit and loss example, if we change the other income to -1,000 the EBIT becomes negative (600).

Look, what happens to the chart. The chart values move above the x-axis. EBIT is still shown above the x-axis. It should have moved to below the x-axis.

Restricted Color change

In this chart, the color change is restricted. If you right click on the chart and change the colors. It has to be done manually as the colors do not change dynamically with the change in data.

For example, if I change the color of COGS to Green, the other negative values still remain orange. See below.

Therefore, if we want to change the color of all the negative values in the above example, it has to be done manually.

Other alternative to change the color scheme of this chart is change the color theme. The default theme excel pics is the “Office” theme.

To change the color theme, go to page layout, color and select the color theme you want. You can also customize the colors by selecting the customize colors option.

The limitation here is that if you change the color theme, the color of all the waterfall charts in your excel workbook would change.

Restricted Data Label position

This is the last limitation of this chart. The data labels in the default chart are above the columns with positive values and below the columns with negative values. We can’t change the position of all the labels to above the columns if we want.

Despite these limitations, creating a waterfall chart has become much easier in newer versions of excel. It is easy and can be created with just a few clicks.

To improve your data analysis and visualization skills, please visit our online course Data Analyst Skills Training in Excel. This program will help you Go from beginner to an Expert in Data Analytics and Visualization skills in Excel? Join this program to create reports quickly through hands-on projects.

Keep Learning.