I am going to show you how to make a grade report in excel so that for each student
you can have their own printout that says you know how they're doing in the
class
this is not necessary the most
easy way of making a grade report
but I think it is the most flexible and if you're trying to make one for yourself
from scratch this is probably one of the best ways to go
You can actually find this
specific workbook in the description
but we're gonna make it from scratch
So what I have is I have a giant table that has all the data that I'm going to be using.
If you have your own grade book you should definitely be using an excel
table for it.
So I'm going to go table-> tools-> design
and summarize by pivottable
and we'll put this on a new worksheet.
So the first step
is to take the students' names
and put it in the rows. I have all the students,
looking good so far.
Now I'm going to take the overall grade
and I will put that in there
and we have the overall grade not formatted
the best but that's okay for now.
Then I'll also put in some assignment grades
so I have Homework 1
And I have Classwork 1.
So this is starting to look ok
but it's not exactly all the way I want to work I want everything to be on the
left and then just one column here and the trick to doing that is to take the
columns
where this is values and put them here
and now you have
what starts to look like a grade report which is awesome!
So let's format the second column, so i just selected that whole column
And I go to home and I'm going to change this to a number so it looks pretty
Okay now it looks pretty.
And now we're just going to do one more easy step
I'm gonna go to where it's his name
and I'm going to go all way down to
field settings
Go to Layout & Print.
and insert page break after each item.
and now
what happens if we go file->print
we can see that we have
a grade report!
really quickly for each student If I have the next button you'll see it.
Billy, Ernest, Fred, etc.
So that's a good start but there's a couple things we should probably do
I don't really like the sum count something
I would much rather have realistic names
if you try calling it what you originally had,
you get an error that the Pivot field name already exists.
The best way to get around it is you simply add a space
and hit enter and then Excel is happy
so I just always leave a space
and then and then it works just fine
So that
that is a good start If you want to add a header
you just have to insert some rows
and you can
you know type your classname and you know
who you are, etc.
and then you want this to repeat on each page so if you want to repeat
what you need to do is you need to go to page layout
and somewhere you'll find the word print titles
and it will have something that says rows to repeat at top
and you just simply say i i want to select how about all the way down to
here
and you can click ok
and now what happens is when you go to
file->print
you have
a nice little header
fro each student
and so that's that's the basics
and that looks pretty good
and you can rename this
so this would be like grade report
uh...
and then if...
that's, yeah, that's...
that's a lot! If you've gotten that far you're doing pretty good.
So now I'm going to try to go and reproduce some of the formulas to do some more lookup stuff.
so for example previously I had something that said
like max points
okay so we went back to here if i want to show
this point for... like ten points for Homework 1
that's a little more complicated
so we'll walk through
how to do that
so the way that this is done
you type equals to make a formula
you type match
match is going to look up some value
and I wanted to look up this value which we're going to modify in a minute
and then we have a lookup array so this is where we want it to look up
so I want you to look up and i'm holding control shift enter
I select where I want it to look up
and the match type
is
zero for an exact match
and so if you look at what happens it's is N/A (not applicable) and here's why
So this is a neat feature
I'm going to go to formulas
evaluate formula
and you can see that what happens is
there's a space still in ' Homework 1'
so it looks up everything and it says they can't find it
so here's how we get around that
we're gonna go to here and we're gonna say
you want to look at the right
part of this
and the number of characters is the length
of all this
minus one
Close that off
and now it's found it's a Homework 1
is the twentieth position
in...let me just jump to it
T is the twentieth column and so it has found that
so that looks good
uh... but now we want it to return the point value
so the way we do this is we say
equals offset
the reference so in this
place over here
I'm going to select the first cell in line
the number of row to drop down: I want it on the same row, so I'll say 0.
the number of columns
to reference
is this whole value here this whole match
minus one
that's tricky
and that's because it already is telling us this would be one
we don't want to offset
and then we just hit enter.
and look at that we have it's ten and for classwork one
is one
and for overall grade it is zero
and there's only one little bug with that
and that is we want to keep this stuck on a 2.
so i put in a little $ (money sign)
so you can see what little money sign as
and that makes it so when we fill
this looks up correctly
but what I want, I'm going to do, you can do a little more formatting
if you'd like
and so that's when you have a student name
it doesn't say
Not Applicable N/A
so the way to get around us as you say
iferror(
all this
comma
uh... "" (nothing)
in there now
we get rid of all those errors
it's still says zero for overall grade
and you can say you can make your own if statements
if this
doesn't equal zero
comma
all of this
[I love this <3]
else
show nothing
and then when we fill this
there we go
that is the formula right there i'd would not copy it if I were you
and but
that is how how you can make it.
Have fun! =)