Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
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.
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
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.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
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.”
They say every picture tells a story and AOL On's new original series My Ink proves it. Travel along as some of the world's greatest athletes bring their tattoos to life through exclusive interviews and visits to their favorite tattoo parlors.
Discover crowdfunded small business success stories with author, comedian, and entrepreneur Baratunde Thurston.
Go behind-the-scenes with racing's hottest, young talent, 17-year-old Dylan Kwasniewski, as he aspires to make it in the #1 motorsport in America – NASCAR
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.
Iconic potter, designer, author and personality Jonathan Adler shares his unique perspective on creativity. Showcasing the inspiration Jonathan finds in the most unlikely people and places, Inspiration Point will add style, craft and joy to your life.
Serving Innovation gives a fresh look into the stories and passions that motivate some of the most innovative tastemakers in America.
A documentary directed by Alex Winter exploring the Napster downloading revolution; the kids who created it, the bands and businesses that were affected and its impact on the world at large.
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.
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.