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
