A write-in to Bill today to ask about methods to create a Waterfall Chart in Excel, without the aid of an Excel Add-in. In Episode #1430, Bill shows us how to accomplish the task. "The Learn Excel from MrExcel Podcast Series" Learn Excel 2010!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel podcast, episode 1430: waterfall cash flow charts.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and today's question is sent in by Wael and Ronga.
Wael and Ronga want to know how to create a waterfall chart.
They know that there's utilities out there that you can buy; an add-in that will create a waterfall chart, but how can you just create this in Excel?
With a waterfall chart, usually the starting and ending bars are the full value, but then from there, see we had negative 503, so red going down and then more red going down and then green going up and so on.
It is possible to do this in Excel.
I've been doing this for a long, long time.
Let’s just change the zoom here.
So, the first thing we want to do is we want to take that-- those cash flows and build a new column called balance.
This balance shows us the total after that month.
So, we start out with 5,000.
That's easy now, but then equal the previous value plus whatever the cash flow was in this month and that gets us a balance.
Now, we're going to build the red and green bars separately.
The green bars are for all of the positive numbers.
Now, you could use equal if B4 is greater than zero, B4, otherwise zero, but check this out.
I love this.
I want the MAX of B4, comma, zero.
Get it?
That is a really good way to get all of the positive numbers because if it's positive, it's going to win and the MAX will return the positive number, but if it's negative, then the zero wins and we get the zero.
Now, to get the red, I'm going to do the same thing minus the MIN of that number over there in B4, comma, zero.
This will get us just the negative numbers, but the minus sign in the beginning is going to make sure that it's a positive amount.
All right, charting series.
So, we have the red and the green done.
We have the grey done, but what we need is the invisible series.
The invisible series is the series that makes the bars appear to float and this is a little bit tough.
Okay, so what we need for the red columns, the red columns have to have the balance after this amount.
So, that white area is going to be the balance, but for the green columns, since this is going up, we need the prior balance as the white bar and then the additional money is going up to the new balance.
So, we kind of have to have a little bit of a rule over here.
We are going to have to switch over to an IF statement in order to do this.
We say =IF the amount here is less than zero, then we want-- I hate when I get to Excel help.
Don't you hate that?
If this amount is less than zero, then we want the current balance.
Otherwise, we want the prior balance.
Just copy that down.
All right and then we're going to create a chart from all of these.
So, insert, column chart, we want a stacked column chart even though in any given month we're only going to see one column.
All right, now, okay, that legend is going to go away, eventually, but right now it's going to stay because it's really helpful in helping us figure out the color of each column.
So, I'm going to select the first series here, which is called grey.
Of course, I want to change on the format tab, the shape fill to a nice dark grey.
For the green series colored green, which is called red, that's this one, we want to change that color to red.
So, shape fill, I'm going to use this nice bright red here.
For the purple series, which is called green, shape fill, use that nice bright green and then for the series called invisible, it's like one of those and shape fill, none makes it float.
All right, now at this point, we don't need that legend anymore.
Click on the legend, delete and let's make the columns wider.
In order to make the columns wider, I choose any column, Ctrl 1 and right here gap width.
There's nothing that says make the columns wider, but what we can do is make the gap narrower.
I’ll go with 26%, close.
Starting to look good.
Make the chart a little bit narrower and what else do we need?
We need a chart title.
So, chart title, centered overlay, cash flow waterfall, enter.
There we go.
All right, looks good.
So, a waterfall chart without using any add-in; just requires some extra formulas over here in order to make it work.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel podcast, episode 1430: waterfall cash flow charts.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and today's question is sent in by Wael and Ronga.
Wael and Ronga want to know how to create a waterfall chart.
They know that there's utilities out there that you can buy; an add-in that will create a waterfall chart, but how can you just create this in Excel?
With a waterfall chart, usually the starting and ending bars are the full value, but then from there, see we had negative 503, so red going down and then more red going down and then green going up and so on.
It is possible to do this in Excel.
I've been doing this for a long, long time.
Let’s just change the zoom here.
So, the first thing we want to do is we want to take that-- those cash flows and build a new column called balance.
This balance shows us the total after that month.
So, we start out with 5,000.
That's easy now, but then equal the previous value plus whatever the cash flow was in this month and that gets us a balance.
Now, we're going to build the red and green bars separately.
The green bars are for all of the positive numbers.
Now, you could use equal if B4 is greater than zero, B4, otherwise zero, but check this out.
I love this.
I want the MAX of B4, comma, zero.
Get it?
That is a really good way to get all of the positive numbers because if it's positive, it's going to win and the MAX will return the positive number, but if it's negative, then the zero wins and we get the zero.
Now, to get the red, I'm going to do the same thing minus the MIN of that number over there in B4, comma, zero.
This will get us just the negative numbers, but the minus sign in the beginning is going to make sure that it's a positive amount.
All right, charting series.
So, we have the red and the green done.
We have the grey done, but what we need is the invisible series.
The invisible series is the series that makes the bars appear to float and this is a little bit tough.
Okay, so what we need for the red columns, the red columns have to have the balance after this amount.
So, that white area is going to be the balance, but for the green columns, since this is going up, we need the prior balance as the white bar and then the additional money is going up to the new balance.
So, we kind of have to have a little bit of a rule over here.
We are going to have to switch over to an IF statement in order to do this.
We say =IF the amount here is less than zero, then we want-- I hate when I get to Excel help.
Don't you hate that?
If this amount is less than zero, then we want the current balance.
Otherwise, we want the prior balance.
Just copy that down.
All right and then we're going to create a chart from all of these.
So, insert, column chart, we want a stacked column chart even though in any given month we're only going to see one column.
All right, now, okay, that legend is going to go away, eventually, but right now it's going to stay because it's really helpful in helping us figure out the color of each column.
So, I'm going to select the first series here, which is called grey.
Of course, I want to change on the format tab, the shape fill to a nice dark grey.
For the green series colored green, which is called red, that's this one, we want to change that color to red.
So, shape fill, I'm going to use this nice bright red here.
For the purple series, which is called green, shape fill, use that nice bright green and then for the series called invisible, it's like one of those and shape fill, none makes it float.
All right, now at this point, we don't need that legend anymore.
Click on the legend, delete and let's make the columns wider.
In order to make the columns wider, I choose any column, Ctrl 1 and right here gap width.
There's nothing that says make the columns wider, but what we can do is make the gap narrower.
I’ll go with 26%, close.
Starting to look good.
Make the chart a little bit narrower and what else do we need?
We need a chart title.
So, chart title, centered overlay, cash flow waterfall, enter.
There we go.
All right, looks good.
So, a waterfall chart without using any add-in; just requires some extra formulas over here in order to make it work.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
