Have students own their grades

As a teacher in middle school (I’m sure this happens in high school as well), I get this question pretty often. “Mr. Cauley, how much will this impact my grade?” I find that students have no idea how their grades are calculated – I have also found parents and teachers just as ignorant on the topic as well.

Part of the issue comes from our live grades. Students can see after an assignment what it did to their grades, but they often have no way of knowing what it will do before hand. Having students create their own gradebook in a spreadsheet is quite handy. This allows them to take real ownership of their grade if they truly understand how it is calculated. Check out the instructions below.

These instructions show how to calculate a percentage without any weighted grades. If you need help with that – let me know 🙂

Google Sheets – Making your own gradebook

This guide will help you create your very own functional gradebook so you can keep track of your own grades and have a better understanding of how grades are calculated, figured and how impactful each grade truly is.

We will be entering in 20 separate assignments, projects and tests into this sample gradebook. Feel free to add more or less as you see fit.

Let’s begin.


Step 1 – Open up a blank Google Sheet

Go to drive.google.com and sign in.

Then click the New button (on the left hand side).

A drop down menu should appear and from there select Google Sheets.

Step 1 - Open up a blank Google Sheet

Step 2 – Set up the first half of your gradebook.

You want your gradebook to have two halves. The top half will have all the individual assignments. The second half will be the final calculations and final percentage (or grade if you are courageous enough).

Write this information in each corresponding cell.

  • A1 = Entry Number
  • A2 = Assignment Name – (see if you can figure out how I wrote the word Name below the word Assignment)
  • A3 = Date
  • A4 = Possible Points
  • A5 = Points Earned
Step 2 - Set up the first half of your gradebook.

Step 3 – Add the dividing line

To better separate the top half from the bottom half we will add a black dividing line.

This is very easy to do.

Click the Row Number 6 – this will highlight the whole row.

Then click the paint bucket button in the toolbar and select the color black. This will make a black line separating the two sections.

Step 3 - Add the dividing line

Step 4 – Create the Totals section

This section will tell us how many total points there are and what our final average is.

This section is very easy to set up.

Add this information in each corresponding cell.

  • A7 = Total Points Possible
  • A9 = Total Points Earned
  • A11 = Final Average

We will add equations to A8, A10 and A12 later, so leave them blank for now.

Step 4 - Create the Totals section

This is what your spreadsheet should look like now.

I have some done some formatting like centering text, adding a grey background to the cell, increasing the size of the font. Format your gradebook however you like.


Step 5 – Adding Entry Numbers

This is a simple step. We want to add entry numbers. To do this start typing in 1, 2, 3, 4 . . . 20

Again, I centered them and made them bold but you can format as you like.

Step 5 - Adding Entry Numbers

Step 6 – Adding Assignment Names, Date, Possible Points and Points Earned

Now we can start adding in assignments.

Make up the assignment name, date, possible points and points earned for your 20 assignments.

*Helpful Tip: You can automatically format the date by selecting the dates and then clicking on Format —> Numer —> More Formats —> More date and time formats*

Step 6 - Adding Assignment Names, Date, Possible Points and Points Earned

Step 7 – Total points

Now that we have a bunch of data in the top half, we can start to work on some calculations on the bottom half.

In cell A8 we need to calculate the Total Points. Here we will need to add all the cells in Row 4 – the Possible Points row

In cell A8 we will type this equation: =sum(b4:u4)

Step 7 - Total points

Step 8 – Total points earned

Now we will add up all of our total points earned.

In cell A10 type this equation: =sum(b5:u5)

Step 8 - Total points earned

Step 9 – Final average

Now that we have our total points and our total points earned – we can calculate our final average.

To find our final percentage we need to divide the total points earned into the total points possible.

In cell A10 write this equation: =A10/A8

You should see a decimal. That’s OK – we will fix that next.

Step 9 - Final average

Now I see a decimal but changing it into a percentage is very easy.


Highlight the cell with the decimal in it.

