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.
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.
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:
- Start by entering your first value.
- Then enter all the “intermediate” data.
- End your list with the SUM function.
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
Instantly your chart appears, but there is a problem with the last bar
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:
- First, select your entire data set.
- Then click on the last bar.
- On the setting pane, check the option Set as total.
The last bar now originates from point 0 of the x-axis.
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.
- Select the vertical axis.
- In the axis options, change the minimum value to 900.
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.
