How to Create a Waterfall Chart in Excel

In this lesson, you will learn how to insert a waterfall chart in Excel.

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.

Waterfall chart

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.)

Waterfall chart table data

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.

Waterfall chart table data profit left

Now create a stacked column chart. You won’t need the legend, so go ahead and remove it.

Waterfall chart Excel

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’.

See also  How to Break Y Axis in Excel

Waterfall Chart Format

To make it look like a waterfall chart in the same dialog box, in the ‘Series Options’ tick ‘Gap Width’ at 0% (‘No Gap’).

Waterfall Chart Format Data Series

For an even more attractive graph, format the column with the revenue and profits by setting the fill color to green.

Waterfall chart example

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.

Waterfall 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.

Download the free spreadsheet here.