Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
Go behind the scenes with some of the biggest digital celebrities to see what life is like when the blogging and tweeting stops.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
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.
Tags:How to make an Excel Revenue Bridge Chart,bridge charts,excel charts,Excel Revenue Bridge Chart,excel spreadsheets,Learn Excel from MrExcel,charting,excel,spreadsheets
Grab video code:
Hey welcome back to the MrExcel I am Bill Jelen and what is planned is another charting question, this one comes from Mike in Arizona. Mike is trying to create something called a revenue bridge chart, and now I recognize this as a something that came out of Mackenzie and filler name Jean—and the Mackenzie waterfall chart, same concept here. Mike has sales from last year and this year, last year was 10 million and this year is 12 million and wants to show how we got from the 10 million to 12 million. So it takes each of us five divisions here, it says we are 10 million last year hardware is up 1.5 million plus 1500 thousand there.
Software though had really bad year there, they are down 2200 so go from that 11,500 level down 2200. And then service went from that level up 1700, trending is down 900 and finally documentation is up 1900 and end up 12 million. Kind to paint a picture of how we got from last year to this year, it kind to shows the breakdown and the people are up or down.
So we go about building this, there actually three series in this chart, I've done the waterfall chart before but what is unusual here though are the negatives. Having negative 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 we’ll take a look at how I set up these formulas.
First thing I want to do is figure out the balance, so where are we so fa, 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 your simple formulas that grab the value from the column A. For the first and last point is always as just a single positive values, so that is 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 we’re going to take the previous balance.
We’re going to start at the previous balance and if this particular value is negative, then I wanted to lower that base. This is the color of – this is the height of the white invisible series. I want to lower that base by the negative amount, otherwise we want just to 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 want to bring the value- I want to 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.
Alright a few things, if I just created this chart-the chart levels are going to show zero here and that’s not what I want. So I am going to format this series, we’ll do control one and I’ll go into custom, we’re going to use our zones. So positive number I want to show plus zero. There will be no negative numbers here so I can just leave that zone blank and for zero numbers like I don’t want to show anything so okay. Now over here in the negative this will be tricky again control one format this we’ll go into custom, for the positive numbers I want to show minus zero remember those positive numbers are actually negative in the original data since I want to fool excel-and I'm showing minus zero and then they’re will be no negative and these zeros I don’t want to appear. So look at that, isn’t that tricky? Its storing a positive 2200 but it’s showing a negative 2200 now.
Let’s create the chart we are almost home at this point, insert ,column, stack column chart and here we our negative, positive and base. For the positive numbers, let’s go with a nice positive color there, we’ll format that under fill a solid fill and – positive and then for our negative numbers we’ll format that fill and lets choose a solid fill. And red for the negative numbers and then finally for the four series, the series is supposed to be blank. I'm going to choose that fill it’s going to be no fill and then for the color no line. Click close that is going to make everything else float, we can now get rid of the scale.
The labels, labels are kind of tricky and I really could not come up with a good solution for this. If we choose just the negative series and say add data label, there’s our data that was there so pick up the value from the original chart an choose this series add data labels. So we are data labels one at a time, again our positive – I really like for those to appear on top of the chart, it’s just not going to happen. There was an option in 2003 to do that they have rid it 2007, if I would choose those data labels and go in to format they give me an option for inside and or inside base inside end. It doesn’t really get me where I want to be, so I'm going to and just unfortunately move this manually.
Once we have those labels selected I can now select a single label and just drag to where I wanted to be. 1500 drag -1700 label drag that, the 1900m label drag the negatives probably want to have those read at the bottom. Now the first clicks, selects all the labels in the series, second clicks selects the ones singled and moved down. The 12,000 I miss that one so two clicks. Select it and just drag it up and now of course the unfortunate part if the scale changes or anything then we’re going to have to redo those.
Let’s give it a grid line, I think they don’t have a lot of this chart, if you wanted to you can actually try a little drawing objects to draw on the top of this bar over this bar kind of show. I think – 1700 down 900 up,1900 into 12,000 now there you have it called a revenue bridge chart. Also similar to this something called a waterfall chart, the main trick here is to get this to float as to happen invisible series with no border, no fill.
Hey thanks for sending that question in, thanks to Jean—out of Mackenzie, great guy who is written a couple of books about charting. Not charting in excel just a chart in general so great theory there and thanks to you for stopping by, see you next time for another netcast from MrExcel.