Backwards Bar Charts

Recently, someone shared a visualization from Periscopic about the Trump Emoto-coaster. While the subject matter itself was not of particular interest to me, I did like the presentation of it.

Strap yourselves in. Your hands must be this small to ride this ride.

The line chart at the top made me think about the rises and falls within a school year. March seems like an especially cruel month, with teachers' tempers growing short. (Just ask me about how I ended up in a conversation with a five-year old about why we need to wear pants at school.) How do attendance and discipline intertwine? And, when I looked at the horizontal bar cum sparkline plots shown above, it also made me wonder what we would see if we plotted individual classrooms over time. Maybe something like this:

Let's say there are four teachers at a particular grade level in a school. If we looked at the number of student absences and office referrals from the beginning of the year to the end of the year...what might we see?

If I was a principal, I might use something like this to either look for "hot spots" in my school that I might not know about...or monitor how well my school improvement initiatives are being implemented at the classroom level...or even to show staff for input. If I was a teacher, this might give me a general way to compare outcomes in my classroom. It might also piss me off (This just shows you that I have ALL of the bad kids!).

My challenge was how to build this. At its most basic level, this is a floating bar chart. And Ann Emery has a great tutorial for doing just that in Excel. But I didn't take that particular route this time because of how I need these charts to lay out. You see, absences for any given classroom total no more than 70 in a month...but referrals are no more than 13. Excel isn't going to let me push the edge of the chart off the lefthand side of the worksheet if I keep the x-axis the same on both sides, meaning I ended up with a ton of blank space. I suppose I could put attendance on the left and discipline on the right, but hey, what's Excel without some challenges?

So, how do you build a backwards bar chart?

Create your horizontal bar chart the usual way, then fuss a little bit with the axis settings.

Once you do this, then remove the gridlines and axes themselves, you'll be able to position this bar smackdab against the other one. You know it's worth it...you can work it. Just put that chart down, flip it, and reverse it.

Holla!

Another to know about this chart is the addition of the line down the middle. Since I deleted the gridlines and axes, I need some sort of visual between the bars. So, a simple line shape in grey 1.5 pt is all that was added.

In terms of labels, I'm going to leave them off. If you understand how one is laid out, then you can understand a whole school's worth. The numbers themselves aren't the big idea with this visual. It's the patterns and comparisons we're after. When we've identified those, we're ready to ask some deeper questions and dig into the numbers in a different way. These charts are the starting point for conversations...not the end...even if that seems a little backwards.

I've Got (Graphs for) the Power (Law)

In our last post, which introduced the Power Law, things were getting kinda hectic:




We'd looked at a whole bunch of stats, talked about our friend from high school algebra (formula for slope), and started to repurpose the original gradebook I posted. It was like old home week around here. But we're not stopping there. Nope. It's time to develop graphs for this data. So, let's take a look at the reporting tool in the workbook. I've made a couple of changes.

First of all, because the Power Law focuses attention on the most recent score, the way we look at student progress is going to look different. In fact, if you click on the "Scores" part of the workbook, you'll notice that the columns that used to contain the median scores for a grading period have been removed. In the reporting tool, I've added two items and altered a heading to accommodate the changes. I selected the colour blue to represent "student progress," which is a way to describe the scores determined by applying the Power Law. In other words, black shows the assigned score, and blue the adjusted score. I also replaced the bar chart showing process with the trendline representing the Power Law data. Finally, I changed the head of E10 to be "Learning Trend" instead of "Growth from 1st Quarter."

I'm not going to go through how to create the data validation list for the last name or the whole graph build. If you need a refresher on those items, please (re)visit the Beginner's series Part IIa (data validation, INDEX/MATCH) and Part IIb (graphs). I'm just going to show you the new stuff.

Here's the first row (Row 11) for Steve Canyon's performance. In cell C11, I've used the INDEX/MATCH function to report the most recent score (not an average or median) from the report for this standard. Our "All Scores" graph looks the same as in the original Beginner's Gradebook, but in cell E11, we can see the results of the Power Law plotted out for comparison with a Steve's scores. As you can see, this gives us a different view of student performance. His actual scores jump around, but the overall trend is one of moving toward the standard.

Here are a few other examples for your consideration. In these cases, I've plotted both the student's actual scores (in black) and the predicted scores using the Power Law (in blue).

Gonna Fly Now...

Got Viagra?

Patient flatlining. Clear!

The Power Law can provide a different sort of summary of performance vs. simple measures of central tendency, and can generate different kinds of discussions with stakeholders. Also notice that the learning curve doesn't always head in a positive direction. In the middle example, there's a student who has met standard, but never improved on the performance. And in the bottom graph, you can see that inconsistent performance leads to no curve at all.

Some commercial gradebooks have a Power Law option. It's certainly a lot simpler to have a built-in formula at your fingertips. However, I don't want you to think that you have to spend a lot of money to get this sort of information. And, I firmly believe that you should be in control of your data. You're the expert about your students. You can download the final version of the Power Law Gradebook, which has all of the report completed to use as a model for your own data sets.

Happy dancing!




Music clips from The Power by Snap! (c)1990.

Using Add-Ins: Sparklines

In the last post, Jennifer mentioned one of her favourite Excel add-ins: ASAP Utilities. If you don't know what an "add-in" is, it's a little program that works inside Excel. (There are add-ins for other Microsoft products, too.) Some are fee-based and others are free. When you find an add-in that you want, download and place the file(s) in a location you associate with Excel files. You can place the add-in files anywhere, but once you've told Excel to use them, it doesn't tolerate the add-in being moved around.

