Increase, No Change, and Decrease Graph

Question

I am attempting to make a template of a graph my company will use for reporting on data regarding risk scores in health assessments.  The graph would need to depict # of individuals who increased in risk score, no change, or decreased in risk score for 6 different health categories.  The format I've used is a bar graph however my issue is for the no change group this adds into the positive change group.  Ideally I would like for the no change group to just have a call out box with the number of individuals who showed no change while only the positive and negative scores reflecting above(increase in score) or below (decrease in score) the X-axis.  Is this possible?  The work around I've done is to just simply color the no change and positive bars in the same color and make the call out box pointing to the axis, however the bar for the positive groups looks higher than it should as it is combining the positive and no change bars.

Secondly, I would like the total number of participants in each category to be show below the Risk category labels.  This chart has been created with text boxes over-lay onto the graphs, but for streamlining the process and efficiency if there is a way that it would update with updated pivot tables that would be amazing.

I will attach a picture to hopefully make sense of this.

I hope this makes sense, thank you.

Answer 1

So would this require customization for each data set?  As the -40 to 60 wouldn't work on the client I'm preparing the current report for.  That was actually a completed old version of the report I completed.  I will need to make this for each division of the current client I'm working on meaning I would need to format this chart separately for ALL, and then roughly 8 different divisions.

Answer 2

OK

The trick here is separating the 'No change' from the Increase onto two separate Y axes so that you can achieve the gap in the middle. It's possible to create this from a pivot table but rather more fiddly - lets deal with the basics first.

Arrange your data as shown below. Leave B1 blank.

Select B1:E7, insert 2D stacked column chart.

Right click on 'Increase' data series, Format data series and plot series on Secondary axis

Format the primary Y axis with bounds -40 min to 60 max. Set the number format to Custom, format code ;-#,##0;0. Set 'Horizontal axis crosses' to Axis value -40

Format the secondary Y axis with bounds -50 min to 50 max (Note: to achieve the 'gap' here the two axis bounds need to cover the same range (100 in this case) between min and max, but be offset by however wide you want the gap to be (10 in this case)). Set the number format to Custom, format code #,##0;

Click on the 'Decrease' data series. In the formula bar it should look like =SERIES(Sheet1!$D$1,Sheet1**!$B$2:$B$7**,Sheet1!$D$2:$D$7,2). Change the X values series to include column A, so that it reads =SERIES(Sheet1!$D$1,Sheet1!$A$2:$B$7,Sheet1!$D$2:$D$7,2)

If you want to add the data labels to the bars, then for each series select the chart tools Design tab, Add chart Element, Data labels, Centre. You might want to download Rob Bovey's XY Chart Labeller to get some more control over the labels.

Also have a look at Jon Peltier's discussion about making charts from pivot data to get some insight into that issue (http://peltiertech.com/regular-charts-from-pivot-tables/).

Regards

Murray

Answer 3

I could certainly play around with that, it may work.  Please send the steps. Thank you!

Answer 4

Hi

Would something like this meet your needs? This is possible 'out of the box' in Excel - you can also ad the data labels if required.

Respond back if yes and I will outline the steps.

regards

Murray

Answer 5

You might want to try a 3D histogram chart of number of people

in each % change in risk score vs each health category.

http://www.mediafire.com/download/g3892753wqa3uxe/08_02_16.xlsx

http://www.mediafire.com/download/4556orv5532zq18/08_02_16.pdf