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 and turn them into visual representations to share with students.
- [Instructor] In this course, we're not going to 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're required to enter in student grades regarding the content. But I want to create a spreadsheet that I can track other things, such as students' progress in 21st century skills, and them 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 part of their grade, it's something that we're going to have a conversation with throughout the year.
So I want to set up this spreadsheet so that I can 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 set up your spreadsheet may be completely different, and you may be using if for a completely different reason. So what I'm going to do right now is that first I want to select the area that I'm going to be using for my spreadsheet.
Going to go 40 down and 35 over, and once I have that area selected, 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 want to do is I want to 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 want to 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 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 going to 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 want to do is I want to merge these cells because I'm going to have the type right here, so 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 and Center. Now I'm on the Home tab, and I'm going to click Merge and Center and it will merge those cells together. Now I'm going to click B1 and B2, and again, Merge and Center. So now I have my Last Name, First Name, and Type all in 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, 'cause 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 C-O-M-M, and 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 want to say in Settings, under Allow, I'm gonna click the drop-down menu, and I want to 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 to 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 1 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 for communication. Now the next one 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, and 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 want to do is to take a look at some of the different features and functions that 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