• LOGIN
  • No products in the basket.

How To Create A Waterfall Chart In Excel

If you use Excel, then am sure you are very aware of the benefits of Excel Charts. Data inserted into rows and columns can be sorted, filtered and displayed in a visual presentation. Using Graphs and charts you can add meaning to data which otherwise may just exist as rows and columns of data.

There are a lot of predefined charts in Excel, some of which include column, line, bar, pie etc. In today’s post, we will take a look at how to create a waterfall chart which is a little beyond the basic charts.

A waterfall chart is defined in Wikipedia as a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. This is normally used for analytical purposes to understand or explain the gradual transition in an initial value of an entity which is affected by a series of intermediate positive or negative values.

In the video below, you will learn and understand how to create a waterfall chart in Excel. Below the video is the process flow of the steps demonstrated in the video:

 

 

File To Download:

Kindly Download the worksheet used in this tutorial for your personal practice.

 

Waterfall Chart

 

PROCESS FLOW:

To create a Waterfall Chart of the Net Cash Flow below:

 

Waterfall Chart

 

1. Insert three (3) columns before the Net Cash Flow column, by highlighting column B, C and D. Right Click on the highlighted columns and click on Insert as shown below:

 

Waterfall Chart

 

2. Name Column B as the Base, Column C as the Fall and Column D as the Rise.

 

Waterfall Chart

 

3. Convert all negative numbers in column E to positive numbers in column C whilst all the positive values appear as zero. To do this insert the formula =If(E2<=0,-E2,0) into cell C2 and press ENTER from your keyboard.

 

Waterfall Chart

 

4. Copy the formula in cell C2 for the rest of the months, thus from February to December. As an alternative, you can also copy them down to the adjacent cells (rest of the months) using the fill handle:

 

Waterfall Chart

 

5. Next, all negative numbers in column E must appear as zero in column D, whiles we maintain positive numbers as they are in column E. To do this insert the formula =If(E2>0,E2,0) into cell D2 and press ENTER from your keyboard.

 

Waterfall Chart

 

6. Copy the formula in cell D2 for the rest of the months, thus from February to December. As an alternative, you can also copy them down to the adjacent cells (rest of the months) using the fill handle:

 

Waterfall Chart

 

7. For the Rises and Falls to be propped up to their respective heights, we will need a base to support them. To generate this base, insert the formula: =B2+D2-C3 into cell B3 as shown below:

 

Waterfall Chart

 

8. Copy the formula in cell B3 for the rest of the months, thus from February to December. As an alternative, you can also copy them down to the adjacent cells (rest of the months) using the fill handle:

 

Waterfall Chart

 

Now the data is well organized and we can go ahead with creating the waterfall chart. We now need to create a standard stacked column chart:

9. Select your data including the column and row headers, excluding the Net Cash Flow column. Thus, select range A1:D14.

10. From the Ribbon, Click on the INSERT tab and go to the Charts grouping.

11. Click on the Column dropdown and choose Stacked Column from the drop-down list.

 

Waterfall Chart

 

12. The graph appears in the worksheet with the Rises and Falls prop up to their respective height with their Base, but it hardly looks like a waterfall chart.

 

Waterfall Chart

 

13. This step is optional. You can format your graph to make the bars bolder and thicker. Go to Chart Styles from Design on your ribbon and click on the dropdown icon. Select Style 26 from your dropdown list as shown below:

 

Waterfall Chart

 

Waterfall Chart

 

The next step to take is to turn the stacked column graph into an Excel bridge chart in order to transform the column graph into a waterfall chart. To do this we need to make the base series or bars invincible:

14. Click on any of the Base series or bars to select them, right-click and choose the Format Data Series option from the context menu.

 

Waterfall Chart

 

The Format Data Series pane immediately appears to the right of your worksheet

15. Click on Fill and select No fill.

 

Waterfall Chart

 

16. Click on Border Color and select No Line.

 

Waterfall Chart

 

17. Click on Series Options and change the Gap Width from 150% to 11%.

 

Waterfall Chart

 

This closes the space between the bridge charts:

 

Waterfall Chart

 

18. You can click on the Legend at the right side of the Graph.

19. Click on the base and Delete it:

 

Waterfall Chart

 

20. You can bold the inscriptions on the Chart by clicking on an empty space of the chart and either clicking on Bold from the ribbon or use the shortcode Ctrl + B.

Give your chart a Title:

21. Go to Layout from the Chart Tools on the Ribbon and click on the Chart Title dropdown icon.

22. Select Above Chart. A text box will be displayed on your Chart. You can go ahead and edit by double clicking on the text and input your new title as shown below:

 

Waterfall Chart

 

Now you have your waterfall chart looking great! Am sure this was helpful and believe it won’t be a problem for you to manually create your own version.

If you are you new to Excel and you looking for the best excel training tutorial that will give you the right footing to prepare you for the intricate world of Excel, then look nowhere else than the Excel Fundamentals Course. This free course has been designed for people who need Excel Training for Beginners. In this course, you will learn about Excel Navigation, Data Entry, Worksheet Settings adjustment, and cell formatting, Excel Operators & Operator precedence, How to use Formulas & Functions in Excel, How to use Relative, Absolute and Mixed References in Excel and much more.

The course is free and it’s delivered online. You need to get a free account to access the course. You can register right away (it’ll only take a minute – literally)!

Watch out for our next tip on how to use a drop-down to switch between charts in Excel.

Any comments, contributions, concerns and thumbs up will be gladly appreciated.

SmithTips is a human capital organization that is aimed at being the bridge between academia and industry by working to build and develop the capacity of individuals and groups.

 Dansoman Atico, Accra, Ghana

(00) 233 260 734 574

info@mysmithtips.com

Last Tweets

Who’s Online

There are no users currently online
top
© 2016-2018 SMITHTIPS CO. LTD. | ALL RIGHTS RESERVED.
X