How to Make a Grade Book in Excel

1
How to Make a Grade Book in Excel
By Lauren Dooley
2
Why would we want to make a grade book in excel?

• We want MORE time!
• One component of the classroom is constant
  assessment, whether by teacher observation or
  recorded grades
• Those recorded grades, like tests, quizzes, and
  homework need to be entered and averaged not
  always an easy task
• Why not simplify our lives a little and use a
  tool that will calculate for us?
• This will give us more time to focus on lesson
  planning and be 100 ready to help our students

3

• With that in mind, lets learn how to set up a
  simple grade book that will allow you to
• Insert grades
• Calculate simple averages
• Calculate weighted averages
• Convert grade percentages to letter grades
• Highlight information that reflects
• a desired condition

4
OK to start, we need to open the program
Microsoft Excel. The icon you are looking for
either on your desktop or in your programs is
this
Lets Get Started
Your blank excel spreadsheet should look
something like this when you open the program. ?
5

• Look at the bottom of your window and notice the
  tabs.
• These represent different sheets, or your
  Workbook
• Rename Sheet 1 by right clicking and selecting
  Rename
• Enter the name of your class here.

REMEMBER Save Your Work!!!
6
Now, its time to enter some information!
Make a Header! Enter the information you want
and change the colors to make it fit your style
Enter your students names and alphabetize using
the Sort feature
7
Lets fill in some grades
If you wanted to add an additional grade or
student, all you have to do is right click on Row
Number or Column Letter and select Insert
8

• Time to calculate some grades!

If all of your assignments are weighted the same,
calculating the average is very simple. All you
have to do is the following

• Click on the first cell of the Average column
• We need to use the Average formula to calculate
  the average, it looks like this
  AVERAGE(Location of cells you wish to include)
  OR for our example AVERAGE(D2,E2,F2,G2,H2)
• If your cells are consecutive, you can simplify
  as AVERAGE(D2H2)

• Another way to do this is to use the AutoSum
  function.
• Click the dropdown arrow of the AutoSum icon
• Select the Average option

9
This is how the AutoSum function looks once
applied. Notice how the formula appears in the
function bar.
Here the formula was extended using AutoFill.
Simply click the black square on the bottom right
corner and drag to include the boxes you want.
10
What if the grades are weighted???
No need to worry! ? This just requires a little
modification to your Average formula. For
example
My grading policy is Homework 10 Quizzes
50 Tests 40
Each component needs to have its own formula that
will be added to the total formula.
So Homework Add the grades (D2E2)
Divide by of grades Multiply by the
10 or 0.10 Make sure you have the correct
number of parentheses
Once you have put together each of
the components, you add it to the same average
formula so it looks something like
this AVERAGE(((D2E2)/2(.1)(((F2G2)/2)(.5)(
H2.4)))
11
Here the weighted average formula has been
applied to all of the students. You can see the
formula in the function bar above the
spreadsheet. Again, in order to apply the
formula to all of the students, click the box on
the lower right corner of the cell and drag to
highlight all of the students, in this case, to
I6.
12
Converting to Letter Grades

• To convert a numerical grade in Excel to a
  letter grade, we need to use a reference table
  that will be called upon by the program.
• To do this we create a table underneath our
  grade book that defines the grading system in
  ascending order. For example
• NOW Highlight the entire table
• Click Insert on the toolbar and select Name
• Choose Define and enter a name for your table,
  like grades

13
To retrieve your grades, we use the Lookup
function. To do this, click on the first cell
in your Letter Grade Column The formula we
use is VLOOKUP and it has 3 components
Using the sample from the previous slide, we
would enter VLOOKUP(I2,grades,2)
which means
VLOOKUP(Cell I2,grades table, Column 2 of the
reference table) Use the AutoFill function to
convert all of the numerical grades to Letter
grades.
14
Its a good idea to make legends on your grade
book indicating what abbreviations are and/or
stating your grade policy. This makes it clear
for you and anyone who might read it.
15
And Finally
Conditional formatting allows you to highlight
cells based on a specific condition. For
example, if acceptable achievement in my eyes
means students score higher than 80 on the test,
I can track those who scored below that grade.
To do this, highlight a cell in the Test
column Click Format on the toolbar and select
Conditional Formatting A new box appears. In
the first item to the left, make sure it says
Cell Value is In the next item, choose the
condition that suits your parameters less
than in this example Lastly, enter your
parameters Click the Format button to choose
how you want to distinguish your cells
What about tracking student progress? Or those
two students who always seem to get the same
grades? CONDITIONAL FORMATTING! o)
16
To extend the condition to all of the grades in
that Column, click the Painter icon and
highlight the cells you want to include. Once
you have done that, it will look something like
this
17
Excel allows us to do a lot of things that can
make some very time consuming work, like grading,
much less of a hassle. It does require a bit of
set-up time, but once that is finished, just plug
away. Now its your turn., Happy Grading! The
End ?