How To Set Up A Gradebook In Excel

Using Excel every bit a Grade book

Class book basics

Many school systems have begun to provide grade recording software applications to their teachers. If your schoolhouse organisation does not provide such software to you, mayhap you could make your ain grade book using Excel.

Beneath you see an image of the beginning of a Grade book Several elements of this image will exist discussed 1 at a time. Your grade book may be more unproblematic than the example below. Nosotros purposely used a fairly complicated weighted grading organisation to illustrate the things that you can do with Excel

Send Email to either of the co-founders of Internet4Classrooms if you need specific aid in making, or modifying, a grade volume for your own classroom direction.

Our e-mail addresses can be found at the bottom of whatsoever of our pages.

This is not shown so that yous can copy my information into a spreadsheet. It is Just shown as an example.
As yous experiment with using an Excel workbook as a Grade volume, you should continue information technology elementary.
Outset with 4-five names and at nearly two types of grades.

Legend

Legend - List each item for which yous gave a grade. Be specific, you have plenty of room and later you may wish that you had recorded more than information virtually what assignment the class was given for.

Grade Policy

Grade Policy - You should clearly spell out how yous volition use grades to decide a student's last grade. An Excel worksheet provides enough room that this data could be included on each Grade book page.

Office

Equation for determining grades (Function) - The equation above applies my stated class policy. If you can state your grade policy as an equation, you tin write an Excel part to do the adding. The function above does the following

Test grades

Add the three grades (D7+E7+F7)

Separate by the number of grades (average)

Multiply by 0.4 (40 %)

Project grades

Add the two grades (G7+H7)

Divide by the number of grades (boilerplate)

Multiply by 0.5 (fifty %)

Homework grades

Add the four grades (I7+J7+K7+L7)

Divide by the number of grades (average)

Multiply past 0.1 (10 %)

Filling the role into other cells - In the sample worksheet above the function has been entered into cell M7. Click on the bottom correct corner of the cell and drag downward to the last cell where the office is needed. In the example in a higher place that would be cell M17.

Formatting information
Averages can exist displayed to whatever precision you wish to utilize. I used i decimal place, although you lot may wish to use zero decimal places. Nix decimal places would keep the grades in a format like they are reported to students. An advantage of using naught decimal places would be to avoid confusion regarding rounding grades. To illustrate this consider the following grade:

A grade of 75.49 would circular to 75 with null decimal places. Nevertheless, at one decimal identify that grade rounds to 75.5 and students would have the expectation that the grade would round to 76. Using cipher decimal places will allow Excel to circular without confusion to some students.

How to format

1. Highlight the cavalcade to exist formatted by clicking on the alphabetic character at the summit of the column.
2. From the Format menu choose Cells

3. From the Format Cells window choose Number and then select the number of decimal places you want to employ.

Advanced Grade book topics

Using a Lookup table

Now we will inquire Excel to look at the numerical average in column Thousand and compare it to a list which defines the grading scale, for the purpose of assigning a alphabetic character course to the average. Room was left at the meridian of the Grade book for this purpose.

The information to the left, defining the grade calibration must be entered in ascending order from top to lesser. The number entered to the left of a letter must exist the lowest number course that would equal that letter grade.

enter the numbers and messages

highlight the entire range from A1 to B5

Go to the Insert carte, select Name and choose Define

Give a name to this lookup table, I chosen mine grades

Next we volition write an equation which will await at a student's numerical average, look at a list of grades, and assign a letter grade to the pupil. This is done with a functioned named VLOOKUP. The equation must specify three elements:

The location of the numerical class to exist compared (M7 in the example)

The name of the lookup table (grades)

The location of the letter grade in the lookup tabular array (2) [because the letter grade is in column 2 in the lookup table]

After the equation is entered in N7, click and drag to fill the equation down into the remainder of the Grade volume

A Listing of Online Resources for Excel Form books

How to Utilise VLOOKUP Excel Formula - Information lookup is one of the most of import processes to understand if you lot want to analyze your data optimally

The Concluding Guide to VLOOKUP in Excel You lot'll Ever Need - six piece of cake steps