In this lesson, you will learn how to insert a waterfall chart in Excel.
Table of Contents
What is a waterfall chart?
In Excel, a waterfall chart is a powerful tool for visualizing the influence of both positive and negative data on the end result, typically reflecting net profit.
Creating a waterfall chart in Excel is a straightforward and effective process. A waterfall chart can be used, for example, to show the revenue and expenditure of a personal or business venture. A more ambitious application is to illustrate a profit and loss statement for a business. It is simpler to display your own income and expenditure in the monthly budget. You have more difficult examples. The waterfall chart will show the individual components of the profit and loss (simplified). Subsequently, we consider:
- total revenues,
- cost of sales of products,
- Gross profit on sales,
- management costs,
- Cost of sales,
- other operating costs,
- financial costs,
- net profit.
How to Insert a Waterfall Chart in Excel?
Start with the data. Create a table with sample amounts of revenues, expenses, and net profit calculation. (The table is formatted like an accountant’s.)
In the second column, enter the calculated residual profit. It is through the remainder of the profit rise, ‘waterfall’ on the chart. For example, the remaining profit for the financial cost is the sum of the financial cost and the cells below, or net profit.
Now create a stacked column chart. You won’t need the legend, so go ahead and remove it.
Currently, the chart doesn’t resemble a waterfall chart. To change this, click on one of the blue columns (Excel ticks all) and format the column. A dialog box appears, where you should select ‘No fill’.
To make it look like a waterfall chart in the same dialog box, in the ‘Series Options’ tick ‘Gap Width’ at 0% (‘No Gap’).
For an even more attractive graph, format the column with the revenue and profits by setting the fill color to green.
After making a few adjustments and applying formatting, your waterfall chart will be ready. Include data labels and consider adjusting the axis scale on the chart.
Your waterfall chart is now complete. Doesn’t it look intriguing? It looks different from the commonly used types of charts. In the same way, the waterfall chart can be used to illustrate your monthly budget.
