One of the most frustrating things (for me) as I try to do new things with Excel is the lack of non-business examples. Most websites and YouTube videos assume that you are (a) always working with numerical data and (b) interested in some sort of angle about profit margin or losses. We really do need a bank of "how to's" that models for education. If you have seen some, please do share in the comments.
I've had a lot of you contact me over the summer asking about my Excel gradebook and any updates. I have been promising to post those...and now your wait is over. The video below will show you how to get set up. I also have a sample workbook you can download and use with the video. (But if you want to use your own data sources, that's cool, too.)
As you will see, the workbook has two worksheets: Scores and Report. This allows me to keep the raw data separate from the dashboard reporting too. Depending upon what you're working with, additional sheets can come in very handy. Perhaps you want them for qualitative data you collect, attendance, discipline, or other notes. If not, and you're anal-retentive about how your spreadsheet looks, then an extra sheet is very handy for stashing your formulas and ranges: It will keep your raw data looking fresh and clean. If you're hellbent on making things look pretty, stay tuned for later videos. You'll have the Miss America of dashboards when I'm done.
I populated the Scores worksheet with some names, assignments, and data. Even though the default color themes in Excel are awful, I demo them so you can see some basics about applying colour. I like to separate grading periods and different types of standards using colour. This makes it much quicker to find information. But I also apply conditional formatting to the spreadsheet so that I can more easily visualize what is happening with the scores. Finally, I use a simple formula to determine the median and help summarize the scores.
In the next session, I'll show you how to create a dropdown list of student names. The selection from the list will be used to auto-fill many of the cells in the reporting tool using a formula with INDEX and MATCH functions. Sound like it's over your head? Come back and give it a try. I think you'll be surprised at just how easy it is.
Very well done. I have hacked at this stuff for years and it is nice to see someone make a clean presentation about it.
ReplyDeleteA big applause from my side too :)
DeleteExcel Dashboards
I realize this is probably the dumbest question ever, and I've been looking through your posts trying to find the answer. How do you go about representing the data in your 4 point system as a traditional percentage? I hate traditional grades, but unfortunately my district hasn't moved forward in this regard. I still have one spot on the gradecard where I am supposed to cram all of my data with a (seemingly) arbitrary letter grade. How do you tackle this issue using the data presented in a 4 point system?
ReplyDeleteThis is NOT a dumb question. It is, however, the $20M question...so if you figure out the best answer (as there is no "right" one), you'll be able to retire with it. :)
DeleteMy answer is here. Basically, each student and I sat down together and figured out what to put on the report card. I understand that this approach doesn't work well in a time where parents can log in and watch student grades like a stock market report. And yes, it's a squishy way to do things...but oddly enough, students always said they knew why had a particular grade in my class vs. their other classes where there was calculation involved.
Other teachers have other systems and answers for number crunching. I recommend taking a look at what Jason Buell and Frank Noschese have to offer.