Friday, November 22, 2019

How to create a sample student report card in MS-Excel


Well, today we are going to learn how someone can create a sample student report card in MS-Excel. We will use some screenshot of our own generated demo report card and its looks very simple yet easy to make one.
So, without wasting valuable time of everyone’s, let’s proceed to the point right now.

Prerequisite

To create a student report card, you should have a clear understanding of different types of mathematical expressions (conditions……if-else) and should know how to perform some basic level mathematical operations, like- Addition, Average and so on. That’s set, isn’t it looks simple! Well, actually it is. So, let’s start,

At first, we are giving a screenshot of our completed Student Report Card & here it is,



So, we hope, you get the idea how your student report card will look like. (Take a closer look at the above screenshot please)

Create the basic layout of the report card like the title of your desired school name, the number of field required for the card, input values including Student_ID, Student_Name, Subjects & Marks etc.

For the demo, create this below layout first,



Here, you can see we have already input some demo values for our first six columns (from left to right) and now we have to calculate Total marks of those subjects entitled as English, Science, Math an ICT. So, how we are going to do that? Well, it’s just a simple addition operation.

In the Total column, we will take our mouse cursor and click the G6 cell first. Then, start typing “ = ” in that cell and start clicking from left side cell of subject values, for our case English is most left sided subject and has a value of 87 which is in the cell C6 then use the addition symbol (+) and select the next cell of the subject i.e. for our case Science and has a value of 77 which is in cell number D6 and similarly just select other cells of the subject values and just press the Enter button of your keyboard. 

You will get the Total marks of those subjects based on your input values. Or, you could just type the below formula in the G6 cell.



Now, just put your mouse cursor below in the G6 cell and you will notice a black + symbol there, just drag your mouse from the next cell G7 to the last cell of that table G11 and you will get Total marks of all other students in the table.

Now, it time to calculate the Average of marks of those individual students. We definitely know how to calculate average from some values, right!

Well, in mathematics, the formula of average,

Average =Addition of all values/ number of inputs

For our case, we can see, there are four (4) subjects and we already calculated the Total marks by performing addition of subject-wise values and we put the value in a new column titled as Total. So, it will save our time and effort to calculate the Average marks of students.


So, from above screenshot, it is easy to calculate Average marks of Students. For example, Average Marks of Student A = Total/4 (Here is only four subject enlisted) and same formula will work on other Students Average Marks Calculation.

From our report card, we will use this simple formula ( =G6/4 ) for calculating the Average marks of Student Name, A and for other Students we will just drag the ‘+’ symbol from the below of the H6 cell to the following cells.

Now, give your full attention, because in this part, we will learn how to calculate the Grades of those Students and for that we will use a little bit difficult formula.

For calculating Grades of the Students, at first simply we will use this formula, =IF(H6>=80,"A+",IF(H6>=70,"A",IF(H6>=60,"B",IF(H6>=50,"C",IF(H6>=40,"D",IF(H6<40,"F")))))) to find out the Grade of Student A.

Then, for other Students Grades we will just drag the ‘+’ symbol from the below of the I6 cell to the following cells.


And, for the last field of our report card, we will Comment out the Grades (it could be Outstanding, Good, Satisfactory based on performance) and we will do it in a manual way. That’s all for our demo Student Report Card for Beginners and hopefully, everyone will clearly understand the full process of generating a simple Student Report Card in MS-Excel in any version.

The final look of our created Student Report Card,


Thanks for your support. Have a good day & stay with tutorialabc.com for more lessons and learning contents.

Data Validation on excel

Today we learn how to validate excel  field. We will show you number restriction.

Create validation rule:

  • At first move on to  data tab and click data validation.
  • Here you several type data validation

  • For number restriction we select (whole number).

  • Enter minimum and maximum number. (For check   now min value 1 and max value 11)

Massage input:

It will appear when you select the box. It will show your inserted message.

  • Move on Input message tab.
  • Input message title and input message.

Error alert

When you insert Error message that will show you error alert during wrong input,


  • Set Error alert (title and error message).
  • Press ok.
  • If the value typed more than or less than that it will throw this error message what you save now.

Monday, October 28, 2019

How to create a Drop-down List in Excel (Absolute beginner)


Today’s lesson for absolute beginners so we are going to follow step by step process for this tutorial. So, what is today's tutorial? Well, it’s a very simple yet very essential topic to learn in Excel. 

In this tutorial, we are going to learn “How to create a Drop-down List in Excel” but at first, briefly we will discuss the reason to use a Drop-down list in Excel.







(Arrow is used to indicate the Drop-down button)

Take a deep look at the above image first. Here, you can see an ICON just below the Label named ‘Brand’ and this is the thing called Drop-down icon and by clicking this icon we will get a list for our specific work.

So, without furthermore delay, let’s roll to our today’s tutorial……………..


First, open you MS-Excel Program and choose a blank workbook. Then, choose your cell number where you want to create Drop-down list. 


It will be a good practice if you label the above cell with any meaningful name. Just like in the following image, we use Month & Brand for labeling the below Drop-down list and it’s really helpful to find out what does the list actually means. 




Next, in the cell where you want to create Drop-down list just select it and go to the Data section, here marked in a red circle.




Then, click the Data Validation tab again marked in a red circle and a new window will pop up like this image.




Form here, select the List value from the Drop-down menu and if you want to specify the list values then just type those values in the Source field – marked in a red circle in the below image.




Well, you just create your first Drop-down list in Excel. See the below image,


(You will get exactly the same type of screen if you exactly follow us)



Now, for your exercise try to build the following Drop-down list as shown in the below image. 




But, here we will not use list values manually. Here, we will use a dynamic way for the list input. So, take a closer look, how you can do such thing in Excel for a Drop-down list.

Initially, this process is almost the same as before. But, at first, you need to fill up some cells by inserting some data values, like the following image we insert some demo data for the label ‘Brand’.

We use some giant computer brands name for the purpose of this tutorial & only for this tutorial and we do not intend to promote those brands for any cause (check our Disclaimer page please).




Then, just select the red arrow marked cell and go to the tab named Data ® Data Validation and a new pop up screen will come. Then from the Settings tab of the new screen under the validation criteria select ® Allow: List and Source: just click the Icon at the right side of blank field. A red circle marked in the above image.

Just click OK and select the column header (for our case it will be L).  It will automatically select the range required for the Drop-down list values. Like the following image,



After that, just click the right side icon once again or just hit Enter of your keyboard and just click OK




You just created your Drop-down list dynamically. Isn’t it very easy?


For your clarification, here is the full process added here in a gif file. Take a look at it, now.




We suggest you to keep practicing and give more effort to learn anything newer to you. If our tutorial helps you in any way please share it with your friends & family.

And, have any question in your mind or have some opinion for us to improve the service, please feel free to contact us via our Contact page. It will be very much appreciable. Thank you for your support and keep learning through tutorialabc.com!