How to Create an Intuitive Waterfall Chart in Your Excel Worksheet

Chart in Excel can help us analyze data and information better. Here we will introduce how to create an intuitive waterfall chart in Excel.

Suppose now you need to create a waterfall chart for the sales volume.

In this chart, you need to show the fall and rise of the sales volume based on the number of last month. Now you may follow the steps below to see how it works.

Arrange the Range for the Waterfall Chart

Before you create a waterfall chart, you need arrange the range in the worksheet.

1. Click the tab of column B to select the whole column.
2. And then right click.
3. In the new menu, choose the option “Insert”.

Thus, you have inserted a column into the worksheet.

4. Now repeat the above steps and insert two columns into the worksheet.
5. Now input the three items into the first row: Base, Fall, Rise.
6. Because here we need to reflect the changes of every month, the base of the first month is the sales volume. And the fall and rise are both 0.
7. Now input the formula into cell F7 in the worksheet:

=E3-E2

8. And then double click the fill handle in F3 to fill the whole column to get the result of the changes.
9. Now input this formula into cell C3:

=IF(F3>0,””,-F3)

10. And then use the fill handle to fill the whole column.
11. After that, input another formula into cell D3:

=IF(F3>0,F3,””)

12. Next still use the fill handle for this column. Thus, the fall and rise numbers will appear in the two corresponding columns.
13. After that, you need to input the base of the range. If the sales volume of a month is smaller than the former month, the base is the sales volume of the current month. Else if the sales volume of a month is greater than the former month, the base is the sales volume of the former month. In order to get the result in the “Base” column, now you can input this formula into the cell B3:

=IF(E3>E2,E2,E3)

14. And then use the fill handle to fill the column.

Create a Waterfall Chart in Excel

Now you have finishing setting the range in Excel. And in this part, you can insert the waterfall chart into the worksheet.

1. Clear the content in cell A1.
2. Select the area of the first four columns.
3. And then click the tab “Insert” in the ribbon.
4. After that, click the button “Column” in the toolbar.
5. Next choose the option “Stacked Column” in the submenu. Therefore, you have inserted a stacked column into the worksheet.
6. In this stacked column, right click one of the bars of the series “Base”.
7. And then in the new menu, choose the option “Format Data Series”.
8. In the “Format Data Series” window, click the option “Fill” on the left.
9. And then choose the option “No fill” on the right part.
10. Now continuing click the “Border Color” in the window.
11. And then choose the option “No line” for the border color.
12. When you have finished the setting, close the window. And then you can see that the bars in the series “Base” are all hidden. And because the sales volume of October and November is the same, there is no bar in the “November”.
13. Now click the bar of January. Here you will see that all the bars for the series “Base” are selected.
14. Click the bar of January again. Therefore, you have only selected the bar in this chart.
15. Now right click the bar that you have selected.
16. And then choose the option “Format Data Point” in the menu.
17. In the “Format Data Point” window, choose the option “Fill” on the left.
18. And then choose one option in the window and set a color for the bar. Here we choose the “Solid fill” and set the color.
19. After you have set a color, close this window.

And then you can see the result in the worksheet. This is the result of the waterfall chart. All the fall and rise are displayed in the chart. Therefore, the next time if you need to create a waterfall chart, you can follow this method in this article.

Deal with Excel File Corruption

The application of Excel is far from perfect. Therefore, it will sometimes refuse to work. On the other hand, with the powerful feature in Excel, many companies need to rely on it. And many essential data and information are stored in Excel. Hence, Excel files have long been the victim of malicious hackers. To avoid the major loss that you might suffer, you can prepare an Excel repair tool at hand. This tool can repair xlsx files and other Excel files easily. And with this tool, almost all the lost data and information can be retrieved.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx and outlook repair software products. For more information visit www.datanumen.com