Making a Marks Book in Microsoft Excel
This week, we will being looking at another program within Microsoft Office, Microsoft Excel.
Microsoft Excel is useful for many things. The main features that we are going to investigate are drawing tables, using formulas and drawing graphs.
We will begin by looking at a very simple example that your teachers will be very familiar with, making a Marks Book. The Marks Book will be for a single subject, Maths, for a fictional Year 6 class, Year 6Z.
Below are the steps to make the Marks Books. Each of the steps will be demonstrated at the front of the class, but if you are confident in your skills, you may follow the steps at your own pace.
- Go to the Windows Start Button and open a new Microsoft Excel document.
- Go to 'Page Layout' -> 'Orientation' and change it to 'Landscape'.
- Go to 'Page Layout' -> 'Margins' and change it to 'Normal'.
- Change the width of Column A to 15.0.
- Change the width of Columns B through to L to 8.5.
- Change the height of Row 1 to 30.0.
- Change the height of Row 2 to 97.5.
- Change the height of Rows 3 through 17 to 24.75.
- Change the font of all cells to Calibri, font size 14
- Select cells A1-K1 and click 'Merge and Center'
- In cells A1-K1 (which were merged together in the last step), write the title 'Maths Results 6Z - <Your Name>'. Write your own name instead of <Your Name>. This way we know who owns each Marks Book when they are printed.
- Select Row 2, Right Click (on one of the highlighted cells) -> Format Cells -> Alignment and change the Orientation to 45 degrees.
- Select A2 - K17, go to the Border Tool and select 'All Borders'.
We have now set up the document ready to enter all the Students' grades. The Students' grades will be on a seperate photocopied sheet to make it easier to enter. The next few steps will show you where to start.
- In B2 write 'Numeration', in C2 write 'Fractions', in D2 write 'Decimals', in E2 write 'Percentages', in F2 write 'Angles' in G2 write 'Order of Operations' and in H2 write '2D Shapes'.
- In A4 write 'Amanda', in A5 write Bridget, in A6 write Debra. Continue with the rest of the student's names. Your final name, Stan, should be in A17.
Now that your topics are along the top and the student's names are down the side, fill in all the students' marks from the photocopied sheet.
We will now adjust the alignment to make the page look more attractive.
- Select cells A4 to K17 and change the alignment to 'Middle Align' and 'Center'. This is done just the the right of where we changed the font size.
- Select cells B2 to K2 and change the alignment to 'Bottom Align' and 'Center'.
Now that you have all the students' marks entered, we will now determine their overall percentage. Excel can do this very quickly using formulas and means that we don't have to use a calculator to find the average mark for each student.
- In J2, write 'Overall'.
- In J4, write the following formula EXACTLY (or it will not work properly) without the quotation marks '=AVERAGE(B4:H4)'.
- Select cells J4-J17 and press CONTROL-D. This will copy the formula down the page for all the students.
We will now check to make sure that it will fit onto a single page when printed. DO NOT PRINT YOUR MARKSBOOK!
- Go to the Office Button, scroll down to 'Print' and select 'Print Preview'. DO NOT PRINT YOUR MARKSBOOK!
- Check to make sure that all your marksbook fits onto a single page. If some of the 'Overall' border is cut off, that is fine. If it does not fit onto a single page, reduce the size of your columns and/or rows so that it does fit onto a single page.
If time allows, we may look at a formula that calculates a students 'letter' grade from their percentage. This will only be done if the majority of the class has finished their marksbook and there is spare time at the end of the lesson.
NOTE:- You are not expected to know how to write your own formula for this. The formula will be given to you.