Waterfall chart in Excel

Waterfall chart in Excel
Last Updated on 02/05/2023
Reading time: 3 minutes

What is a waterfall chart?

A waterfall chart is used to display how each item impacts the overall result.

For example, in this bar chart, you see the variation of the cash flow between the beginning and the end of the month.

Bar chart for the cashflow

However, you don't know which product has a positive or negative impact on the overall result 🤔🤨

It is precisely in this case that a waterfall chart is helpful.

Waterfall chart

As you can see in this chart, we can quickly identify the data that acted positively or negatively on the month-end result.

  • Positive = Product A and Product E = 70+50 = 120
  • Negative= Product B, Product C and Product D = -40-10-20 = -70

So 2 products have a positive impact and 3 a negative impact, and the overall result is 50

How to present the data

To create a waterfall chart, you must start:

  1. Start by entering your first value.
  2. Then enter all the "intermediate" data.
  3. End your list with the SUM function.
Data for a waterfall chart

To avoid any mistakes, the value for the end of the month is a SUM formula

=SUM(B1:B6)

Step 1: Create the chart

Since Office 2016, the Waterfall chart has been added to the Excel chart type in the ribbon.

  • Select all your data.
  • Go to Insert > Waterfall Chart
Waterfall chart in the ribbon

Instantly your chart appears, but there is a problem with the last bar

Waterfall chart but the last bar must be fixed

In fact, at this point, Excel does not know if this bar is the final result or a bar like the other values

Step 2: Edit the last bar

To correct the last bar, do the following:

  1. First, select your entire data set.
  2. Then click on the last bar.
Select the last bar of the waterfall chart
  1. On the setting pane, check the option Set as total.
Set the last column as total

The last bar now originates from point 0 of the x-axis.

Waterfall chart with the total bar

You can see that

  • the total bar has a different color
  • the color for the increase and decrease values are different

Step 3: Change the value of the vertical axis

To better visualize the variations, it is better to change the starting point of the vertical axis.

The bars start from point 0, but in our example, we want to change this default value to 900.

  1. Select the vertical axis.
  2. In the axis options, change the minimum value to 900.
Change the value of the vertical axis

And the last modification is to change the color of the first bar

Now, you have a perfect waterfall chart, and you can see the variation of each product on the overall total.

Waterfall chart 1

Leave a Reply

Your email address will not be published. Required fields are marked *

Waterfall chart in Excel

Reading time: 3 minutes
Last Updated on 02/05/2023

What is a waterfall chart?

A waterfall chart is used to display how each item impacts the overall result.

For example, in this bar chart, you see the variation of the cash flow between the beginning and the end of the month.

Bar chart for the cashflow

However, you don't know which product has a positive or negative impact on the overall result 🤔🤨

It is precisely in this case that a waterfall chart is helpful.

Waterfall chart

As you can see in this chart, we can quickly identify the data that acted positively or negatively on the month-end result.

  • Positive = Product A and Product E = 70+50 = 120
  • Negative= Product B, Product C and Product D = -40-10-20 = -70

So 2 products have a positive impact and 3 a negative impact, and the overall result is 50

How to present the data

To create a waterfall chart, you must start:

  1. Start by entering your first value.
  2. Then enter all the "intermediate" data.
  3. End your list with the SUM function.
Data for a waterfall chart

To avoid any mistakes, the value for the end of the month is a SUM formula

=SUM(B1:B6)

Step 1: Create the chart

Since Office 2016, the Waterfall chart has been added to the Excel chart type in the ribbon.

  • Select all your data.
  • Go to Insert > Waterfall Chart
Waterfall chart in the ribbon

Instantly your chart appears, but there is a problem with the last bar

Waterfall chart but the last bar must be fixed

In fact, at this point, Excel does not know if this bar is the final result or a bar like the other values

Step 2: Edit the last bar

To correct the last bar, do the following:

  1. First, select your entire data set.
  2. Then click on the last bar.
Select the last bar of the waterfall chart
  1. On the setting pane, check the option Set as total.
Set the last column as total

The last bar now originates from point 0 of the x-axis.

Waterfall chart with the total bar

You can see that

  • the total bar has a different color
  • the color for the increase and decrease values are different

Step 3: Change the value of the vertical axis

To better visualize the variations, it is better to change the starting point of the vertical axis.

The bars start from point 0, but in our example, we want to change this default value to 900.

  1. Select the vertical axis.
  2. In the axis options, change the minimum value to 900.
Change the value of the vertical axis

And the last modification is to change the color of the first bar

Now, you have a perfect waterfall chart, and you can see the variation of each product on the overall total.

Waterfall chart 1

Leave a Reply

Your email address will not be published. Required fields are marked *