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.
To create a Waterfall Chart of the Net Cash Flow below:
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:
2. Name Column B as the Base, Column C as the Fall and Column D as the Rise.
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.
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:
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.
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:
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:
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:
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.
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.
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:
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.
The Format Data Series pane immediately appears to the right of your worksheet
15. Click on Fill and select No fill.
16. Click on Border Color and select No Line.
17. Click on Series Options and change the Gap Width from 150% to 11%.
This closes the space between the bridge charts:
18. You can click on the Legend at the right side of the Graph.
19. Click on the base and Delete it:
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:
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.