By this stage of the school year the marks are beginning to come in and the old mark book needs readying for the new data. I know teachers who make elaborate cut-outs to try make the mark book more efficient and easy to use.
One colleague even has the recipe stuck into his lesson-planning manual so that he can make new mark books each year.
But perhaps it is time to dump the paper version and go digital. One advantage is that you can use the processing power of the computer to manipulate the marks. When the principal asks for your term marks to be adjusted by half a percent across the grade – don’t laugh. I’ve worked for such a man – it’s a simple case of asking a spreadsheet program to do the work for you.
So this month we will investigate setting up a simple mark book in Microsoft Excel.
- Open the Excel program (click Start/ All Programs/ Microsoft Office / Microsoft Excel 2007).
- The grid that loads on to the screen is known as a workbook. The grid is divided into cells. Each cell has an address or coordinates. The first cell, for example, is A1. This means that the cell is in the first column called A (columns run vertically) and is in row 1 (rows run horizontally).
- All labels or words are by default aligned to the left. If you want to insert numbers as labels (for example, class name ”9Y”) then you insert an apostrophe before typing in the numbers. All values (numbers) are by default aligned to the right.
- In cell A1 write the class code, for example Grade 9Y.
- Use Row 2 as a heading row and insert the labels you require to remind you what the figures in each column are. In A2 write Surname, A3 Initial, A4 Test 1, A5 Test 2 and so on. Insert as all the headings that exist at this stage of the year. You can insert new headings as the year progresses.
- In Row 3 insert the value of each test, for example, total 50.
- Then list all the class members’ surnames in Column A, initial in Column B and test results, if you have any yet, in Columns C, D. Your page should look like the one above.
- Often the size of the column is too small for the label or value that needs to be placed in it, as in Column A in the example above. To resize the columns, place the mouse directly on the division between the column descriptors, for example between A and B. The mouse cursor changes to a double-sided black arrow. Double click the right mouse button and the column will be resized to fit the largest cell. Now resize all the columns that are either too big or too small.
- To find the total of a set of marks select the cells with marks in the first column; that is from C4 downwards. Click the left mouse button and keep it pressed while you drag the mouse down the column or row. You will know if you have been successful as the column turns black once selected. Continue down to the last value and include one extra empty cell. It is in this last cell that the answer will appear.
- With the column selected press the ”∔’ button at the top of the screen. In Excel 2007 the icon reads ”− AutoSum”. The total of the column will appear in the extra cell. Type ”Total” in the same row but in Column A.
- You can find out the sum of a series of cells by typing in the following formula where you want the answer to appear, =SUM(c4:c29). The formula means: use the function sum to add the contents of the Column C from row 4 to row 29. If your class is bigger or smaller than 25 you will need to adjust the second row number to the person in your workbook, for example.
- To find out the average for the first test select the cell directly under the column sum. This is where the answer will appear. In this example it would be C16 but you need to check your own workbook. In this cell type the following formula: =AVERAGE(c4:c14). What you have asked for is to use the average function to determine the average value in Column C from row 4 to row 14.
You might again need to change the last row address to suit your particular workbook. If you are working in Excel 2007 then you can follow a procedure of the AutoSum described in 11 above, but instead of choosing AutoSum click the drop down box next to AutoSum icon and select ”Average”.
- Now clean up by typing ”Average” on the same row but in Column A and make both label and values bold. Your mark book should look similar to the one above:
Excellent work. You have made a good start in terms of learning to use Excel. Now you can see how a spreadsheet might enhance your class administration.
If you want to know more, try these free online tutorials: http://office.microsoft.com/en-us/training/CR061831141033.aspx or http://www.baycongroup.com/el0.htm
Andrew Moore is a former teacher. He has a MEd in computer-assisted education. He works for Neil Butcher and Associates, an education technology consulting company