Case Study Solution – Mom Needing Help on Science Fair Graphs/Charts

Yesterday I presented a case study on assisting a mom in creating a chart for a science fair.

You can read more about it here:

Case Study – Mom Needing Help on Science Fair Graphs/Charts

So here is what I did.

First we need to see what the data should look like in Excel.  I have deduced that the data that the mom has posted in the forum would have looked like this in a spreadsheet:

Excel 2010





ABCDEFGH
1Subject A
2Wii SportsReal Sports
3TennisPulse BeforePulse AfterSteps TakenPulse BeforePulse AfterSteps Taken
4Trial 1808810801201119
5Trial 2808411881281242
6Trial 3848810841241181
7Bowling
8Trial 1848858488246
9Trial 2808468088263
10Trial 3808078288255
11Baseball
12Trial 180841996112887
13Trial 28088208096961
14Trial 384882088104924
15
16Subject B
17Wii SportsReal Sports
18TennisPulse BeforePulse AfterSteps TakenPulse BeforePulse AfterSteps Taken
19Trial 164961468721028
20Trial 268964668882316
21Trial 3721005064881678
22Bowling
23Trial 176120266064199
24Trial 268100376284350
25Trial 372128296688225
26Baseball
27Trial 176962264104825
28Trial 27211226641081058
29Trial 3721241768112987

Sheet1

Looking at the data, it appears that there are several groupings or comparisons that need to be made.

We need to group the Data for the 3 Trials (Wii Sports vs Real Sports) for each sport (Tennis, Bowling and Baseball) for each subject.

This sounds like a perfect solution for an Excel Multi-Level Category Labels.

What is that you ask?



It is just the best, easiest way ever in Excel to create groupings in Excel Charts and Graphs.

To do this, we first need to re-arrange the data.  You may need to do this as well with the data you have for your Company Dashboard Charts.

So this is how we need to rearrange the same data:
Excel 2010

ABCDEFG
1Pulse BeforePulseAfterSteps Taken
2Subject ATennisTrial 1Wii Sports808810
3Real Sports801201119
4Trial 2Wii Sports808411
5Real Sports881281242
6Trial 3Wii Sports848810
7Real Sports841241181
8BowlingTrial 1Wii Sports84885
9Real Sports8488246
10Trial 2Wii Sports80846
11Real Sports8088263
12Trial 3Wii Sports80807
13Real Sports8288255
14BaseballTrial 1Wii Sports808419
15Real Sports96112887
16Trial 2Wii Sports808820
17Real Sports8096961
18Trial 3Wii Sports848820
19Real Sports88104924
20Subject BTennisTrial 1Wii Sports649614
21Real Sports68721028
22Trial 2Wii Sports689646
23Real Sports68882316
24Trial 3Wii Sports7210050
25Real Sports64881678
26BowlingTrial 1Wii Sports7612026
27Real Sports6064199
28Trial 2Wii Sports6810037
29Real Sports6284350
30Trial 3Wii Sports7212829
31Real Sports6688225
32BaseballTrial 1Wii Sports769622
33Real Sports64104825
34Trial 2Wii Sports7211226
35Real Sports641081058
36Trial 3Wii Sports7212417
37Real Sports68112987

Sheet3

Now that we have the data rearranged, we just need to chart it.





Simply highlight the range and create a 2-D Clustered Column Chart.  It will look like this:image

Check out how the Horizontal Axis groups the data.  Lets look closer just at the data from Subject A:image

If you right click on the Horizontal Axis and then click on Format Axis… from the pop-up menu.  You will then see the Axis Options dialog box:SNAGHTMLcce7033

Notice that Multi-level Category Labels is checked.  If you uncheck the Multi-level Category Labels check box from the Axis Options in the Format Axis dialog box, you chart will be changed to this:image

Now it is back to a normal column chart in that the Horizontal Category Axis Labels are only showing the first column next to your data.  Not cool.



Here is a simpler chart using Multi-level Category Labels:

Excel 2010

ABC
1BirdsCanaries6
2Parrots7
3AmphibiansFrogs5
4Turtles8
5MammalsDogs4
6Cats6

Sheet2

image.png

So how it makes things so much easier to read?





Try it for yourself.

Back to our story, what did Mom think of my solution?

Here is what MomNeedingHelp wrote back:

Re: Help with Science Fair Graphs/Charts

OMG!!! I am in awe! You did it FOR me??? Silly me, I am so emotional over it. I have sat in front of the pc, searching and searching and searching. I actually found your website today, from here, and was going to try and copy some of the things on your website.
I just can’t express enough appreciation. I have been an at home mom for 15 years, now going back to middle school for the 3rd time with my kids, 4th time for me in total.
Thank you, thank you, thank you.
Now, how do I learn to do stuff like that so I can teach my kids?
You are the best!!!!
Did I say Thank you? Thank you!!!!

 Video Tutorial: http://youtu.be/2CxyyPvegjk

HOW COOL – But I wonder if I won the science fair?  Well, I didn’t do the test, I just helped the kid make a really cool chart out of his data.  Check it all out in this video tutorial:



Do you think you can use this technique in your next Executive Company Dashboard?

Let me know in the comments.

Steve=True





5 COMMENTS

  1. Hi, I would like to know whether the Multi-Level Category Labels are available for the Mac 2011 version of Excel.
    I’ve been trying to do something similar to the second option you showed in this video: http://www.youtube.com/watch?v=FuSI0XDJynY

    But I can’t get the Multi-level category option to show up; it just reverts provides the regular max/min/interval axis options.

    Do you know whether it’s possible to get Multi-Level category labels on Excel for Mac? How would I do that/How is it different?

    Thank you so much for your tutorials! They are so helpful.

  2. This site has been really helpful so far! I have a follow up question. Could I create a secondary axis for one of the horizontal subcategories? For example, from the case study above, can I put the “Steps Taken” category on the secondary axis. The Steps Taken values are on average much higher than the “Pulse” ones so the Pulse information isn’t that well represented on the vertical axis. In other words, the Steps Taken bar is huge compared to the Pulse ones so Pulse becomes less meaningful when it’s so small to see in the graph. Is there a way to put “Steps Taken” on the secondary axis and Pulse info on the Primary Axis so the Pulse units could be more granular?

    My issue: I have clustered two stacked columns for each month in the year – one column is dollar value of purchases and the other is the number of purchases. Stacking is for the purchase type (service vs product). The each purchase is very high dollar value while there aren’t that many total purchases in a given month so the dollar value bar is much bigger than the number bar. In fact the number bar barely shows up on the chart. Is there anyway to put dollar value on the primary axis and number of purchases on the secondary axis so the info can be displayed better?

LEAVE A REPLY

Please enter your comment!
Please enter your name here