On the toolbar there is a percentage button (%). Click that


Now you have a final percentage!


Optional steps

If these 9 steps were easy – test yourself by adding this functionality to your gradebook.

  • You can use conditional formatting to highlight assignments that are 0’s
  • Create a final grade that changes based on the final average (this is completely new)
  • Create a row to show averages for each assignment
  • Create conditional formatting for each assignment to highlight low grades
  • Create a way to lookup assignments to get all their information quickly (challenging)

Good luck!

Grading should be transparent!

Good day reader. Today, I’m going to write an opinion piece about something near and dear to many of our hearts – grades. I believe that grades should be transparent. Doesn’t that sound nice? You bet! it does but what does it mean? Here is what I’m talking about. Of course, I’m still not a fan of weighted grades, but that doesn’t mean I hate all grades.

Students, parents administrators, and, of course, teachers need to know how grades are collected and calculated. It sounds simple enough, but you would be shocked how many of these stake holders have no idea how grades are calculated. This ignorance can cause big problems when parents or students question their grade and the person or institution who is reporting it can’t explain how it came to be. It’s also a little embarrasing too.

I’ve worked in a fair number of schools and in all schools, without exception, there have been a substantial number of people who have no idea how grades work. Shocking – but true. Here are some questions I’ve had to field.

  • If I don’t turn this in, what will happen to that grade?
  • So, this assignment is worth 10 points, does that mean that the final grade will go up 10 percent?
  • When I calculate the grade I get something completely different – is there something wrong with the gradebook?
  • I got this grade on an assignment and my friend got the same grade, but his grade went up more than mine. Why did that happen?
  • I have weighted grades and I got an A- on my quiz and my grade dropped even though I had an A!

The list can go on, and these questions have come from students, teachers, counselors, administrators and parents. It really doesn’t matter who asks the question, the fact that they don’t know boils down to that this person wasn’t taught or didn’t bother to learn and what’s worse is that they are stake holders. So let’s dig deeper.

Don’t point fingers!

When people don’t understand something and it affects them, they become confused, often frustrated and don’t know who to turn to for help. It’s easy to point fingers and start blaming people but this is usually counter productive. Check out the blame game below.

  • Parents can blame teachers for not explaining it to them
  • Students can blame teachers for not explaining it to them
  • Counselors can blame the IT people or the Student Information People for not explaining it to them
  • Administrators can blame teachers for not properly communicating with parents
  • Parents can blame administrators for not forcing or standardizing how grades are reported
  • Students can blame the school for not making it clear how it works
  • Teacher can blame the administrators for not explaining it to them

It can go on and on but one thing that we all know here is that this only makes the situation worse and angers people. It doesn’t work towards the goal that everyone wants – which is a clear explanation of how grades work.

First move – write a policy

Decide what type of grading system you want. Is it going to be weighted, averaged points or a set number of points that all teachers must use. I am sure there are other options out there, but the school needs to settle on one and the entire teaching staff must abide by and use it. No odd one’s out. That way students, teachers and administrators know the basics of all grading in all classes.

This way if there is a parent meeting about grades, the “how question” has already been answered, understood and can quickly be addressed. Even if the parent isn’t familiar, if the administration can point to documentation sent home and visible policies regarding grading, it will move the conversation onto more important questions such as “Why?”, “What can be done?”

Next – Identify an expert

Now that policies have been set there needs to be an “expert” in the school. Someone where students, parents, other teachers or administrators can go to ask about grades and handle these unknown questions. This person should have the ability to at least view everyone’s gradebook. In short it this perosn is a point of contact.

They should also be someone who is very available to all stakeholders. I tend to think that counselors should be this person, if not a counselor, then an IT coordinator or principal. Definitely an educator for sure. Someone who knows the kids and is familiar with working with parents.

