Measuring and tracking student progress is an important way to deliver feedback to student. Microsoft Excel allows you to create spreadsheets which make it easy to do this. It also gives the ability to create automatically calculated grades and progress reports and turn them into visual representations to share with students.
- View Offline
- [Trainer] In this course, we're not gonna go in depth with the features and functions for Microsoft Excel. In fact, my goal is to create a simple spreadsheet in which I can use to track student progress. Now, if you're an educator, you likely already have a grade book that you require to enter in student grades regarding the content. But I wanna create a spreadsheet that I can track other things such as students' progress in 21st century skills and then give them formative feedback. Now, this is something that I don't think we spend enough time on, is giving students feedback on some of these different skills.
So my goal is this, it's to give students formative feedback on 21st century skills including communication, collaboration, critical and creative thinking, inquiry, and soft skills. For different activities I have throughout each semester, I'm going to use a rubric to assess these activities and give them a score in which I believe that they are advanced, proficient, basic and minimal. Now, this isn't going to count even for a part of their grade, it's something that we're going to have a conversation with throughout the year.
So I wanna setup the spreadsheet so that I keep an eye on who's making progress, who needs additional help and so on. Now, I gotta warn you that in the beginning I'm going to go kinda quickly and just start setting up a spreadsheet. My goal is to just show you what we can do inside of Microsoft Excel. Now, the way that you setup your spreadsheet may be completely different and you may be using it for a completely different reason. So what I'm going to do right now is that first I wanna select the area that I'm going to be using for my spreadsheet.
I'm going to go 40 down and 35 over. And once I have that area selected, I'm going to go back to the beginning and I'm going to click on the icon up here that allows for borders. And I'm going to click on All Borders. This creates a border around each of the cells and, I think, makes it a little bit easier to read and follow. Now, what I wanna do is I wanna start giving some names to these different cells. So right now I'm in cell A1 and I'm going to type in Last Name. In the next cell next to it, B2, I'm going to type in First Name.
Next to this, I'm going to have Assignment. And I'll just keep it Assignment right now, but that's going to be the title of the assignment. And below that, I want to have the type. So the type of assignment that it will be. Now, what I wanna do first is, you can see that Assignment is carrying over into the other field already. And we're going to need to have this be a little bit bigger when we start to type in the names of the assignment or the activity in which we're gonna assess the students on. So I'm gonna do it like this, I'm going to select the line right below the 1.
And when my cursor turns into that plus sign, I'll drag that down just to make that a little bit larger, so these cells are larger. Now, inside of this cell where it says "Assignment," I'm going to click in there, it's C2, and I'm going to right-click and go to Format Cells. From here, you can click on Number, Alignment, Font. I'm going to click Alignment, and on the right I'm going to adjust this up to about a 75, 76 will work, 76-degree alignment.
So that will be the orientation of the text. I'll click OK, and as you can see it creates this diagonal look for that cell. Now, I can easily copy this by dragging this over. So what I can do in order to copy it is in the bottom right of a cell, there's a dot, and when my cursor turns into the plus sign, I'm going to left-click and drag that over all the way to the end of my grade book. And as you can see it puts Assignment in there. So that's where I'll put the titles of the assignments and they'll appear as that diagonal.
Now, the next thing that I wanna do is I want to merge these cells because I'm going to have the type right here. I think this looks a little awkward with Last Name and First Name, so I'm going to drag from cell A1 down to A2 and then simply go up to Merge & Center. Now, I'm on the Home tab and I'm going to click Merge & Center, and it will merge those cells together. Now I'm going to click and B1 and B2 and, again, Merge & Center. So now I have my Last Name, First Name and Type on the same line. Now, for the type of assignments, remember that I wanted to track students on communication, collaboration, and a few other 21st century skills.
Well, to make this easier, it would be nice to have a drop-down menu there in which I could just select the category that the assignment belongs in. So let's put a drop-down menu in here. First, I'm going to go inside of D2, right here, where I would like the drop-down menu to begin, because this will be our first assignment. And I'm now going to scroll all the way to the bottom of our spreadsheet and even a little bit below that. So down here in some cells that I am not using. I'll go here in A46 and I'm going to type in what I would like to appear in the drop-down menu.
First, I'm just going to put a generic phrase like (type). Below that, I'm going to abbreviate for communication and put comm. Then I have collaboration, critical thinking, creative thinking, inquiry, and soft skills. Now, what I need to do is go back up to the cell in which I wanted to create the drop-down menu and I'm going to go up to the tab up here for Data. And then over on the right, Data Validation.
Now when I click this, it brings up Settings, Input Message, Error Alert. I wanna stay in Settings, under Allow, I'm gonna click the drop-down menu and I wanna select List. After I click List, it asks for the source. So what are the contents of this drop-down menu? So what I'm going to do is go the right and I'm going to click here to tell it the cells that I'd like to use. So I'll click here, it's asking me which cells I'd like to use, and I'm going to scroll back down, I'll left-click at the first one and drag that down to the bottom one which is the soft skills.
Now, once I have that done, I'll hit return, then I'll hit OK, and let's go back up to the top. And just like that, inside of this cell, I now have a drop-down menu which will have these different categories. So let's say that assignment one happens to be a gallery walk. So in here I'll type in Gallery Walk. I like how that looks. And that is going to be one one for communication. Now the next one, it does not have a drop-down menu. So what I need to do is to copy this cell and that formula over to all of the different cells to the right of it.
So what I'm going to do first is put this to the generic phrase that I said, (type), where it's just saying this is the type of assignment that it is. What I'm going to do is in the bottom right of that cell, when my cursor turns into a plus sign, I'm going to click on it and drag that all the way over. Now as you can see, it says "(type)" in each one of them. And let's just say on W here, if I click there, it brings that drop-down menu and I can choose. So just like that I made it that simple to select the type of activity that this is going to be.
So I have the basic structure for my spreadsheet laid out, now what I wanna do is to take a look at some of the different features and functions that's we can use, including adding some equations to calculate scores, also taking a look at how we can conditionally format cells and filter the information. So I'm going to put in some student names and some scores, and in the next few videos, we'll take a look at some of these features.
- Creating effective and engaging presentations
- Creating flipped classes with Office Mix
- Empowering students with PowerPoint
- Creating tutorials, newsletters, and flipped classes with Sway
- Tracking student progress in Excel
- Using Word templates
- Creating visual representations in Word
- Using OneDrive for collaboration and storage
- Creating modules and flipped classes using OneNote
- Using Outlook, Docs.com, Skype, and SharePoint