Excel Gradebook for Intermediate Users: Part I, Getting Organized

Welcome back to the Roll Your Own Gradebook (RYOG) series. This post builds on the lessons from the beginner's series (see Lesson One; Lesson Two, Part I; and Lesson Two, Part II). In those posts, we used a single worksheet with student scores and another as a reporting tool. Now it's time to step it up a bit. We're going to use two different classes of data and one reporting tool. First, we'll set up a page just to organize many of the formulas and lists that will drive the reporting too. Then, we'll learn how to set up two data validation lists so we can sort by class and student name.

There is a "how to" screencast at the bottom of this post. You can also download the workbook for these sessions to use at home. Ready to earn your yellow belt in Excel?

When you open the workbook, you'll notice that there are three tabs for the worksheets: P1 Biology (which is the same data from the RYOG Beginner series), P2 Chemistry, and Report (which is nearly identical to the version in RYOG Beginner). We're also going to create a new one. So, click on the little icon next to the "Report" tab. Name this new tab Formulas. While you certainly can place the lists and formulas we will use on existing worksheets, you will have a cleaner and more manageable product if you place the "engine" that drives the dashboard in its own space.

While you're hanging out on the Formulas page, let's add some information to draw from later. Using cells A1, A2, and A3, create a range for the classes. (See example on the left.) While it might seem a little silly with just two classes for now, you can imagine what this might look like if you had multiple class periods to track or multiple subjects at elementary. If you're an administrator, this list might represent classrooms in your school or schools in your district. We're just going to ease into things with two for now. Then, create a named range for this information. If you've forgotten how to do this, highlight cells A2 and A3, then on the Formulas tab on the ribbon, click "Define Name." Choose a name like "Classes" and hit Enter. You're good to go. You can also revisit Part I of the Beginner's series for a refresher. Now, using the last names of the students on the P1 Biology and P2 Chemistry worksheets, create two more named ranges. I used P1Biology and P2Chemistry as the names. We're also going to insert two lists: one with the names of the standards for biology and one for chemistry.

Now, click on the Report tab. Let's get the data validation lists going. Highlight cells C4 through F4 and then the "Merge and Center" button on the ribbon.

This will create a single cell in that space. This is where we will put our first data validation (i.e. "dropdown") list to select a class. Remember how to do that? On the Data tab, select "Data Validation" and then in the Settings, choose to allow a List. For our source, type Classes. Hit enter and your list should be set up. Now, let's do something similar for the data validation for the Last Name. The difference will be what you use as the Source:

We're going to use a formula here instead of a range like we did above. Why? And what the heck do "INDIRECT" and "SUBSTITUTE" mean? Well, first of all, we need more than one list available for this cell. We need it to display one list if we're wanting to look at Biology data and an entirely different list if it's for Chemistry---and we just want to use one cell. The "INDIRECT" function tells Excel that the source used there depends on our cell with the first data validation. It will then match things up for us. The "SUBSTITUTE" piece is necessary because we have a space in the class names. Excel doesn't do well with that. So, by telling it to substitute a space (that's the part with the " ") with no space (the part with ""), we've eliminated the source of a possible error. If you do get an error message (e.g. "currently evaluates an error), don't freak out. All Excel is saying is that there's nothing selected in the first data validation list, therefore, it doesn't know what to do with the second one. Now your workbook is organized and ready to use.

Watch the tutorial below. Come back for the next post to find out how to use the IF function in order to fill in the information for the Report. In the final tutorial for this gradebook, we'll make use of our Formulas worksheet to create the dynamic data for our graphs on the Report.