Track student progress

- [Instructor] In this video, my goal is to create a simple spreadsheet to track student progress on several 21st century skills. These are ones that would go above and beyond what I would track in my grade book or learning management system. Now you can see here that I have the different skills that I'll be tracking as well as how they'll be assessed, and that is with a rubric all the way from four down to one. Now let's take a quick look at the end product. This is the type of spreadsheet that I'd like to create, so I can easily have the students in here, their averages, I can have all the information showing me assignments and which skill that they are tracking.

So I want to build this spreadsheet. And let's take a look at how. So I'm going to switch over to a blank spreadsheet. And from here, let's take a look at some tips and tricks on how to format this. The first one is one that is a personal preference. I like to go ahead and select the area I'll be working with. So let's say it's here over to here, I'm going to highlight that. And then up at the top, I want to make sure I'm on the home tab. And then I'll select the borders icon. And from the dropdown, select all borders.

You can see that it makes the outside of each cell bold and I think it makes it easier to track and follow along. So now I'm ready to name my columns. Up at the top, I'll type in last name, first name, and assignment. Now, that doesn't look very good because the text doesn't fit inside the cells, but we can easily fix this. If I go up in between column A and B, notice that my icon, right when I get in the middle, turns to this plus symbol. I can click, hold it down, drag it, and make that cell larger.

I'm going to do the same thing here, between B and C. And then for the next one, I want to show another trick and that is when you get this plus symbol, you can double-click on it and it will automatically fit it so that the cells will fit the size of the largest text. In this case, that's assignment, so it makes assignment fit in there perfectly. I'm going to make it a little bit bigger. Now the next thing I want to do is to make the first row bigger. So between one and two, I'll do the same thing I did to make the cells larger.

And when I get that plus symbol, I'll drag that down. Now I have last name, first name, and assignment. Now I don't like the way that assignment appears here, it's going to take up a lot of space if it is going across like this. So I want to change the alignment of it. And I can do this by right-clicking inside of this cell, which in this case is C one, and then clicking on format cells. From here I can format the numbers, alignment, font, border. I want alignment. I'll click on that tab.

And then over here on the right-hand side for orientation, I'm going to set it at 75 degrees. I'll click okay. And just like that, it puts it at a 75 degree angle. I like the way that looks better, because it now will allow me to fit more in here. Now we could go ahead and do that for each column, but obviously, that would be very tedious. So one neat trick inside of Microsoft Excel, is that you have the ability to click inside of a cell, and when you do, in the bottom-right, there will be a small square or a darker area of that cell.

From here, I can click on that, so you can notice that it goes to that small plus sign when I'm on it. I'm going to click it and hold it down and drag that across this row. Now when I release it, it simply copied that into each of these cells. So just like that, I was able to change the different cells, so I have that correct orientation. I'm going to make this one a little shorter so it looks like the other ones. And I'm ready to get going. Now, the only thing here is that I could enter in my assignment, let's say for instance, it is a gallery walk, and it will put it at that angle.

But the only problem here is that I'm not sure which 21st century skill that I'm tracking for that assignment. So below this, I want to put in type. And I'll make this just a little bit bigger, so that fits inside of here. Now once I've added type, I don't like the way this looks, because it makes these two cells pretty useless. There's nothing we would put in here, because we still need to put in the type. But I can easily change this by going, again, up to the home tab, and then I'm going to select cell A one and A two, and up here in the middle, I'll click merge and center.

And just like that, it merges cells one and two. So I'll do the same for B one and two, merge and center, and I like the way that looks. So that's a lot better, it's all now in line and I'm ready to get going. Now, again, here I could go in type and let's say for a gallery walk it was communication, I could go and type that in, but I'd like to make it easier. And I would like to do this by creating a dropdown menu that I can simply use to select the 21st century skill being assessed for that assignment.

Now let's take a look at how to do that. What I'm going to do first is to create a list of the things that I want to appear in the dropdown menu and I need to do this somewhere off the area that I'm using. So right now, I'm using this whole area of the spreadsheet. I could go down here, below, and start creating it here. I could add a sheet and put this on another sheet inside of this. But I'm going to go right here. And the first thing I want to put is a generic text that says type, that will just appear there in the beginning.

Below that, I'm going to create the different skills. I'll put a shortened version of communication, collaboration, critical thinking, creative thinking, and soft skills. So here is what I would like my dropdown menu to look like. So once I have this, the things that I want to be in the dropdown menu, I'll scroll back up to the top and select the cell where I want that dropdown menu. In this case, it's right here on C two. The first thing I'll do is go up here to data, select that.

And then over to the right, there is an icon for data validation, this is what I need to select. And then I'll select data validation. From the popup box, the next thing I need to do is to create, under allow, it says any value, I want to put a list. Now it's going to ask me for the source of this list and I can enter that in here, I could go ahead and type it in, but I'm going to click on this up arrow, that will allow me to add in different cells. Now, it's asking which cells do I want to use? I'm going to scroll down here to that list I created, select that, and then I'll hit enter, and okay.

Now, if we go back up to the top, I can go into this dropdown menu and select type. Just to give it a generic type for right now. Now just like I said before, we can go ahead in the bottom-right corner, click on that little box, and I'm going to drag that all the way across. So the beauty of this, is that now, for the gallery walk, if I want to select the 21st century skill, that I will be assessing here, I can click on the dropdown menu and select communication. For the next one, let's say I needed that to be soft skills, I can click on that.

So adding these dropdown menus is a great way to make your life easier and to save you some time in doing this. Okay, so we are ready to start entering in our students and our assignments, to get this spreadsheet ready to roll.