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.