Blog

Adding Standard Deviation to a Dataviz

Adding Standard Deviation to a Dataviz

Do you need to communicate your standard deviations to your audience? No, I mean, really DO YOU? Think hard about that because chances are your audience doesn’t give a crap. They want to know you calculated your standard deviations. They want to know you were meticulous in your number crunching. But most likely they do not want you to actually tell them the standard deviation. Tuck that ish in an appendix or something.

But in the rare circumstance where your audience really does want to see your standard deviations, here’s a pretty easy way to visualize them in your graph inside Excel. It represents the standard deviation via a gray band behind the mean scores, using a stacked area chart. Sounds complicated, I know, but it’s actually pretty simple and these are the step-by-step instructions. In this example, we are visualizing mean reading scores for schools across the state.

Step 1, we calculate means and standard deviations for each of the 5 years we are reporting on.

StdDev

 

Step 2, add the standard deviation to the mean to calculate the upper end of the standard deviation range. And subtract the standard deviation from each mean to get the lower end of the standard deviation range.StdDev2

Step 3, the difference between the upper range and the lower range is the full standard deviation range, so calculate that by subtracting the lower range from the upper range.StdDev3

Step 4, graph. We want to create a stacked area chart, using this guy:

StdDevType

So highlight the values in the lower range and the full range, click the stacked area graph, and get this:

StdDevStacked

 

As my notes suggest, change the default colors of the stacked areas so that the lower range is no fill or white and the actual standard deviation range is gray. That’s your gray background, yo!

Step 5, add the mean values. First, right-click on the graph, click on Select Data, and add your mean values as a new series. It’ll look like this:

StdDev6

You don’t want it to look like that, but don’t freak out. Just keep going.

Step 6, click on that new area representing your means so that it is highlighted. Then change the chart type and select a line.

StdDev8

It’ll look like this:

StdDev9

Whew! That’s more like it.

Step 8, format! Add an explanatory title and a legend-like subtitle to convey what standard deviation actually means.StdDev10And there you have it. But wield this with care, okay? Standard deviation, while important to us datanerds, can elicit deer-in-the-headlight stares from many audiences.

9 thoughts on “Adding Standard Deviation to a Dataviz
  1. Nic Bookman says:

    Subscribing to the blog!

  2. Kristen says:

    Thanks for sharing, I am subscribing now!

  3. Jen Peterson says:

    Fantastic – thanks so much! Perfect timing too – we are just producing our final evaluation report.

  4. kirsty wright says:

    Elegant and clear

  5. David N says:

    Here is a link to a trend chart that shows CIs (confidence intervals) for each segment of the data. Very similar in principle to showing SD (obviously).

    https://twitter.com/Biff_Bruise/status/446633864908136448

    I did this one back in 2011… look familiar? :)

  6. Angie Ficek says:

    this is great, stephanie! i hadn’t thought of using an area graph to do this, especially for data over time. i’ve been messing around with line graphs / adding hi-low lines to show standard deviations. will try the area graph next time!

  7. Amy Elizabeth says:

    This is great – but a line chart doesn’t fit my data! Do you have a suggestion for how to add standard deviation to a bar chart using Excel? Thank you!

    • Stephanie Evergreen says:

      I think you could use a similar method if you really wanted. You’d probably need to put the standard deviation bars on a secondary axis. Try and let me know!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

RT @RadPresenters: In Episode #15 we talk PPT speed with Camille Holden from @Nuts_BoltsPPT | http://t.co/B6ZaTm1qJM

RT @albertocairo: An interview with @evergreendata Good Visualization Can Change The Conversation http://t.co/uh7rCO9AmA #dataviz http://t.…

dot plots and annotations! https://t.co/YMSaK3Kkqw

RT @fassforward: Learn what type of presenter you are. Join @powerfulpoint on his free webinar May 14. http://t.co/fjaGxemmYu http://t.co/1…

Best week workshopping with @BillDeBaun @ncan @Education_AIR @AnnKEmery @RELMidwest & @AISD_DRE if every week could be like this!

RT @marthaglassVT: @evergreendata my @VTHED students try their hand at data viz http://t.co/6qKPlXIFCl

@marthaglassVT @VTHED cool! I like the numbers at the ends of the bars!

What a fun workshop with @AISD_DRE nerding out in #Excel! Template redesigns, new graph types & more.

@AISD_DRE love my face here. priceless.

@marc_smith @KimFLeonard @AnnKEmery you might check out John Burretts work http://t.co/a9z7xP0kez