I want to make a chart that looks like this:

This example comes from Powerpoint. There’s another chart right next to it showing percent of revenue. I had to edit the chart in Powerpoint to obfuscate the data. That was a lesson in itself. I right clicked on the chart and chose Edit from the Chart Object menu. I had to manually change all of the numbers. I have no idea if you can use formulas in that PPT grid, but I didn’t see how. Then when I got back to the slide, none of the labels were right – they’re all hand typed numbers. And those lines that seemingly connect the bars? They’re hand placed individual shapes. That was a process. And not one I’d like to repeat. Oh, one more thing. Where facilities crosses the vertical axis, the axis is hidden by three shapes; One blue rectangle to fill over the axis and two small black lines to serve as horizontal borders. Brutal.

I started with this data

and end up with these charts

Here’s how I got there. First, I read Peltier’s post on the subject. Then I read Tushar’s page on the subject. Both are for columns, not bars, but very valuable information. I think we all know that I didn’t really read those “first”. Rather, I tried to do it myself, got stuck, then went and read them. But you get the idea.

The key to making a proper chart is laying out the data properly, so let’s start there.

Column D: I manually identify which numbers are my end numbers. In D3, the formula is =C3 and it’s similar all the way down. Where this is no number, the cell is blank.

Column E: `=IF(ISBLANK(D3),MAX(0,MIN(SUBTOTAL(9,C2:C$3),SUBTOTAL(9,C$3:C3)))+MIN(0,MAX(SUBTOTAL(9,C2:C$3),SUBTOTAL(9,C$3:C3))),0)`

If it’s an end (D3 isn’t blank), the result is zero. Otherwise it computes where the visible data series should start. Column C uses the SUBTOTAL function to calculate the end points, so this cell uses them to properly ignore the end points. Other than the conditional and the use of SUBTOTAL, this came straight from Jon’s example (as did most of the formulas).

Column F: `=IF(ISBLANK(D3),MAX(0,MIN(SUBTOTAL(9,C$3:C3),C3)),)`

Column G: `=IF(ISBLANK(D3),-MAX(0,C3-F3),0)`

Column H: `=IF(ISBLANK(D3),MAX(0,I3-C3),0)`

Column I: `=IF(ISBLANK(D3),MIN(0,MAX(SUBTOTAL(9,C$3:C3),C3)),0)`

These four formulas split the data into rising and falling, positive and negative. It reminds me of astronomy class and waxing gibous and waning crescent. I’ll hold off on columns J:L until later. If I create a stacked bar chart from just those columns, I get

And we’re done. Just kidding. That’s hideous. I’m sure there are better ways to start, but for me, I select the data and press Alt+I+H to create a chart. In this case, it means I have to delete the Actual data series. In addition to that, I do my normal chart formatting stuff.

- Border and Area of Plot Area set to None
- Legend removed
- Major gridlines color set to 25% grey

And some charting stuff that is particular to this chart

- Reverse order of Y axis
- Remove Y axis tick marks
- Set Y axis labels to Low
- Set Gap Width to 50
- Hide X axis

You can see that the work is pretty much done. It’s all about the data layout. Next, color the Blank data series as invisible, the End data series mustard and everything else Carolina Blue. There are four series that get the Carolina treatment and they may not all be showing. I don’t have any “Up Pos” datapoints visible – they’re all zero. But you still have to color them or you’ll be in for a shock when your data changes. Also, remove the borders.

Now I need to get some data labels on there. For this I needed three columns corresponding to the x coordinate, the y coordinate, and the label. You may have noticed that I used an ugly yellow color for the End data series. That’s because I wasn’t using a very good formula for placement of the labels and some of the labels were overlapping with the bars. When the bars were dark blue, it was no good. I fixed the formulas, but I must have grown fond of the yellow. So that’s the story behind that.

X Coordinate: `=IF(COUNTIF(D3:I3,">=0")=COUNT(D3:I3),SUM(D3:I3),IF(COUNTIF(D3:I3,"<=0")=COUNT(D3:I3),E3,MAX(D3:I3)))`

Y Coordinate: `=(ROWS($B$3:$B$14)-ROW()+ROW($B$3:$B$14)-0.5)/ROWS($B$3:$B$14)`

The Label3 column (used for the actual text of the label) just repeats column C, but is formatted the way I want. The Y Coordinate formula came from PTS Dot Plot. It simply returns a point on the Y axis that lines up with the bars. For the X Coordinates, I wanted the label to be just to right of the bar. If everything in that row (D3:I3, for example) is positive, add them up. If everything is negative, put it where the Blank data series stops. If there's a combination of positive and negative numbers, only consider the positive.

To create the labels, I right clicked on the chart and chose Source Data. Then on the Series tab, I added a new series called Label with column K as its Values.

I changed that new data series into an XY chart and edited the Source Data again. I set the X Values to column J and left the Y Values at column K. Next I formatted the secondary Y axis to have a Minimum of zero and a Maximum of 1.

Finally, I used Rob Bovey's XY Chart Labeler Utility to put 'Right' labels from Column L on those points.

The finishing touches included hiding the secondary axes, hiding the Label data series bullets, making the primary Y axis 40% grey, adding a chart title, and adding a primary y axis title.

Then I did it all again for the percentage chart. The bar that crosses the Y axis doesn't cover up the axis, but I can live with that. Also, there are no lines connecting the bars, but I don't see the need for them. I could make another series to replicate it, but does it add anything? I don't think so.

You can download PLChart.xls

Nice tutorial. You can simplify the adding of the labels with the following approach. There’s no need to add a XY Scatter series located at the tip of each bar. The solution is to (1) if the blank values are negative, add the data labels to that series, (2) for positive blank values add a new series of all zeros and add the data labels to this new series. For both set of data labels, position them to ‘Inside Base’.

So, add another series (in your example, column J): =IF(E3<0,NA(),0)

Add 2 columns for the 'conditional' labels:

In column K: =IF(E3<0,ROUND(C3/1000,0),"")

In column L: =IF(K3="",ROUND(C3/1000,0),"")

Now, use col. K as the labels for the Blank series and col. L as the labels for the col. J series. Format the data labels for each of the series to be 'Inside Base'.

Thanks Tushar. Adding the labels is the kludgiest part, so I appreciate the shortcut. As I was doing the XY thing, I guessed that there was a simpler way, but it eluded me.

Sneak preview of a new version of my Waterfall Chart program. I don’t think it will come out until shortly before the next version of Office is released, though it will work in Excel 2007 onwards.

In case the image tag above isn’t accepted, here’s the link to the image file:

http://peltiertech.com/images/2012-02/PL-HorizWF.png

Nice. Let me know when you want me to beta test it.

On an unrelated note, the peltiertech.com servers have been really slow lately – like DDoE slow. Did you change hosts?

Nice tutorial, but it takes time to have it all set up.

Using “Sparklines for Excel” add-in, you just need 3 formulas using overlapping ranges.

see here for a screenshot : http://www.box.com/s/ajr1mhty8s88id02xt38

the xls file : http://www.box.com/s/10hqi27rcuvgxdx50lfs (you will need SfE installed to have it working properly)

Shouldn’t EBITDA and Net inc. be in red as those are negative values ?

kindly keep mailing me all this types of charts and other advanced files on excel like Advanced uses of ARRAY formulas.

This is a great visual way to set up the income statement. Thanks!

Hi!

I’m trying to add a new P&L concept, but I can’t seem to get it to work. One of the Labels always displays the last Value of Label2 @ ColumnK.

I will appreciate the insights