Creating Waterfall Charts in Excel – Example & Limitation
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. • Waterfall charts are available in excel 2016 and newer versions of excel. It is not available in earlier versions of excel.
Email: Support@skillfinlearning.com
Mobile: 9650010925
How to create Waterfall Chart in Excel – Example (1/5) • Here is the profit and loss account of ABC Limited as shown here. • We have to create a waterfall chart showing how revenues have translated to Earnings before Interest and Tax (EBIT).
Email: Support@skillfinlearning.com
Mobile: 9650010925
How to create Waterfall Chart in Excel – Example (2/5) • •
To insert a waterfall chart, Click anywhere on the data. Then go to the Insert Tab. Click on Recommended Charts.
Email: Support@skillfinlearning.com
•
Select All charts and then select waterfall chart.
•
Click OK.
Mobile: 9650010925
How to create Waterfall Chart in Excel – Example (3/5) We get the default waterfall chart in Excel 2016
• 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.
Email: Support@skillfinlearning.com
Mobile: 9650010925
How to create Waterfall Chart in Excel – Example (4/5) •
•
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).
Email: Support@skillfinlearning.com
Mobile: 9650010925
•
Similarly, repeat the step for EBITDA and EBIT.
•
Once we tell excel which columns are the Totals. We get the Third series (Total) in the waterfall chart in Excel (See below).
How to create Waterfall Chart in Excel – Example (5/5) • Once the formatting is done, we have a chart like this. • Now we can format the chart. • Remove Gridlines, legends (as we do not need them). Also remove the y-axis and give a Title to the chart. • Also reduce the font size (if needed) so that the x-axis is able to accommodate the labels.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Limitations of Excel inbuilt Waterfall Chart Not available in older versions Chart title – Non dynamic
Restricted Data Label position Waterfall Chart Limitations Can’t handle negative values
Restricted formatting changes Restricted color changes
Email: Support@skillfinlearning.com
Mobile: 9650010925
Not available in older versions
• Excel inbuilt waterfall chart is available only in Excel 2016 and newer versions. • Not accessible in older versions of Excel
Email: Support@skillfinlearning.com
Mobile: 9650010925
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 we 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 we click on the title, excel doesn’t allow us to click on the formula bar.
• In case we 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. Email: Support@skillfinlearning.com
Mobile: 9650010925
Formatting of the Connector lines cannot be changed If we click on the connector lines, excel gives us the option either to keep or remove them but doesn’t give the option to change the color or style of the connector lines.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Can’t handle negative values • 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.
• This is a big limitation of this chart. • In case the total is negative then we can’t use the inbuilt waterfall chart. • In our profit and loss example, if we change the other income to -1,000 the EBIT becomes negative (600).
• Email: Support@skillfinlearning.com
Mobile: 9650010925
Chart gets distorted when data total is negative
Restricted Color change • • •
In this chart, the color change is restricted. We can right click on the chart and change the colors. However, such change is not dynamic. For example, if we change the color of COGS to Green, the other negative values are still orange. See below.
Therefore, if we want to change the color of all the negative values in the above example, it can only be done manually. Email: Support@skillfinlearning.com
• 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. We can also customize the colors by selecting the customize colors option.
The limitation here is that if we change the color theme, the color of all the waterfall charts in the excel workbook would change. Mobile: 9650010925
Restricted data label position
• 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. •
Email: Support@skillfinlearning.com
Mobile: 9650010925
Data Label position cannot be changed in the Excel inbuilt waterfall chart
Conclusion • 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.
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com