Microsoft EXCEL Laboratory Exercise – CREATING A SIMPLE GRADE SHEET
1. Open a blank workbook
2. Write the following entries into the specified cells:
3. Select the cell G4 and write FINAL AVERAGE.
4. Select the H4 and write REMARKS.
5. Merge and center the cell from A1 to H1, A2 to H2. Apply the following changes.
Change the row height of row 1 as 60. Wrap your text and choose middle align.
Change the row height of row 2 as 25. Wrap your text and choose middle align.
Change the font, font size, and font style of your row 1 as Times New Roman, 20, bold.
Change the font, font size, and font style of your row 2 as Times New Roman, 14, bold.
6. Select the cell A4 to H4. Change the font, font style and font size as Arial, 12, BOLD.
7. Select the columns from A to H and apply AutoFit Selection for these columns. Use center
alignment. For these columns.
8. Enter the information below in the cells indicated. This will establish the weight each exam
is given in a student's final average.
A39 – AVERAGE (Change the font, font size, and font style as Arial, 14, bold.)
A40 – WEIGHT (Change the font, font size, and font style as Arial, 14, bold.)
B40 - 25%
C40 - 25%
D40 - 25%
E40 - 15%
F40 - 10%
9. Select range B39:D39 and G6:G37 and format your cell by removing the decimal places and
making it equivalent to zero.
10. Find for the average of each criteria using the average function.
11. Calculate your FINAL AVERAGE by multiplying your criteria to each weight then add each
product to get the final average.
12. Select address A42 and write Lowest Score. (Change the font, font size, and font style as Arial,
14, bold.)
13. Select address A43 and write Highest Score. (Change the font, font size, and font style as Arial,
14, bold.)
14. Select B42 and find the lowest grade using a function.
15. Select B43 and find the highest grade using a function.
16. On your REMARKS, use an If statement, using the condition if average is less than 75 it should
have a remark of FAILED while if the average is greater than equal to 75 it should have a remark
of PASSED.
Note: The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. To place an
IF statement inside another IF statement is referred to as “nesting.” Excel allows you to nest up to seven IF statements.
17. Select the cell I4 and write GRADE. (Change the font, font size, and font style as Arial, 12, bold.)
18. Select column GRADE, using the Vlookup function, find the equivalent of your final average using
the following data.
19. Select your column FINAL AVERAGE and apply Conditional Formatting so it Highlight
Cells containing values Less Than 75 with a red fill with black text.
20. Select your column REMARKS and apply Conditional Formatting so it Highlight text
containing FAILED with a red text.
21. Use center alignment for your range B6:I43.
22. Select range A39:I40 and use GOOD as a style to your cell.
23. Select range A42:I43 and use NOTE as a style to your cell.
24. Add borders for your table. Select address A1 to I43 and use all borders. Make the outside border
thicker than the inside border.
25. Select the address B45 and insert a comment. (Write any comment about your activity)
26. Add a Header using your COMPLETE NAME. (Ex. LASTNAME, FIRSTNAME,
MIDDLENAME).
27. Add a Footer using the Date of your submission. (Ex. January 07,2020).
28. Change the page orientation as Landscape.
29. Make your data fit in one page only.
30. Finally, put a password to your file and save your file with a file name of GRADE SHEET.