True story. I was at a school and had to mark a students quarterly grades as incomplete. I didn’t know how to do it and emailed a few people, but no one got back to me, so I went ahead and submitted my grades thinking that someoone would catch it and fix it. The next day I received no less than seven emails telling me to change it and was visited by five people telling me the same thing. When I asked how to do this – no one could answer. I was upset, frustrated and the knowledge base for the program did not address this issue. In short, I was being asked to do something from a group of people and no one could give me an answer. I later learned that I needed to go to the IT department. Since no expert had been pointed out, something that should have been answered in an email took three days to figure out.

Training and testing the staff

Yep – you read it correctly – training and testing the staff. I’ve sent out all staff emails with important information before only to have that important information ignored. You can’t trust people to read their email or follow a set of directions. There needs to be a workshop and accountability. This should be run and organized by the “expert.”

It should not just be a workshop where someone walks the teachers through the grading policy. There should also be a test. This is to ensure the administration that the teaching staff not just knows but understands the policy and how it applies to their specific class.

Communication – with students

Students need to also understand how grades are calculated. Again, it shouldn’t be a handout or a teacher standing up there talking at the students. They should be shown how to access their grades (if that is an option) and make sure they actually can.

They should also know who to go to if they lose access to that system. That person is should the grading “expert” or maybe it is an IT person since it deals with an IT system.

Students should also be given or shown how to create their own gradebook on a spreadsheet. While having access to their grades online is good, it often does not give them a deep understanding of their grades. Having them to keep track of their own gives them a deeper sense of ownership and the ability to “play” with their grades to see how certain assignments can benefit or hurt their grade in certain situations.

Also, if students understand how grades work, they can work with their parents, thus making their parents a larger part of the educational and learning process. Usually a very good situation.

Communication – with parents

Parents also need access to the online grades (if your school has it). This is usually done through email, but there should also be an opportunity when parents can come after school for a workshop. Again, this should be organized by the “expert.” It gives a face to a name and is a gesture that is quite often appreciated by the parent community and this goodwill goes a long way.

Parents also need to know how to calculate grades. Trust me, this will save a bunch of emails later on in the year if parents understand how grades work.

Post info online

Finally, guides shou ld be created and posted online. These guides should be available to anyone. If you can point parents, students and others with basic questions here, this will save lots of time and confusion.

Wrapping it up

This is a long post, I know, but it’s important. This is a lot of work to be done at the beginning of the year, but if everyone knows where the expectation is and if people know what they are expected to know – it gets a lot of procedural questions out of the way and lets everyone get down to the important business of teaching and learning.

More Spreadsheet Programming

Working with spreadsheets is a good and practical place to start getting students interested in programming. This short guide will show students how to make a spreadsheet calculate unit rate between two products to show the best buy.

This will work with any spreadsheet program, though the instructions will be different from program to program but rest assured, it will be able to perform all of these actions. This guide will show students how to do the following:

  • Add division problems
  • Conditional formatting
  • Organizing data

Leave comments below!

Google Sheets – Making a Unit Rate Calculator

Calculating unit rates is easy, but using a spreadsheet makes it even easier than that. Also, we will add a little conditional formatting so when we are comparing two products we can see which one is the better buy.

Step 1 – Create a new Google Sheets file

Head over to drive.google.com

Then click on the New button and select Google Sheets.

A new window will pop up with a blank spreadsheet.

Step 1 - Create a new Google Sheets file

Step 2 – Set up your sheet

Set up your sheet just I have set up mine.

Step 2 - Set up your sheet

Step 3 – Add your data

Make sure you are putting the correct data in the correct column.

Step 3 - Add your data

Step 4 – Write your formulas

Now comes the fun part. We are going to write our formulas for Unit Rate #1 and Unit Rate #2.

Remember we must use an equal sign (=) in order to tell the spreadsheet it is going to perform an action.

So in cell A2 write this equation: =c2/d2

In cell B2 write this equation: =e2/f2

These equations will take the values in those cells and divide them.

Step 4 - Write your formulas

Step 5 – Replicate

