Transcripts
1. Excel Introduction to the Course: Well, hello there, and
welcome to this course on creating a one student result
seat in Microsoft Excel. So in this course,
you'll learn how to design a clean and
organized layout, and the student
details as needed, such as subject marks, apply formulas to calculate total marks and
percentage automatically. We'll use if
conditions to assign grades and generate
remarks based on performance as well and build a pass fail check using all the subject scores
as needed, as well. So you'll also apply
conditional formatting to highlight results and prepare
the sheet for printing. And by the end, you'll
have a professional, reusable Excel template
ready for practical use. So let's get started.
2. Planning the Layout for a One Student Result Sheet: So now let's get
started with making our automatically calculating
result inside of Excel. So to do that, first of all, I'm simply going to go ahead and open up Microsoft
Excel right here. And then I'm going to choose a blank workbook right
here for the result. So what I want to do
now is I want to make the result as an A four
size paper format. So to do that, I need to go around over here
onto View R here. And then what I need to do is
go ahead onto Pase layout. Once you do that, I see the result in a pase
size viewer here, and this gives me a better idea on what I can keep as needed. So over here on the result, I'm just going to go
around and try in result. Of final exam. Let's say, for example, right
here, just like this. I can go ahead and
format it out. So in this particular
starting period, we're going to work around
with the formatting itself, and then I'm going
to go ahead and increase the size of
the font as well. So now, apart from that, I want to add in some
student details. Let's say, for
example, over here, I want to add different things like subjects and so forth. So now I'm going to go around
over here right below, and let's just go around
with the subjects here. Let's say, for example, I'm going to go for subjects, and then I'm going
to say mathematics. Uh, mathematics right here, and I'm going to say
social studies right here. And then I'm going to
say science right here, computer science, and I'm
going to say English, Spanish. And let's just go round
onto environment sign. Let's say, for
example, let's say I have these particular subjects. So I can go ahead and rearrange the size of the cells,
just like this, as well. So these are the subjects, so let me just press
Control B to build it out. And these are what
you have right here. So now, apart from that, I want to add in total marks. Let's say, for example,
right here, just like this. I'm going to go ahead and
bold it out right here. And then I'm going to
go around and see, marks obtained right here. So let's say by the student, the student obtains
a specific marks, and then around over here, I'm going to add in grade
right here, just like this. So apart from that,
I'm just going to add in a remark over here, just like this as well, but
comes as per the grade. So now, this is what you have, as you can see here. So let's just go ahead right here and then work
around with this. So I'm just going to go ahead
right here, just like this. So now, this is what you
have as you can see. So I actually collapse this area right here,
just like this. Let me click right in between to expand this out right here. So we got all of the subjects and everything else right
here, just like this. And over here on the bottom, I'm going to add in a total
section right here as well. Pull this out as well. So this is what you have,
and now I'm going to select this out and
format it out as a table. So I'm going to go ahead and
insert a table right here, just like this and get
the format that you like. So I'm just going to go around over here and
let's say, for example, add in a last column right here, just like this as well,
and first column as well. No, last column, maybe I
want to go around over here. This is what you have. So
total row at the end, as well. Is this like this, you can
see that I got the total row, but not in the
format that I need, so I'm just going to press
Control that t here. So now around over here, I'm going to choose the
type of layout that I want. Let's say, for
example, this one or maybe a bit of a lighter color right
here, just like this. So this is what you have, or maybe something else. So you can choose the design of the table as you
want, right here. Let me just go ahead
and select out. Let's say for example, right here, just like this. I'm going to select
this layout itself, Rate and then you can go ahead and change it up
according to how you want. So this is the marks that
you have right here, as you can see
here, or maybe I'm going to choose the
lighter layout itself. So let's just go around over here onto the
lighter layout right here or go ahead right here and select out
this one itself. So let's just go onto this one. Subjects total marks,
remarks, and everything else. And I can still go around
and add in background color as I wish right here for the
top row as well as I need. So I'm just going to
go around for purple, and this is what you
have, as you can see. So we have our design ready
for marks and everything, so you can see that this
is within the pase itself. And over here on the
top, I want to go ahead. And add in other details
like class right here. Let's say, for example,
or grade right here, grade for the student. I'm just going to add in the
grade information there. Name of the student
will be here, so I'm just going to go ahead
and merge these out here and left line it just like
this and bold it out as well. So now I can add in the student details and everything right here
on this section. So now, apart from that, I'm just going to
go around and say, as an or symbol number rot or maybe I want more space
for the symbol number. So I'm just going to
go around and say S N and then bold it out. So this is the space for the
symbol number right here. And then apart from that,
let's say, for example, this is grade, and
then I'm going to go around and add in
a group section as well. Let me just add in the symbol
number right around over here and mese the cell out as well because
I need more space. And then around over here, I'm going to go around and say section right here,
just like this as well. Our group Merse this out and then go
around onto the left, and then this is what you
have, as you can see. So this is what you have, so you got grade and name
and everything. So now I'm going to go
around over here and then select this out and then
delete out these as well. So I got the basic layout ready, as you can see here. So now what I'm going to do is I'm going to go
ahead and then add in some border rot here and add in a border rot here on
the name as well. So you can see that this is
the layout that I have here. And then around over here, let me just go around onto this area and then
delete this out as well. So you got name section and simil number
here, just like this. So apart from that, what I want is, let's
say, for example, I want a percentise right
here, just like this. I want a percentise
just like this. And below it, I'm going to say overall read right
here, just like this. And let's say, for example, I'm going to go round onto result which says
qualified or not. And then I'm going to go ahead and add in
more things as well. So you got the percent
days, overall grade result. And now I'm going to
go ahead and add in, let's say, for example,
attendance right here as well. So attendance right
here just like this. And then that is what you
have attendance as well or and then I'm going to
go ahead and select out. Let's say, for
example, this as well. So I'm just going to go ahead
and then merge these out together and merge this
as well, just like this. So that is what you
have right here, and then you can see that I got the percents overall
grade result attendance right here, just like this. And I'm going to go ahead
and add in, let's say, for example, teachers
signature right here. So teachers signature,
just like this. And I'm going to add in a parent's parents signature
as well, just like this. I'm just going to go ahead and bowl these out
right here as well, and I'm going to merge these out together as well. So
this is what you have. So percent days oval
grade or overall grade, let's just go ahead and
bowl it out altogether. So now I want to go
ahead and add in, let's say, for example,
borders for this. But before that, you can
see the borders outside. I actually want to
remove it right here. Because I don't want the
borders to be shown. So I'm just going to select all of these borders
first right here. So let's just go
ahead and select out all of these borders.
I want to remove it. So now I'm going
to go around right here and select line
color as white. So it's actually using
the pencil right here, but what I want is all borders to be gone right
here, just like this. And all the borders
will be white, as you can see, just like this. So now it looks much
more like a document, as you can see right here. So now I'm going to go
ahead and add in things. Let's just go ahead and
add in border to this one, but not the but not the white color,
but the other colors. I'm just going to go around and add in blue color for this one, select this one, and you can see that the blue
border is added in. So now I'm going to
go ahead and add in border over here
just like this as well. And then around over
here to attendance, I'm going to add in border
to this one as well. And in the result, I'm going to add in a
thick border right here. Under teacher signature,
I'm going to go ahead and add in a thick bottom
border just like this. So just on the bottom section. So you can see that immediately it's starting to
look like a result. So I'm just going to
go around over here, and then around over here, just like this, I'm
going to add in a thick border right here,
just like this as well. So this is what you have,
and around over here, I'm going to go ahead
and add in all borders, just like this, as well.
So that is what you have. And even the font
color rot here, let's just go ahead and select blue font right here for
all of them right here. And then for this
one, for subjects, it's all okay right here. So now this is what you have. So now I got all of the
things as you can see here. So now what I want to actually do is I want to go
ahead and let's say, for example, let me
just expand this. You can see the result here. You can see that
only certain portion of the result is being
covered with this. So what I want to do is
I wanted to take it. I wanted to take
much more space. So I'm simply going
to go ahead and select out all rat
here like this. And you can see that
this is actually over here on the right. So this is the rat here. So I'm just going to go ahead
and let's say, for example, drag it a bit across, so that it takes up more space. So let's just go around over
here and then drag this out over here until the end,
and this is what you have. So you can see that
it's much more balanced now with the
tendons and so forth. Uh, parents signature is there. Let's just go ahead and
center align this as well. So this is what you
have, as you can see. Let me just go ahead and
mise this out so that I got a bit of a space for the
teacher's signature as well. So this is what you have,
and then I'm going to go around over here onto
our bottom border. So this is what you
have, as you can see. So now around what I want to do is I want to go ahead
and select out all. So let's just go ahead and
select out all right here and then click and drag this to increase the space
between the borders. So it's actually going to the other page,
which I don't like. So I'm just going
to go ahead and push this, and then
this is what you have. Let me just pull it a
bit more just a bit more until it actually just uses this space so
that it is balanced. So you can see that this is
what you have right here. Class teacher signature,
just like this. And then it fits
exactly one pace here. So now you can go
ahead and change out the other things
as you like, as well. So you got the result and
everything right here. You got the attendance and so forth, as well
as you can see. And I want it to be balanced, so let me just go ahead and
balance it out right here. And that is the result that I have. And this
is what you have. I'm going to go
ahead and pull this out and it is at the bottom. And this is what you have
that you can see here. So now I want a
border all around, so I'm just going to go
around and then, let's say, for example, select
out all rat here. But actually there's actually
extra space rat here, so I'm just going to go ahead
and resize it out as well. I need some extra
space over here on Z. Let's just hold Control. And let's just go ahead and make it equal. Yeah, just like this. So now I'm just working out
with the design right now, so I'm just going
to select out all and add in a thick
border outside as well, so it actually goes ahead
and looks like a result. So you can see that this is the result pase that we have
right here, just like this. And then over here is actually
going to the next phase, which I don't want right here. So I'm just going
to go ahead and then go round onto the top. So you can see that I got
next pase over here as well. So I don't actually
need all of this, so I'm just going to
go around over here, and then I'm going
to say no border. So this is select no border
here, just like this. And then this is an extra piece. So let's just go
ahead right here and delete out these things. So let's just go ahead and
delete this right here. Just like this because
I don't actually need any of these particular pages. I'm just going to delete this
out right here and keep it. So you can see that
that's deleted now, and now I'm going to
go ahead and add in an outside border at here
just like this as well. So this is what you
have as you can see here. You got the result. But I want all of the alignment of the text to be good now. So I'm going to select three of these and then write
align it, centerline it. And then for this one, I'm
going to centerline it. For this one right here, I'm going to top
align it right here, for this one, as
well, top align it. And you can see that
it's starting to look. So over here, I'm going to
select this one as well, center align all of them here. And then around over here, I'm going to go round onto
the subjects right here, just like this, select it, center align it, and
then middle align it, go round and then center align it or left align this
one and go to the top. And for marks as well, I want to go ahead and then
center align these as well. So when I enter Mark all
of the things are there. So this is what you
have, and this is the result page, as you
can see right here. And then you need to go
ahead and save this up. So over here, I'm simply
going to go ahead and save it as result template right
here, just like this. And as we move along
the course now, we're going to see how we can actually go ahead and
add in formulas and everything so that we can
actually go ahead and then create an automatically
calculating result. Over here, in total, let me
just go ahead and add in a color just like this as well so that it
looks good as well. So you got total marks and
then marks obtained here, read and remarks right here. So in total marks,
this is what I have, so I'm just going to go around over here and go
for marks obtained. I'm going to let's say, for example, right here, uh, make some changes as I like
as I go along the way now. And as we move along the course, we're going to actually go
ahead and see how we can, um get the result as needed.
3. Entering Student Info and Subject Marks: So over here, I have a
blank result ps ready. And let's say what
I want to do is I want to add in
the details now. So over here in grade, let me just type in Ix. Let's say this is a ninth grade
rot here, just like this. And then over here in
section, let me add in B. And in symbol number, I'm going to go ahead and
add in a random number. So over here, I'm just
going to go around and say Robert Tang
as the name rot. For this particular student. Let's say, for example, so I got the student details over here. So for the subject, let's just go around and add in the marks. So it's going to be 100 for all. So let me just click and drag
all of them out to make it 100 and select out and bowl this out as
well, just like this. And for this one, let me
select blue color here. So the marks obtained
for mathematics maybe, let's say, for example, 95. Here. And then for
social studies, let's say, for example, is 65. For science is going to be 79. Let's say, for
example, and so forth, it's 84, and for English, it's going to be 90. Let's say, for example. For
Spanish is going to be, let's say, for example, 82, and enviamin science is 73. Let's say, for example, a I've added in all
of the marks here, as you can see, and
there's remarks as well. But as we progress
along the course, we're actually going
to go ahead and then generate out all
of these rat here, including the totals
and the grades here. So Abras grade and
everything else, it's going to be generated. So attendance, I'm going
to go ahead and let's say, for example, this
is going to be 87%. Right here in total
and seven 5%, let's say, for example, and I'm going to center
align it out as well. So percentage right
here is going to be automatically
calculated, and over here, overall grade is going to be automatically
calculated as well, and then result is going to be automatically
calculated as well. So I'm going to leave the
automatically calculated areas as it is for now,
just like this. So that is what you
have as you can see now and then over here
in parents signature, let me just go ahead and then add in something like let's say, for example, IDK right here, something like something
like a dummy right now. And I'm just going
to go ahead and use type right here,
just like this. No type right here, just like this to give it a
bit of a style right here. So let's just go ahead
and utilize this one or something that is something
like a signature right here. Okay, so that looks
like a signature. So let's just go ahead and
then open up the side. So it's black adder right
here, just like this. So this is just a
sample right here. So usually you want
to print it blank because you're going to
add in the sign later. So let's say this is going
to be with a red pen. I'm going to go
around over here, and for tess is going to be something like
this right here, and then this is what you have. So you got your result ready, as you can see, right here. So this is the result
of final exam t here, and I'm simply going
to go around and say, um, uh, let's say, for example, um, something like keep keep on studying
for better future. Let's say, for
example, right here. I'm just going to go ahead and add in a bit of
tagline as well for students just like this and then add in a bit of a space
rot here as well. So it looks much more good. So this is what you
have, as you can see, so you got the dummy
signatures right there and the details
just like that, and the student info
is added in as well. And now as we go along forward, we're going to go
ahead and then see how we can calculate out
the marks that we have. So our result generation
becomes auto. And then you can simply
create a copy of this file and create results automatically
without much hassle.
4. Excel Calculating Total and Mark Percentage with Formulas: So over here you
can see that I got total marks and the
marks obtain data now. But let's say what I want to do is calculate the totals now. So to do that, I can go
ahead over here onto total section and press equals right here and type
in this formula sum. Open up the bracket,
and then over here from the place where you
want the marks to be added, you can simply click
and drag it out till the marks that you need. So right now, it's
from C six to C 12. You can even alternatively
type this out if you want to. So this is C six till C 12, as you can see right here. Close this out right here, press Enter, and then you can see that this
is what you have. You can go ahead and
bowl this out as well. So apart from that,
you can go ahead and press Equals right here and
type in some for this one, as well, for marks
optan as well. So you can type in
some and open up the bracket and then
click and drag this out, close up the racket right here, and then you got the total marks right here just like this. So you didn't have to manually
calculate everything, but everything was automatically
calculated right here. Let me just go ahead
and quickly save this out here so that I
don't lose the data. So apart from that,
you can see that I have percentase detail here. So to get percentase detail, you can utilize a
formula as well. So you can go around and press equals and then select
this particular obtained marks rat
here and divide it out by the total marks
here. Press Enter. Then you actually get the
percentase value here. But you actually want it
in percentase itself. So now, once this is selected, you can go around and
choose percentase here, and then you got 82%. You can go ahead and expand out, not decrease out or increase
out the decimal places. So you can increase this
out, as you can see, and this is what you have, as you can see,
right here, 82.43. Let me just center
line it out as well, right here, just like this, and this is how it looks. Let me just bold it out as well. And this is how you can go ahead and set out the
percentase as well. So you got the totals
and percentage, as you can see here. Let me just go ahead and
quickly save it out, or you can press Control S on your keyboard as well to
save it out time and again. So this is how you can calculate
total marks and even go ahead and then add in
percentase as needed. And as we move along now, we're going to see how we
can generate out grades and even go ahead and then
add in remarks as needed.
5. Assigning Grades with IF Conditions: So over here, you can see that I already have marks entered. But let's say what
I want is I want grads to be displayed
according to the marks, right? Yeah, I can go ahead
and do it manually, but I want it to be
displayed automatically. Let's say, for example, greater than, greater
than, let's say, for example, 40 is
going to be E and greater than 45 is going
to be D and so forth. So to do that, you need to add in something called
the I condition. So to do that, I'm
going to press Equals and type in I here. And then I'm going
to go ahead and then open out a bracket
rot here just like this. So I'm going to go
around and say, and then I'm going to take this as the reference rot here. So it's at mark Optane. This is fine as well. But to keep it simple,
I'm going to go around and say D six here. So if the value of D six, because this is D six rot
here is greater than, let's say, for example, uh, I'm a rate then or
equals to, let's say, for example, 95 rat here, then I want to display A plus. So I'm just going to go
around and say A plus here, just like this within the inverted comma and close the bracket
here, just like this. Press Enter, and then
you can see A plus. You can see that the
other cell range is actually coping out
these things right here, just like this because this
is a part of the same table. But I want others as well. So 65 is sewing false. This is not what I want. So what I want is I want if. So I want so greater than 95. So I'm starting with
the larger number over here first, you can see. So I'm going to say over 90 is going to be A,
let's say, for example. So I'm just going to copy out this particular if condition, type in a comma after where
the condition is true, as you can see, and
if it is false, I want to add in another
if condition t here. So if the six is greater
than or equal to 90, let's say, for example,
then it's going to be an A t here,
just like this. So I want to do
the same rot here, so I want to go
ahead and continue. So I want to add in
a comma right here. And then I can simply
repeat this step. So comma, and I'm
going to go ahead and then copy this rot here, and then I'm going to
paste this out here. So if D six is greater than or equal to, let's
say, for example, 85, then it's going to be a minus rot here
just like this. Similarly, now I want to go ahead and then
paste this out. So let's say, for example, if it is greater than 70, then it's simply
going to be a B. So no B plus B minus
whatsoever right there, but you can go ahead and add
it if you want to as well. So now, if it is greater, the or equal to 60. Let's say, for example,
it's going to be A, C, right here, just like this. And then if it is greater
than or equal to, let's say, for example, 50, then it is going to be D
here, just like this. And then if it is greater
than or equal to 40, then it's going to be an Era you can see that I got the number of brackets and everything here. And if you miss only
a single comma, the code simply will not work. You can simply press Enter, and if it works, it
sews it up here. But if, let's say, for example, some brackets are missing here, then it'll actually
say that it's a typo at here and attempt
to correct it out as well. So now, apart from
that, you can see that it actually copied out
the formula to all. And depending on the condition, you can see that
I got A plus, C, B, and so forth, as you can see here. But this one right
here sews A plus. This is not something I want, so I can simply erase this out and then it won't be shown now. If you don't see the
other grades right here, you can simply pull
up the formula, and then you will start
seeing the grades right here. Let's say, for example, now, let's say, for example,
if I were to do 45, then you get an E here, and all the percentage and everything changes
out here as well. So let's just do 75 here and press Control S to save
out the file as well. So now you can simply utilize
this template to simply enter up the marks and you
get the grades accordingly. So now, apart from
that, you also see an overall grade
here, just like this. And in overall grade, you can see that
I can simply use the same formula
here, just like this. I can simply press Control C, and then around over here, I can press Control V t here. Uh, if I want to, as well. So you can see that
this is what you have, but I want it to be
based off C rot here. So C 15 is the value rot here, so it's going to be C 15. So let's just go ahead and then copy and paste
these out rot here. So I'm going to go for D six, and I'm going to
paste it all out with C 15 right here because I want that to
be the reference point. But it actually went ahead
and said false here. That is because, um, if none of the
condition is true, then we didn't
give it any value. So I can simply go ahead
and add in an F over here. So now, it actually shows an
F rat here, but it's 81%. It's not supposed to
be an F rat here. Let me just bowl
this out as well. Similarly, now if I were
to go around and say 39, we can see that it says false, but I want F over here as well. So let's just go ahead and add in an F rot here just like this. So now, won't you go
ahead and do that rote and then click
and drag this out, then you got F rot here. So now, it says
inconsistent rot here, so you can see that
that is what you have. So let's just go ahead and then add in an F rot here
just like this. So if I were to go
around over here, then I can go ahead and
ignore the error as well. Just go ahead here. So now, what I can do is,
let's say, for example, I can go ahead and then
ignore this out right here and ignore
these out as well, just like this because it's
actually showing an error. Um, saying that there's the F. Like, let's say, for example, there's an consistency
right here. So let's just go
ahead because it's working out quite fine
for me right here. So just like this, I'm going
to go ahead and then close this help out because I
accidentally opened it. So I'm just going to ignore the error right here,
just like this. So if I were to go around
and let's say, for example, say 30, you get F right
here, just like this. So let me just press control
S to save it out as well. But over here, you can see
that the percentage is 81, but still sewing F. That is because this is in
a percentage value. So what you need to
do is you need to go ahead and add in
zero point here, just like this, and
it'll be corrected. So you can see that if I were to add in zero point rat here, then it still sews F because it's still
not greater than 81%. But if I were to go
around over here, zero point rat here still sews F. But if I were to go
around over here now, so zero point rot here, just like this, then it
sews B, as you can see, because 81.86 percents
is greater than 0.7, which means 70 percentis. So I'm just going to go
around over here and add in zero point in
all of these here. So I'm just going to go
around at zero point in 50 and zero point in 40 as well. And then this is what you have, as you can see here. So you got B right here, and I want a result. Let's say, for example,
if now, let's say, for example, the overall
grade is not equal to F, then the result is going to be pass otherwise it's
going to be feel. I'm just going to go
around and add in a simple if condition
here like this, open up the bracket and calculate the percentage
here like this. And I'm going to go around
and see is greater than. Let's say, for example, if percentage is
greater than 0.4, then the result is
going to be pass. Otherwise, it's going to be failed right here,
just like this. I'm just going to
go around over here and add this in right here, just like this and press Enter, and this is what you have. So let's just go around and then center it out right here. Bowl this out over here as well. So this
is what you have. And if I were to go around
and let's say, for example, type in a value like
0.6 right here, then it actually shows failed. Now here, because
60% is way less. But if I were to go
around and type in 49, let's say, for
example, right here, it says pass here. But if I were to go
around and say 38, it says failed here. And you can see that it's
automatically calculating. And as we move along, then what we'll
also calculate is, let's say, for example, if the student fails
any of the subject, then it'll fail as well. But so far, let's keep it simple here and continue forward with generating
remarks, as well. And that is how you can go
ahead and add in if conditions to calculate the grades on its own in the result
here just like this. And furthermore,
we're going to add in remarks generation as well.
6. Generating Remarks Based on Performance: So far, this is the
result that we have here. And let's say, now
what we want to do is generate out the
remarks part as well, based on what I have
generated rat here. So here you can see that I got a formula where yeah it says, if it's a plus, then if it's greater than 95, then it's A plus and so forth. Let me just go
ahead and copy out the same formula right
here and right here. I'm going to press Control C and then press Enter
here, just like this. Let me just go ahead and
then ignore this error out ra here and then paste the same formula here,
just like this as well. Now you get the same
thing right here. Let me just remove this
because I don't want that. This is what you
have, as you can see. But instead of the
grades right here, depending on the grade, I want certain remarks. Let's say, for
example, over here, instead of A plus, I want it to say
exceptional work. So now, what I actually
have right here, is the list of remarks
right here, just like this. So for A plus, I want exceptional work. So I'm just going to go
ahead and copy this outright here and paste it
instead of A plus here. Mind that, this
is going to be in the remark section
of the code here. Press Enter, and then you get the remarks right
here, just like this. I'm not going to worry
about formatting as of now, and I'm simply going to go
ahead and continue work. To make the formula
easier to actually read, I'm going to press I'm going to resize this formula
bar out and press. Let's say, for example,
I'm going to go around and hold Alt on my
keyboard and press Enter. So for each of the
I condition here, I'm simply going to go ahead
and press Alt and enter here so that it actually goes
ahead onto the new line. Uh, even if you change around the line right
here, just like this, it does not actually affect out the code rat here. So
this is what you have. So now after each if you can
see that I did a line break, and now I'm going to simply copy and paste this
out right here. So for A, I'm going
to go ahead and add in this feedback rate here. So for A minus, it's going to say
very good effort. So I'm just going
to go ahead and paste out this one right here, just like and if I were to go around over here
onto B right here, I'm just going to press Control
C and control right here. I'm going to paste out
this and so forth. So now I'm onto the C now, so I'm just going
to go ahead and paste this onto C right here. And then I'm going
to go around onto D, which says below abras
right here, just like this. So I'm just going to go ahead and paste this out over here. Below abras. And then
E is poor performance. So let's just go ahead
and copy this rot here and paste this out here
in E. And finally, we got F t here, which says lacks understanding
and consistent effort. So let's just go ahead and then paste it out right
here, just like this. And I'm going to go
ahead and press Enter, and this is what you have. Let me just go ahead and
drag the formula out. All across, and this is
what you have right here. So for this error right here, let me just go ahead and
ignore these errors right here because it's actually
working quite fine. So let's go ahead right here, ignore this out right
here because this is a table that's actually sewing up that, but it doesn't matter. So that is what you
have. And now I want this text to
actually be visible. So I'm just going
to wrap the text around right here
and it is visible. But I want to give it a
bit of a space right here. So for that, I'm going
to change around the marks obtain part right
here, just like this. I got a bit of a
space t here as well. I'm going to wrap the text for maxobtan as well so that
it takes up less space. Same goes for the
total marks as well. I'm going to wrap it up a bit
right here, just like this. For the grade as well, let me just go ahead
and bring this in. So now for this one, this spot, let me just go ahead and
expand this out right here, just like this until it takes up all of the
space rot here. You can see that I got all
of the feedbacks A here, just like this. And it is 11. So let's just go
ahead and decrease the font size of it to ten
until I see everything. So now if I were to actually
go ahead and type in 23, you can see it says
lack understanding and consistent and
consistent effort here. But if I were to go
ahead and say 78, let's say, for example, the
remark scene is out as well. So depending on the grade, you also get the remarks
here, just like this. Over here, you are
actually not getting the percentage because
of the space I've given. So let's just go ahead
and expand this out a bit and this is it here. So this is how you can see
that I can go ahead and generate remarks based on
the performance as well. And next we are actually
going to see something. So right now, it says pause even if one of the subject
says fail right here. And this is what
we're going to work with now in the next lesson.
7. Adding Condition to Pass and Fail by taking Reference from All Subjects: So far, we have
our result ready. But if I were to
actually go around over here onto, let's
say, for example, one of the subject and then
give a failing mark rat here, it says F, but this
result still says pass. But let's say what I
want a condition as is I want everything to
be counted out rat here. And if there's one F, then I want to see that
the student has failed. So let me just go ahead and make it pass right
now just like this. So now over here in result, I'm just going to
change out the formula. So this is the simple formula. Let me just go ahead and then
base the condition off of the F grade that we may have
in any one of the subject. So to do that, I'm
going to press Equals here and
type in I t here, and then open up the bracket
and type in count if. So I want to count
the number of Fs from cell E six to E 12. So I'm just going
to go ahead and say count if and then open up the bracket rot here
and then I'm going to say six right here. So this is what I'm
going to go ahead and choose till E 12, right here, just like this. So once you go
ahead and do that, this is what you
have, so you can go ahead and select it out. And then what you
can do is count F. So I'm just going to count the number of Fs that there are. So so count F E six till E 12 has any F. And if that
is greater than zero, say, if you find any one F, so I'm counting it
from E six to E 12, four Fs, and then it'll
written out a number rat here. And if that number is
greater than zero. So if you find one F, then it is greater than zero, of course, then I want the
result to be Fra here. Otherwise, if you
don't find any Fs, then I want it to say pass
here, just like this. Or I'm just going to go around
and say fail right here. If it is greater than zero, then fail, otherwise, pass. So I'm just going to
close the bracket t here and press Enter, and now it says pass. But if I were to go
around onto four, then the percentase is 71.14, as you can see, but
since there is one F, it says fail right here. So as soon as I make it pass
rat here, it passes up. So I can go ahead rat here. And if you have, let's
say, for example, do fails, then of course, it's a fail here just like this. So I want to do
the same rat here. But still, let's say, for example, over here
in overall grade, you can still see that if I
were to give a failing marks, then it still sows D rat here. But I want it to actually
fail out because, let's say, if it is failed, like I said, for example, if there's
any one F rat here, I want it to show F as well. But I want to leave the
D as it is right here. So if it is not failed, then I want it to show these particular um this
particular content itself. So now there's a
long if condition as you can see here
just like this. So I'm just going to
go around over here. And let's say, for example, copy out till here. So I'm just going
to copy till this. So until this
particular section, it is the same, here,
just like this. And after that four pass. And if it is passed then I want it to run this code itself. So I'm just going to paste
out that part right here, just like this, as you can see. So if E six till E 12 is
F, then it says fail. Otherwise, it will
actually run this. So let me just
close the bracket, and it says fail a here. But I don't want it to say fail, but just F here. So you can see that it
says F, just like this. But as soon as I go
ahead and type in 56, it says C here. So there's a nest if condition
right here just like this. So I can go ahead and add
in let's say 90 rot here, and it says 64% here,
just like this. So I'm just going to go ahead
and add in 90 here as well. It's B here, 95, let's
say, for example, 96, 78, and then you can go ahead and then increase out the
number rot here. And then this is what you have, as you can see rot here. So this is how you can actually go ahead and
work around with it. T me say 95, and it says E minus rot here. So let me just go ahead
and save this out as well, and we got our result ready now. So you can see that I have
all of these right here. So based on whether I
fail only one subject, it'll actually sew F
and fail right here. Otherwise, it'll sew the grade. So as we move along, we're
going to also add in conditional formatting
to highlight results as needed, as well.
8. Using Conditional Formatting to Highlight Results: So over here, let's
say for breeds, I also want to go ahead and
color code out the results. So that depending
on the marks that the student gets,
there's a color code. Let's say, for example,
for this one right here, I want it to color
code something. So to do that, as my
cell is selected, I'm going to go to conditional formatting right
here, just like this. Or I can select a
whole range as well, go to conditional
formatting and use the highlight cell rule
right here, just like this. So you can see that you can
also manage out the rules, right here, just like this. So now what I want
to do is I want to delete out the rule and add
in a new rule right here. So only format the cell that contains, let's
say, for example. So cell value not between, but I want it to
be equal to here. Cell value is equal to A plus, let's say, for
example, right here. I want it to format green
right here just like this. So press Okay and
then what happens is that the cell A plus
will be green here. So if I were to apply,
you can see that all the cells with
A plus is green. So you can similarly go ahead
and add in the color value. So this is what you
have, as you can see. Let me just duplicate
the rule rat here, and cell value is A plus. So for A as well, I want it to work
around with the same. So I'm just going to
go around over here. So I'm just going to go
round onto A rot here. So A is going to be
the same as well. Let's apply, and for A, it's green as well. So let's just go
around over here, duplicate the same rule out
rot here, double click, and then I'm going to go
ahead and A minus R here, and then press O here and
apply it out as well. So let's just go ahead here. So let me just press Cancel and duplicate this
layer out here. So we need to go ahead and repeat it for all
of it, right here. So if it is equal to B, then it is going to
be green as well. Go ahead and duplicate it, and if it is going to be C, then it is going to
be the same as well. But for D and E, I
want it to be yellow. So let's just go ahead and
duplicate around over here, I'm going to go ahead and type in D and then go around onto format and then go around onto this color here,
just like this. Let's select a lighter yellow and then apply it
out right here. Press Okay, and this
is what you have. Let's apply and you can see that for D, you get
yellow as well. Let's just duplicate
this out here, and for E, I want
it to be the same. So let's just go around and for Es going to be
yellow right here. But for F, it is
going to be red. So let's just go ahead and duplicate out the
rule right here, just like this, and
then I'm going to go ahead and double click
this out right here. And then for this one, what I want is F. So I'm just going to go
around over here, F, format this out, and choose
a red color right here, just like this and press Okay, apply, and then this
is what you have. So let's say, for example,
if someone gets F, you get the color
code right here as red as you can
see right there. So now I want it
to be replicated around over here in
overall grade as well. So to do that, I can simply go ahead and copy any one of these, and then I can go
ahead and paste the formulas format right here. So let's just go to format, and over here you
have this as well. So if I were to
go around onto F, you can see that the same thing applies over here as well. Let's just go ahead and
remove this out rat here, just like this and
so forth right here. So let's just press
Control Z and get back. So this is what you
have as you can see, and now for pass and fail as
well, I want to do the same. So I'm just going to go
around over here onto conditional formatting
rot and then I'm going to go around over here onto
mans rules right here, and then I'm going to add
in a new rule right here. So format the cells
that contain. Let's say, for example, I'm
going to go around and say, let's say, for
example, equal two, and I'm going to go
ahead and type in pass. So if it is passed, then it's going to be green right
here, just like this. Press Okay, and that
is what you have. And now I'm going to
duplicate up the rule, and then I'm going to
go to Edit rule right and then go round over
here onto fail as well. And for this one,
I'm going to go ahead and add in
a red and apply. So this is what you
have, as you can see. So if I were to
type in, let's say, for example, six t here, then you can see that
it says fail right here and there's a color
code here, just like this. So this makes it more visual. So the result that you have is more visual, just like that. And that is how you can use
conditional formatting to highlight results as
you need in Excel. And we have our result
ready right about now, and we can go ahead and
utilize this and enter out other students details
as we need as well. So as we move along,
we're going to see how we can
actually go ahead and finalize our result and even
see how we can print it out, and then we'll carry
on forward with saving this result as a
template so that we can use it with
other students as well.
9. Previewing and Printing the Result: So now let's go ahead and
preview out this result, and then let's see how to save
it as PDF or print it out. So over here you can see that
I got signatures rat here, which I filled with dummy, but let me just go
ahead and remove it. And let's say what I want is an actual picture of
signature Rot here. So to do that, I'm
going to go to Inset, and then I'm going to
go to picture and place inset and inset from
this device R here. So once I actually choose that, it opens up the explorer window. So now I can go ahead
onto my desktop. Let's see, for example,
and choose the picture of the signature and click
on Inset. And there you go. There's the signature R
similarly over here as well, I'm going to go ahead and
do the same place in sell, and then I'm going to
choose this device and select out the second
signature and click on Inset. There you go, there's
the two signatures right here just like this. Or you can go ahead and leave the parents signature
blank as well. If you just want the teachers signature right there so that the parents of this
specific student can sign it later on. So let's just do this here. I'm going to press Control S R here to save this
particular result out. And now, what I'm going to do is I'm going to go ahead
and print this out. So to do that, I'm going
to go around onto FL, and then I'm going
to go to print, and you can see the sample here. So we actually use the pace
view from the beginning, so you actually see how
the space will be printed. So if I were to go around
over here onto Print section, you will be able to see how this actually looks right here. And now this is the
preview that you have, and you can also choose the size of the
paper as you want. Like, let's say, for
example, A four size. But once you choose A four size, you can see that you
don't have the S anymore. So in that case, you can go
ahead and now adjust it out. So let's just go ahead and
adjust it out right here, drag this out as well, just like this right here. And then this is what you have. As you can see here, you can go ahead
and adjust this out right here. And this
is what you have. If I were to go around on
file and then onto print, now this is what you have,
as you can see right here. There's a result, and it says, keep on studying right here. But there's a bit of a space, so you can go ahead
and change it out. So let's just go ahead and
pull this out right here, just like this and maybe drag
this down a bit right here. Drag this up and
then I'm going to go ahead and drag this down
a bit here as well, just like this, as you can
see and change it out, change the outlook
according to how you want. So now I'm simply going to go ahead and then drag
this out here, and this is what you have, as you can see, just like this. If I were to go
around over here, let me just go ahead and
press Control S to save it, and then inFil I'm going to
go to print and you can see that the border is now
more to the rat here. Let's say this is
the one I want. You can go ahead and choose, let's say, for
example, uh, printer, physical printer
attached, and you can choose the paper size
right here and print it. Or you can go ahead
and simply choose Microsoft Print O PDF to save
it out as APDAFL as well. You can go ahead
and click on Print, and then it actually prompts
you to save A PDF FL. So I'm just going
to go ahead and say result sample right
here just like this. Alternatively, what you
can also do is go to FL and then go to save as
right here just like this. And then around over here, let me just go to my desktop. You can choose PDF around
over here as well. So you can see that this
is what I have right here. So let's say I'm
going to select PDF and I'm going to say result
Template two right here, just like this and press Save, and that will be saved
out as a PDF file. So now what you can do is,
let's say, for example, I saved it out in my desktop, and these are the files
that I have right here. So let's just go around and
double click and you can see the result in PDF file
format just like this, as you can see that you can
go ahead and print it out. And if you want to
print it, it would be printed out just
like this as well. Same goes with the other
file, as you can see, you can double click and you get the same result here
just like this. And this is how you can make an overall result using Excel that's
automatically calculated. And now in the next video, we're going to see how we
can actually go ahead and save this result as a template
so that we can use it. So we can use it for other
students as well as needed.
10. Finalizing the Template and Result Sheet as a Template: So now that our
result file is ready, let's just go ahead and save it out as a template so that I can enter out marks of
new students as needed. So let's just go
ahead and remove out these kind of details
like symbol number, section rot here, and grade, and I'm going to go ahead and remove out the name as well. Then I'm going to go
around and remove out all of the marks right
here just like this. And that is it, as you can see here. So now I got a blank
slate for my result. So now, in order to save
this out as a template, you need to go around
over here onto file and then go ahead and
then choose Save as. So now you can simply go ahead and choose where
you want to save this. So let me just go
ahead and click on Browse right here and save it out as result template right here on my
drive of choice. So now let me just go
ahead onto Save as type, and over here, what I want
to choose is template. So it says Excel
template right here. Let me just go ahead
and choose it. And then it actually goes to the template section right
here. For easy access. Let me just go ahead
and click on save, and then the
template gets saved. So now, let me just go
ahead and close this out. So if I were to go around and
close this particular seat rot here and want to use this template in order
to create a new result, I can simply go around onto Fle. Then I can go around
over here onto new, and then you can simply
go to personal section rot here and you can see the
result template over here. You can simply go ahead
and click and then that result template opens up
right here, just like this. Now you can fill in with
the detail as needed. Let's say, for example,
I'm going to say, Robert Benard right
here just like this, six and C and then symbol number is going to be
something else right here, just like this, and I'm going to fill it up with some
marks right here. Let me just not fail the students right
here, just like this. This is what you have, as
you can see right here. You got the result,
as you can see, just like this, and now what you can do is
you can save this up. If I were to go ahead and then
save this right here, uh, you can see that I can
go ahead and browse certain section as I need, and I can simply
go around and say, Robot result here,
just like this. Now if you want to
create a new result, simply go ahead and
follow up the step. You simply go around
over here onto new personal and then choose
result template once more, and a new result template
will lower that. And that is how you can finalize
out the template and use the result set as
template inside of Excel. And by saying sms, we have now come to
the end of the course, and now it's your turn to
create a similar project.
11. Course Project Description: So now that you have completed your lesson and we have made
our results just like this, it's time for you to
put your skills into action now and create your
own results just like this. So in this project, what you need to do is just like this, you need to create a fully
functional results seat for one student using
Microsoft Excel as needed. And then you'll need to begin by planning a simple and clean
layout just like this. So utilize the pays
view right here. So go around over
here onto view and make sure you use the
pays layout itself. Uh, and then what
you need to do is take a dummy student name
and in a clean layout, enter the student details and subject marks so that it's
easy for you to work with. Then what you need
to do is apply out the different
formulas just like this, as we learn in this
particular course. So what you need to do
is you need to go ahead and utilize if conditions
just like this, to assign grades as
needed and also create a pass fail condition by taking all subjects
into account as well. So make sure you use the conditional
highlighting as well. For example, make sure
that for certain grades, you get certain highlights
right here, just like this, as you can see right here to
make it visually appealing. And finally, you need to format the sheet for
printing as well. So add in boards just like this and maybe add
in colors here, and then even go ahead and save out a PDF
document as needed. So now, um, finally, once you format out everything, save it out as a
template, as well. And once you're done,
export out your project as an Excel workbook and also
a template file that is XT XLTXfle and
also a PDF preview of a sample result and submit these files to
complete your project. And this is a great way to
reinforce what you blend and walk away with a
useful real world tool. So I can't wait to see
what you come up with. So go ahead and start making
and start submitting.
Pratik Pradhan, Digital Artist
