How to Create Waterfall Chart in Excel
Creating waterfall chart in Excel is easy. It requires a few extra steps for preparing your data before plotting. Unfortunately, earlier versions of Excel do not provide a direct option to create such a chart. But never mind, it’s still no rocket science! We will teach you how to go about it. In this tutorial, we will use a sample Excel datasheet to demonstrate various steps of creating a waterfall chart. The sample datasheet contains revenue data of a firm for a particular year. The firm starts with a certain amount of money in its coffers and then there are profit or loss figures for each month. You can download this waterfall chart data sheet. If you were to make a waterfall chart from such data, you’ll probably have data as shown below. It simply contains month name and profit or loss during that month. The Start and End points show the amount of money you had at the beginning and at the end of the year.
Now, before beginning the tutorial, let us show you the finished waterfall chart that we will make from this data.
This is called waterfall chart. These charts were popularized by McKinsey & Company. In these charts each step begins from the level of the previous step. Steps can go up or down depending upon the value that step. Overall, the chart gives an impression of a stepped path that falls and rises from time to time. Now, let’s learn how to create this type of charts. Although Excel 2016 has introduced basic waterfall charts but, in order to make a chart as shows above, you will need to make a few additions in your data and then modify a stacked column chart for achieving the above shown results.
Step 1: Add columns
First of all, you should add the following columns in your datasheet. These columns are necessary for the creation of waterfall chart: Base column will contain the total amount of money you have after adjusting previous month’s profit or loss. The base figure will act as the base for creating the next month’s chart bar. End column will contain only one figure which will show the amount of money you have at the end of the year. Increase and Decrease columns will have a number or zero depending upon whether profit or loss was incurred during that month. Start column will also have single figure showing the amount of money you have at the beginning of the year.
Step 2: Use formulas to calculate required data
Now you would need to fill values in these new columns. You can easily populate these columns using formulas. Formula in F2 =IF(G2>0,G2,0)
This formula inquires how much money you have to start with.
Formula in E3 =IF(G3<=0,-G3,0)
This formula simply see the value in Profit/Loss column and if it is a loss, it adds the same value in E3 and if it is profit the formula adds 0 in the Decrease column. Auto fill this formula from E4 to E14.
Formula in D3 =IF(G3>0,G3,0)
This formula enters the profit in the Increase column if there is profit; otherwise it enters 0. Auto fill this formula from D4 to D14.
Formula in C3 =(B2+F2)-E3
This formula calculates the base amount by taking in account the profit/loss at the end of the month. Auto fill this formula from C4 to C14.
Formula in C15 =B14+D14+E14
This formula calculates the overall net amount in your hand at the end of the year.
That’s all! your Excel datasheet is ready to be converted into a waterfall chart! Let’s have a look at how the datasheet should appear now:
Step 3: Create a Stacked Column Chart
A simple stacked chart will be inserted in your datasheet. In a stacked chart, each bar contains more than one pieces depicting the value of data. These pieces are stacked on top of each other. In our chart the value of base column comes first and on top of that comes the increase or decrease of that particular month. Here is the stacked column chart:
Now, essentially, we need to format this chart to create a waterfall chart. In the following section we will describe what modifications are required and how to do them.
Step 4: Modify the stacked column chart to turn it into a waterfall chart
First of all we will make need to make the base portion of bars disappear. As you can see, if we could remove the blue color base portion of each column, then what would be left with is essentially the waterfall chart!
Take right click on any of the blue bars to select the whole blue series Select Format Data Series… from the pop up menu
You will get the Format Data Series dialog box. Here you can change the fill color, border color, border style etc. for the data series you have selected. Make the following changes in this dialog box:
Fill: set it to No fill Border Color: set it to No line Series Options: set Gap Width to somewhere around 20% (this will make the column bars wider by reducing the gap between them
After making these changes, click on Close button to close the dialog box. You should get waterfall chart as shown below. It is noteworthy that the blue base portion has not been removed altogether. We have just made it invisible by removing the fill color and line color.
You work is done! Only a few minor adjustments to be made to make this waterfall chart look really professional.
It would be more meaningful if Start and End columns are of the same color. Take right click on the red colored End column and select Format Data Series. Then under the Fill option, select the Solid fill and fill any color you want in the End column. Similarly, you may also change the color of Increase and Decrease chart columns if you don’t like the default color. Now that we have made the Base portion invisible, there is no point in showing Base in the legends. Double click on Base legend and select Delete to remove Base from legends. If you want, you can similarly remove Start and End also from the legends list. You may also want to move all the legends from right hand side to the bottom of the chart. This will give more space for chart columns to widen. For this take right click on legends and select Format Legend… option. A dialog box will appear. From Legend Options, select Bottom as the legend position. You can add values in the Increase and Decrease chart columns to make the chart more readable. Take right click on any of the Increase chart column and select Add Data Labels. Same way you can add values for the Decrease column.
That’s all! your waterfall chart is ready!
Simple Waterfall Charts
The above method can make a data series visualized as waterfall chart. But waterfall charts can also be made with simpler data. And of course such charts are much simpler to make too. Excel 2016 has also introduced option of creating simple waterfall charts. Let’s see an example taken from Wikipedia. Following are figures of a company’s revenue and expenses:
Product revenue: $420K Services revenue: $210K Fixed costs: $-170K Variable costs: $-140K Total: $320K
This data can be plotted in the form of a waterfall chart as shown below:
Microsoft Office 2016 has included an option for creating such simple waterfall charts. Click Insert > Insert Waterfall or Stock chart > Waterfall.
We hope that this article on how to create waterfall charts in Microsoft Excel was useful for you. Should you have any questions related with this topic, please feel free to ask in the comments section. We will try our best to assist you. Thank you for using TechWelkin!