You will need to tell Excel to use the Add-in. In the Options menu, select "Add-ins":

You'll see a list of ones that can be accessed and used. If you don't see the one you downloaded, hit the "Go" button at the bottom of the window. This will bring up another dialog where you will be able to browse for the add-in and then use the checkboxes to tell Excel to use it.

And now, I'd like to introduce my favourite add-in for Excel: Sparklines for Excel. (It's free!) A sparkline is a "data-intense, design-simple, word-sized graphic," according to Edward Tufte, one of the godfathers of modern data visualization. The idea is that you don't always need a full-size chart or graph to illustrate a point. Small and simple is powerful.

Excel 2010 includes 3 sparkline options, but they don't hold a candle to what the freeware add-in can do. You have the-sky's-the-limit options in terms of using colours and marks. Here's a quick overview of the types of visualizations you can do with your data:

Scales and Performance

I haven't used the Scales very much, but the Performance graphs are amazing. Bullet graphs are something every educator wants...and no one seems to have. These are fabulous for showing student progress. You can divide the block into regions (the sample on the left has dark, medium, and light blue bands) representing below, at, and above standard performance. The black bar in the center can show performance for the first/previous grading period, while the red marks growth (or lack thereof). What an awesome and simple way to communicate with parents. Perhaps a student isn't at standard yet, but we can still honour the progress they make.

Evolution and Comparison

I'm sure you're familiar with the Line graph shown in the Evolution section, but you might not have used Area and Horizon graphs. We'll have a look at these another time. As for the Comparison tools, you can imagine the variety of uses for these. They're great for summarizing student performance, either by examining individuals in a class or assignments across a standard.

Composition, Distribution, and Correlation

This section has some more advanced styles of graphs. You might not have had much call for Treemaps and Heatmaps in your classroom, but you might have used a Box and Whiskers or Scatter Plot. In the coming weeks, we'll spend more time with these types of graphs (along with their full-size counterparts).

Fabrice (the author of the add-in) also has a user manual and colour design manual you can download. All for free (with opportunity for donation). The add-in is also available for Excel 2003 and 2007, with the occasional Mac option. If you've got Excel, chances are there's a version of the add-in for you.

Here is one example of what you can do with this add-in. Take a sample of scores:

We have student names, formative and summative (bold) assignments, and scores. But this doesn't give us a good handle on what's happening in the class. What if our gradebooks looked more like this?

We get a picture of each student's performance. We can look for patterns and have a very concise view of what is going on. Notice that each of the line graphs are about the size of a student's name. This is what sparklines do: they condense a lot of data into one bite (byte?) sized container.

At the bottom of the list is a bar graph. This graph summarizes the performance of the entire class. The median for each assignment has been derived and "stop light" coding applied so a teacher can easily see how the class is doing. This format would take some getting used to, but what an awesome option to include for teachers.






The Downside of Add-Ins
If you use an Add-In, keep in mind that in order for other people to use the workbook, either they have to have the add-in or you will have to save the workbook as a macro-enabled workbook (it's one of the options you have). The other downside isn't so much one associated with add-ins as it is with freeware: your options for support are pretty minimal. In other words, you get what you pay for. When it comes to sparklines, there are commercial options. So, if you like the idea, but are nervous about being on your own, you might want to check out Microcharts. This is one of the reasons why I built the graphs in the "Roll Your Own Gradebook" series as full-size before minimizing. I am totally sold, so to speak, on the freeware sparklines. But they don't auto-update and can sometimes be quirky as you close/re-open a workbook. Just something to consider as you work with various projects.

We'll explore more charts, graphs, and add-ins in future posts. Is there something you'd like to see? Leave your suggestions in the comments.

Excel Gradebook for Beginners, Lesson Two: Part II, Sparklines

It's now time for the big finish for our beginning gradebook: using Excel's built-in chart functions to create sparkline graphs for our student reports. Just a reminder that you can download the workbook and play along at home.)

To do this, you'll need to create a table in the gradebook for some dynamic data. You could actually put this table anywhere in the workbook that you like. I put it below the student scores because it makes it much easier to associate the numbers with their labels.

Use your INDEX/MATCH combo function from yesterday's post to get things kicked off:

Be sure to make the cell on the "Report" sheet that contains the list of names an absolute reference. Otherwise, when you use the fill option to create the data points for the other cells, Excel will also change the location it references on the report. Not good. All you have to do is click on the "C4" in your formula and then hit the F4 key. This will lock the cell for your formula. Then, add a row of "3" underneath the student data. This will represent the number for "at standard" performance and be useful for the charts.

Now, you're ready to make a line chart using the student scores for an individual standard, and a bar chart (A/K/A "column chart" in Excel) to show growth. You'll need to clean up the starting graphs that Excel barfs up, then lock the cell size and shrink it down to fit in a cell on the gradebook. When you're done, you'll have something like this:

The charts will auto-update anytime you change the student name. They will also update if you add scores to the gradebook. Just set them up once and let 'er rip.

Here's the "how to" video:



This concludes the beginner series of "Roll Your Own Gradebook," but we have certainly not exhausted the options. Some of you are going to want to pull multiple classes, subject areas, or other data sets into a single dashboard. You're going to need a couple more formulas to make this dream come true. But I'll help.

If you've watched the videos and are still feeling lost, you can download a copy of the finished workbook to adapt and use. Don't be afraid to click and play.