Mike from Arizona asks how to create a revenue bridge chart. This is similar to a waterfall chart, except some of the points are negative. Episode 1093 will show you how to create this chart.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I didn't plan this but you had another charting question this one comes from Mike in Arizona.
Mike is trying to create something called a revenue bridge chart.
Now, I recognize this as something that came out of McKinsey and fellow named Jean Zelazny there and the Mackenzie waterfall chart same concept here.
Mike has sales from last year and this year.
Last year was 10 million, this year is 12 million and wants to show how we got from the 10 million to 12 million.
So, he takes each of his five divisions here and says okay we were 10 million last year hardware is up 1.5 million so, plus fifteen hundred thousand their software though I had a really bad year. They're down twenty two hundred.
So, we go from that 11,500 level down twenty two hundred and then Service went from that level up seventeen hundred, Training is down nine hundred, and then finally Documentation is up 1900 to end up at are 12 million kind of paint you a picture of how we got from last year to this year.
Kind of shows the breakdown, and you know what the people were up or down So, when I go about building this there's actually three series in this chart.
I've done the waterfall chart before what's unusual here, that was the negatives having negatives.
So, we have the blue series which is the positives the red series which is the negatives and then we also have another series that you can't see that's the invisible series the white series down here.
So, well take a look at how I set up these formulas.
First thing I want to do is figure out the ballots. So, where are we so far.
You know, it's a simple equal B2 there, but then taking the previous balance plus this amount and that's kind of the height that's where I want that column to end.
Now, my actual chart is going to be over here.
So, I bring my labels over those are just simple formulas that grab the value from Column A, for the first and last point. It's always just a single positive value.
So, that's a formula pointing back at B2 and B8 everything in the middle though these are kind of tricky and it depends on whether the number is positive or negative.
So, what we do is say that we're going to take the previous balance.
We're gonna start at the previous balance, and if this particular value is negative, then I want to lower that base. This is the color of this is the height of the white invisible series.
Wanna lower that base by the negative amount otherwise we want to just keep it at the previous amount.
Now, if the number is positive, we bring that value over.
This is tricky though if the value is negative, like here I don't wanna bring the value over.
I wanna bring the negative value over because I need that red bar to actually be positive. And so, I've lowered the base by the amount of the negative and so here I need this to be negative, pretty tricky.
All right now, a few things if I just created this chart, the chart labels are going to show 0's here and that's not what I want.
So, I'm gonna format this series we'll do control+1 and I'll go into customer gonna use our zone so for positive numbers I want show plus zero there will be no negative numbers here.
So, I can just leave that zone blank and for zero numbers I don't want to show anything so, click OK.
Now, over here and the negative this is gonna be really tricky, again control+1 to format this will go into Custom for the positive numbers I wanna show minus zero because remember those positive numbers are actually negative in the original data sets and we're a fool Excel into showing a minus 0 and then there will be no negative and the zeros I don't want to appear.
So, look at that isn't that tricky it's storing a positive 2200, but it's showing a negative 2200.
Now, let's create the chart we're almost gome at this point Insert, Column, stacked Column chart.
There we go. We have our negative positive and base for the positive numbers. Let's go with a nice positive color there we'll format that under fill, I'll go to a solid fill, and maybe green for positive and then for our negative numbers we'll format that fill let's choose a solid fill and red for the negative numbers and then finally for the first series of the series that's supposed to be blank we'll choose that fill is gonna be no fill and then border color no line click Close.
That's gonna make everything else loads. There's all of our floaties. We can now get rid of this scale.
The labels, labels are kind of tricky here and I really could not come up with a good, good solution for this.
If we choose just the negative series, and say add data labels.
There's our data labels there and of course they pick up the value from the original chart and then we'll choose this series aad data label.
So, I think the data labels one at a time.
We get our positives. I'd really like for those to appear on top of the chart, but it's just not going to happen there was an option in 2003 to do that I got rid of it in 2007.
If I would choose those data labels, and go into format they give me an option for inside and or inside base, the inside end.
Doesn't really get me where I want to be so I'm gonna just unfortunately move these manually.
Now, once we have those labels selected I can now select a single label and just drag it to where I want it to be 1500 dragged it, missed.
1500 label drag that, 1700 label drag that, the 1900 label drag.
The negatives probably wanna have those be at the bottom.
Now, the first click selects all the labels in the series, second click selects the one single then we'll move that one down.
The 12000 missed that one.
All right so, two clicks to select it.
Now, of course the unfortunate part is if the scale changes or you know anything then we're going to have to re-do those.
Let's get rid of the grid lines. I think they don't add a lot to this chart, click Delete.
If you wanted to, you could actually draw a little drawing objects to draw you know from the top of this bar over to this bar to kind of show how things are going.
So, up 1,700, down 900, up 1,900 get to our 12,000 there you have it called a revenue bridge chart.
Also, similar to something called a waterfall chart the main trick here is to get these to float is to have an invisible series with no border, no fill.
Hey, thanks to Mike for sending that question in thanks to Jeans Zelasny out at McKenzie a great guy who has written a couple of books about the charting, not charting in Excel just how to chart in general some great theory there and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I didn't plan this but you had another charting question this one comes from Mike in Arizona.
Mike is trying to create something called a revenue bridge chart.
Now, I recognize this as something that came out of McKinsey and fellow named Jean Zelazny there and the Mackenzie waterfall chart same concept here.
Mike has sales from last year and this year.
Last year was 10 million, this year is 12 million and wants to show how we got from the 10 million to 12 million.
So, he takes each of his five divisions here and says okay we were 10 million last year hardware is up 1.5 million so, plus fifteen hundred thousand their software though I had a really bad year. They're down twenty two hundred.
So, we go from that 11,500 level down twenty two hundred and then Service went from that level up seventeen hundred, Training is down nine hundred, and then finally Documentation is up 1900 to end up at are 12 million kind of paint you a picture of how we got from last year to this year.
Kind of shows the breakdown, and you know what the people were up or down So, when I go about building this there's actually three series in this chart.
I've done the waterfall chart before what's unusual here, that was the negatives having negatives.
So, we have the blue series which is the positives the red series which is the negatives and then we also have another series that you can't see that's the invisible series the white series down here.
So, well take a look at how I set up these formulas.
First thing I want to do is figure out the ballots. So, where are we so far.
You know, it's a simple equal B2 there, but then taking the previous balance plus this amount and that's kind of the height that's where I want that column to end.
Now, my actual chart is going to be over here.
So, I bring my labels over those are just simple formulas that grab the value from Column A, for the first and last point. It's always just a single positive value.
So, that's a formula pointing back at B2 and B8 everything in the middle though these are kind of tricky and it depends on whether the number is positive or negative.
So, what we do is say that we're going to take the previous balance.
We're gonna start at the previous balance, and if this particular value is negative, then I want to lower that base. This is the color of this is the height of the white invisible series.
Wanna lower that base by the negative amount otherwise we want to just keep it at the previous amount.
Now, if the number is positive, we bring that value over.
This is tricky though if the value is negative, like here I don't wanna bring the value over.
I wanna bring the negative value over because I need that red bar to actually be positive. And so, I've lowered the base by the amount of the negative and so here I need this to be negative, pretty tricky.
All right now, a few things if I just created this chart, the chart labels are going to show 0's here and that's not what I want.
So, I'm gonna format this series we'll do control+1 and I'll go into customer gonna use our zone so for positive numbers I want show plus zero there will be no negative numbers here.
So, I can just leave that zone blank and for zero numbers I don't want to show anything so, click OK.
Now, over here and the negative this is gonna be really tricky, again control+1 to format this will go into Custom for the positive numbers I wanna show minus zero because remember those positive numbers are actually negative in the original data sets and we're a fool Excel into showing a minus 0 and then there will be no negative and the zeros I don't want to appear.
So, look at that isn't that tricky it's storing a positive 2200, but it's showing a negative 2200.
Now, let's create the chart we're almost gome at this point Insert, Column, stacked Column chart.
There we go. We have our negative positive and base for the positive numbers. Let's go with a nice positive color there we'll format that under fill, I'll go to a solid fill, and maybe green for positive and then for our negative numbers we'll format that fill let's choose a solid fill and red for the negative numbers and then finally for the first series of the series that's supposed to be blank we'll choose that fill is gonna be no fill and then border color no line click Close.
That's gonna make everything else loads. There's all of our floaties. We can now get rid of this scale.
The labels, labels are kind of tricky here and I really could not come up with a good, good solution for this.
If we choose just the negative series, and say add data labels.
There's our data labels there and of course they pick up the value from the original chart and then we'll choose this series aad data label.
So, I think the data labels one at a time.
We get our positives. I'd really like for those to appear on top of the chart, but it's just not going to happen there was an option in 2003 to do that I got rid of it in 2007.
If I would choose those data labels, and go into format they give me an option for inside and or inside base, the inside end.
Doesn't really get me where I want to be so I'm gonna just unfortunately move these manually.
Now, once we have those labels selected I can now select a single label and just drag it to where I want it to be 1500 dragged it, missed.
1500 label drag that, 1700 label drag that, the 1900 label drag.
The negatives probably wanna have those be at the bottom.
Now, the first click selects all the labels in the series, second click selects the one single then we'll move that one down.
The 12000 missed that one.
All right so, two clicks to select it.
Now, of course the unfortunate part is if the scale changes or you know anything then we're going to have to re-do those.
Let's get rid of the grid lines. I think they don't add a lot to this chart, click Delete.
If you wanted to, you could actually draw a little drawing objects to draw you know from the top of this bar over to this bar to kind of show how things are going.
So, up 1,700, down 900, up 1,900 get to our 12,000 there you have it called a revenue bridge chart.
Also, similar to something called a waterfall chart the main trick here is to get these to float is to have an invisible series with no border, no fill.
Hey, thanks to Mike for sending that question in thanks to Jeans Zelasny out at McKenzie a great guy who has written a couple of books about the charting, not charting in Excel just how to chart in general some great theory there and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast MrExcel.
