How to Tally Points and Calculate Grades in Microsoft Excel

Many schools now use grading software to make life as easy as possible for teachers. However, other schools have online grading systems, but the teachers are still responsible for tallying points and calculating grades based on an approved grade range. Many luddites exist in the world of teaching who believe there is some virtue in spending hours with a calculator and pencil adding up rows of figures. Despite how proud they remain about doing it the good old fashioned way, their calculations remain susceptible to errors.

Why spend all those hours tallying points and calculating grades when you can do it in minutes with Microsoft Excel?

This tutorial aims to make tallying points and calculating grades as simple as possible for teachers. I have created this tutorial with no assumptions about the skill level of the people using it. In other words, if you follow this tutorial step by step, I guarantee that you will be able to use Excel to tally points and calculate grades for your students and as a result, you will save hours of time.

This tutorial is intended for Microsoft Windows 7 operating system and Microsoft Excel 2007. However, the instructions well may work with other Windows operating systems and other versions of Excel.

The tutorial is divided into five parts:

—To import student information into Excel
—To format and save your Excel document
—To tally points in Excel
—To round up and round down point totals in Excel
—To calculate final grades in Excel

To import student information into Excel

1. Open the Microsoft Word document which contains the points for the relevant course

Note: If you do not wish to use Microsoft Word, and instead want to keep all the points for your course in Excel, you can skip to subsequent parts of this tutorial. I maintain a Microsoft Word document throughout the semester for aesthetic reasons—I think a printed version looks better. Plus, as you will see, it's so simple to import a Word document into Excel, that it is really not much added work.

2. Click on the Show paragraph mark button

3. If any extra paragraph breaks exist in the document (indicated by a paragraph mark), delete them (to delete, position cursor before paragraph mark and press delete on your keyboard)

Note: When you import the Microsoft Word document into Excel, extra paragraph breaks can cause unwanted formatting in Excel

4. Hover your cursor near the upper left corner of the table until a small square appears and your cursor turns into a black, four-pointed symbol

5. Click on the square so all the contents of the table are highlighted, right-click and click Copy

To format and save your Excel document

1. Open a new Microsoft Excel document

2. Click in the cell in the upper left hand corner, and press Alt + V on your keyboard (shortcut for Paste)

Result: The contents of the Microsoft Word table should now appear in Excel

3. Hover over the line separating two columns until your cursor turns into a black cross (the horizontal line will have an arrow on both ends)

4. Click and drag to the right or left to format the columns as you desire

5. Click the Microsoft Office button in the upper left of the document and click Save As

6. Choose an appropriate name for your Excel document and save it in your desired location

To tally points in Excel

1. Place your cursor in the upper left-most cell which contains points

2. Click and drag, and highlight all the cells which contain points. Also highlight the entire column to the right of the final column of numbers

Note:  In the Microsoft Word document I imported into Excel, I already had an empty column labeled Grades. If your document did not have an available empty column, place your cursor in the right-most column, right click, click Insert and enable Entire column.

3. In the Home ribbon, click the AutoSum button

Result: The total points for each student should appear in the new column

4. Click in the last column of the Excel table, right click, click Insert and then enable Entire column. Label the new column "Divide by 2"

5. Place your cursor in the formula bar at the top of the Excel document and type in the following formula: =(M5/2)

Note 1: Take careful note of your particular Excel document. The "M" in the formula I used above indicates the column letter of the column which contains the figures I previously tallied, not the column where I want the next column of calculations to appear. The column of tallied figures likely appears in a different column letter in your document.

Note 2: This step is done to divide the points total for each student in half because, for this particular course, the points total is out of 200. Obviously, omit this step if necessary or alter accordingly.

Caution: Be careful not to confuse the row number of the Excel document with the number next the first student's name in the list.

6. In the first relevant cell of the new column, hover your cursor in the lower right corner until a black cross appears.

7. Click and drag down to include the entire column. Release your mouse button

Result: The new row of figures, divided by two, should appear in the new column.

To round up and round down point totals in Excel

1. Click in the last column and create another new column (see step 4 in the previous set of instructions), and label the new column "Round Up/Down"

2. In the formula bar, type in the formula =ROUND(N5,0)

3. Drag the black-cross cursor down the entire column (see steps 7 and 8 in the previous set of instructions)

Result: The new set of figures have no decimal numbers. All figures which previously had a .49 or lower decimal have been rounded down and all figures which previously had a .5 or higher decimal have been rounded up.

To calculate final grades in Excel

1. In the formula bar, type in the following formula:

=IF(O5>=85,"A",IF(O5>=80,"B+",IF(O5>=75,"B",IF(O5>=70,"C+",IF(O5>=65,"C",IF(O5>=60,"D+",IF(O5>=55,"D",IF(O5>=0,"F"))))))))

Note 1: As with previous formulas discussed above, note the relevant column letter in your own document. You are using the column letter that corresponds to the column with the most recent calculations (in our case, the Round Up/Down column). Also, of course, enter the relevant details for the grade range you are applying.

Note 2: For future use, I recommend you copy and paste the formula into a Notepad file and keep it in a folder with any other documents you may have related to calculating and entering grades.

Note 3: This formula, of course, needs to be entered precisely. Notice in the screenshot the colour-coding of the closing brackets, which correspond in colour to the opening brackets. There should be one closing bracket for each "IF" statement.

2. Drag the black cross cursor down the entire column (similar to steps 7 and 8 in previous set of instructions)

Result: The final grades should appear in the relevant column.

3. Reformat the Excel document as you desire (for example, left align all number columns)

There you have it. An easy way to tally points and calculate grades. The great thing about the final Excel document is that if you change any of the point totals for any of the categories, all the calculations will automatically update accordingly. For example, when the worst case scenario happens and a student fails a course, I go back through all assignments and exams in the hopes that I may have missed a point here or there. If I find a few missing points, I simply update the relevant cell in Excel and see if the final grade has changed as a result. One final qualifier: as with all software programs, in Microsoft Excel, there are numerous ways to perform the same function. I have found the above methods the most effective and straightforward. If you have any shortcuts to recommend, or any other methods you feel are more useful, please include them in a comment.