No, you don’t have to type in the equation over and over, though you could if you wanted.

Select the cell in A2. Now notice there is a little blue box in the lower right hand corner of that cell. Click that and drag down like in the image below.

Now you should see new unit rates for each row.

Do that for the Unit Rate #2 column.

Step 5 - Replicate

Step 6 – Conditional Formatting

Now that we have our data and our formulas, it would be nice if the spreadsheet could show us which Unit Rate is the better buy.

To accomplish this we are going to be using something called Conditional Formatting.

First we must select the row data in A2 to A14.

Step 6 - Conditional Formatting

Add a new rule

A new box will appear on the far right hand side of the screen.

Click on Add a New Rule +

From there, it will change to give you lots of options.

There will be a choice called Format cells if…

Click that option and at the bottom select Custom formula is…

Add a new rule

Add the custom formula

Now we need to type in a custom formula. So we have to tell Google Sheets that if the number in cells A2 to A14 are less than the cells in B2 to B14, then it format those cells.

Below the Custom formula is selection there is a blank box type this formula: =A2:A14<B2:B14

What this is telling the spreadsheet to do is look at the numbers from A2 to A14. Then compare those numbers to the numbers in B2 to B14. If they are less than (notice the less than sign) then format those differently than all the other cells.

Add the custom formula

Now that we the format (you should be seeing some changes already) it is time to tell Google Sheets what those cells should look like.

Here you can change the following:

  • Font color
  • Cell color
  • Add bold, italics, underline or strike through

Change it to your hearts content and then when you’re done click on Done.

Now that we the format (you should be seeing some changes already) it is time to tell Google Sheets what those cells should look like.

Now do it for the Unit Rate #2 column

Use the steps above to do it again.

The Custom formula will be a little different – try to figure out what it should be.


Programming with spreadsheets – START NOW!

I’ve heard a lot of math teachers comment (to me personally) how difficult it is to integrate math into their classes. Most of the time these teachers just use apps on iPads or use websites like IXL, Mathletics or more to have students practice skills and then track the students progress. That’s fine and those apps do provide a much needed service especially in the data rich educational classroom of today.

But there is another way, an older way, that can directly tie your lessons into technology. I’m talking about using spreadsheets. Now let me be clear, you can do this with Excel, Numbers (Mac only), OpenOffice, NeoOffice (Mac only), LibreOffice, Google Sheets or Zoho Docs. Yep, that’s a lot of different programs, but the process should be nearly identical regardless. You will be teaching your students to use a spreadsheet to solve math problems. As long as you have access to any of these programs you can do this.

Right now, my sixth grade class is working on volume and surface area of rectangular prisms. Volume is pretty easy, you just multiply the length, width and height. The surface is a bit more complicated as you need to find the area of each side and then add those values up. It’s not hard to say or even wrap your head around but doing the math by hand can often lead to simple mistakes that wreck the whole problem.

I can hear people now “They need to understand what they’re doing!” Yes, yes, yes we’ve practiced surface area ad nauseam and I feel pretty confident they can calculate it, so we’re past that. Now onto the fun stuff – setting up a spreadsheet to do the work for them.

This was their first time really working with a spreadsheet so I expected a lot of questions and confusion. I had to explain the following concepts:
Cell reference
– How to enter equations using the equal sign
– The asterisks (*) as the multiplication symbol

This confusion will pass due to the repetition of the equations that you must enter. The real magic happens when you start to duplicate the formulas and it starts to solve them automatically. The reaction of my students was awesome!

You can do this with just about any equation and I would even try it with students as young as fourth grade. Once a student understands an equation thoroughly enough, they can enter it into a spreadsheet and let it do the heavy lifting.

This is a great start for students because there is so much more they can do such as make their own gradebook, customize their spreadsheets using conditional formatting, need help with more math equations, then you can empower them spreadsheets!

I’ll put my instructions in another post and include a download link for the PDF.

Patrick Cauley – @itbabble