A straightforward way for educators to use Microsoft Excel is to create spreadsheets that help track student progress, whether to track student grades or progress on something else such as returned forms, 21st century skills, or additional certifications. Microsoft Excel allows you to create equations that automatically calculate overall student progress, average progress, and more, as you enter new information.
- In the previous video, we took a look at how to setup a spreadsheet in order to track student progress in a certain area. In my case, I wanted to track student progress in 21st century skills. So as you can see, I've added in students, and here I have approximately 19; 18 different students, and at the top I have some different assignments. It was A Letter to Our Community Partners, A Gallery Walk, Group Project One, The Genius Hour Presentations, Veterans Invitations and so on. And below that it's telling me the category that they belong in.
In other words this one is for Soft Skills, Communication, Collaboration. Another one for Communication, and so on. So I want to take a look at here, how we can add in an equation or a formula to do some calculations inside of a Microsoft Excel spreadsheet. Now this could easily be used to give a student average. If you were putting in percentages for assignments here, you could use a formula to add up all of their assignments, and then divide it by the total possible to give you the student's overall percentage.
In this case, what I want to use it for is to get a average number for each of these assignments. Now remember that a four meant that a student was advanced, a three proficient, a two that they were basic, and a one meant that they were minimal. So what I want to do is at the bottom of each of these assignments, I'm going to go here to the cell right below the final student. And I'm going to add in a formula. So what I'll do is from this home tab, I'm going to go way to the right where it says Auto Sum, and I'm going to select one of these.
Now I have an option for Sum, Average, Count Numbers, Max, Min, and so on. There's another way I can do this real easily inside of Excel and that is to simply highlight this whole row, and at the bottom it will give me the average, the number of scores that I collected, as well as the overall sum. Now I want to put an equation in here so that is automatically does this calculation for each of the assignments. So I'm going to click again at the bottom, and then I'm going to go back up to the top under Auto Sum and choose Average, because I want to see what the average score is.
Now when I select Average, it's asking me where I want the average from, and it's saying do you want it from this area right here, and actually I want it from the top, so I'll click there and drag this all the way to the very last score. When I let it go, I'll hit enter, and it is giving me the average from cell C three to C 20. I'll hit enter, and I get 2.764706. Now Excel is very intuitive, and rather than have to enter in that formula, each time for every different column that we have, I can simply go here, click on this cell, and in the bottom right where my cursor turns to the plus sign I can click and drag that over.
Now you might say, "Well, won't that give us "the average for column C each time?" But Microsoft Excel recognizes that we want it for that particular column. When I let it go, you can see that we get completely different numbers for each of them. When I click here on say, Column E, at the top you can see that our equation is the average from E three to E 20. Now I personally don't like how many decimal points there are for these, so I'm going to highlight these, and then what I'll do is I'm going to right click, got to Format Cells, and here I want to click on Number, and I'll say I want two decimal points.
I'll click okay, and it turns those all into numbers with two decimal points. Now what can I tell from this information? Well I can tell here that I get a 3.17, so on average my students are proficient to advanced in this communication skill right here for the Genius Hour Presentations. However, I get a low score like 1.94 in the Critical Thinking thing in our Environmental Debates that we had. So that will give me as the educator an idea of what types of things I need to focus on.
So you can add equations at any point, wherever you like in your spreadsheet to do different calculations. I could go over here and add a column, and create an average for each student. Let's say that I right-click here, insert a column. I could simply go Auto Sum, Average, and average across the board here, click enter, and it would give me an average for James Aarons. So he has an average of a three. Now again if I copy this and drag that down it will give me these different averages.
And there are some with some pretty crazy decimal points, so again I would go to Format Cells, Number, two decimal points, and hit okay. And it would round that out. Now I don't really care to see this overall because I think these are all separate categories, and this Holistic one really doesn't mean much to me. So I'm actually going to right-click and delete that column. So as you can see, it's not difficult to add equations into Microsoft Excel.
- 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