How to create a student result sheet in Excel

By

In this tutorial I'm going to show you how to use the IF function (logical test) with the True or False value. Also, how to use symbols in excel.



   1. For us to use the IF function and Tick symbols, firstly we have to create a basic student data sheet where you can type the necessary information such as: Student name, subject, score, status etc. The first step for you to do is write down a simple student sheet with the as seen on the picture below. 




   2. Step two, on the Student column select the cells from A2 to A10, right click > format cells click on the merge cells box, then set the orientation to 90 degrees and click OK.






   3. Once you've merged the cells, write down the name of your student and make sure to align your content and text to center and middle as seen on the picture below. Also, make sure you change your font size to your liking as well.  




   4. Next step, write down the scores of your student on each of the subjects.




   5. Now the critical step towards this tutorial is for you to write a logical IF function with a True or False value. And how can we do that? Simply, make sure you set the output on the D2 cell so the formula will take effect in order. Next, write down this formula: =If(C2>=75,"PASSED","FAILED")

What we did with this formula is that we used an IF function to determine a logical outcome depending on the information we provided on the table. Next, we open the parentheses and we select or write down the C2 cell that contains the score on each of the subjects. What we do next is we've set the criteria to greater or equal than 75 (>=75). Once we put a comma we already told the computer, in this case the IF function what to do. Now we continue by setting the True or False value. Take note that the True value always comes first, so we open and close the True value with a double apostrophe, " " . In this case what we want as a True value is a PASSED outcome if the exam score is 75 or higher. Once again to move to the next step, we use a comma for that and we start using the double apostrophes for the FALSE value as well where inside the double apostrophe we write FAILED as our outcome if the score is 74 or lower. Finally we close the parentheses, click enter and we are all set.  




   6. Once you've pressed enter you should have a PASSED or FAILED outcome on your student status. To do the same for the rest of the subjects, simply drag and drop from the D2 cell in order to get an automatic result based on the formula we've just put. Note that now you can change your subject scores and the formula will take care of the rest whether the student has passed or failed the exam based on the criteria we've set, which is (C2>=75). 




   7. Now that the table or student data sheet is complete, it's time for us to put and format the tick symbol, on the score column. To do that, head over to Conditional Formatting > New Rule.




   8. Once the new formatting rule window opens, there is a series of changes that you have to make. Firstly, click on the Format Style and set it to Icon Sets. Next, click on the Icon Style and select the Tick symbol. Under the Icon category, change the ticks that you want to be shown on the column, also on the third symbol category set it to No Cell Icon. Finally, under Value set it to 75 and 0 as seen on the picture below and don't forget under Type, set it to Number for both categories. Click OK and the symbols will appear.





   9. Now that the student data sheet is complete you can add a bit of a design element depending on your liking. What I've done is selected a header design and under Borders > Draw Border I've manually drawn borders on the side of the data sheet to give it a more complete look.




   Here's an idea on what the table or student data sheet will look like once we've done all of the steps mentioned above.




   10. It's not necessary, however, you can always convert your student data sheet into a table. By doing so, you're going to have a much wider access to your student data base, as you can see on the picture below, you can select a particular, subject, score or status to be shown on your table.




   11. If you wish to add more students to your data sheet, you can either drag and drop the table for extension, or for a more precise solution simply, copy and paste the subject column just under the data sheet. Format and merge the cells on the student column and write down the name of your next student.




 Hopefully, by now you have a clear indication on how to create a student data sheet so you can start from the beginning and create one for yourself depending on your needs and priorities.




Here's also a video showcase of this tutorial.

Comments

Post a Comment

Popular posts from this blog

How to create a barcode using Excel

By
Image
Hello everyone and welcome to another tutorial where we are using Excel to create or generate a barcode using very simple few steps. Before starting with this tutorial make sure you have downloaded a barcode font where we're going to use it in the next steps. Finding a barcode font is very easy, just google the font and you'll be able to download various barcode fonts.     1. First step, enlarge the A and B column so that the barcode would fit in. By doing so, in the A column select the number format as Text.     2. Next, under the A column, we have the serial number ID that will generate the barcode (you can also use letters instead of texts).    - Use the formula to generate the barcode: ="*"& A2 &"*"     3. After you've entered the barcode formula drag and drop so it can automatically generate the barcode for the rest of your serial number ID.     4. At this stage, select all the gen...
Read more

How to create a simple database in Excel

By
Image
In this step by step guide, I'm going to show you how to create a quick and simple database using Microsoft Excel. Even if you've just started using Excel for the very first time this tutorial doesn't require any intermediate skills, all you have to do is follow the logic behind the steps that will create this database function. For this database we're going to use the DSUM, DAVARAGE, DMAX and DMIN functions.      1. Step one, have an idea on what your database will consist of, in my example I have used a simple smartphone database. So, from cells A6 to F6  write down the categories on what your database will consist of and whats you did that make sure you highlight the categories with Bold and fill the cells below with the required information. As you can see from the example below I have filled the necessary information to each category.   - Once you're finished with the first step, note that as seen in the example below in the highlighted cells...
Read more

How to create an actual cell vs target graph in Excel

By
Image
Hello everyone and welcome to another tutorial where this time we're going to see how you can make a very quick and simple actual cell vs target graph in Microsoft Excel in just a few easy steps.      1.   First step, fill in the cells as seen on the picture bellow and write down the benchmark of units sold on the target or goal cell. Now that we have the benchmark of 100 units sold as our primary goal, head over to the actual cell and write down a random or in your case the actual number of units sold. Once you've done that on the graph cell type =D5  as seen on the picture where we will use the formula to show the graph in the next step.     2. Step two, once you click enter you will notice on the graph bar the progress is 50 units of the 100 target units sold. To add the graph go to Conditional formatting > New rule     3. When the New Formatting Rule window opens head over to Format Style and set it ...
Read more