Get more Mail & Guardian
Subscribe or Login

Excel in Excel

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.

  1. Open the Excel program (click Start/ All Programs/ Microsoft Office / Microsoft Excel 2007).
  2. 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).
  3. 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.
  4. In cell A1 write the class code, for example Grade 9Y.
  5. 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.
  6. In Row 3 insert the value of each test, for example, total 50.
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. 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”.

  13. 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: or

    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

Subscribe for R500/year

Thanks for enjoying the Mail & Guardian, we’re proud of our 36 year history, throughout which we have delivered to readers the most important, unbiased stories in South Africa. Good journalism costs, though, and right from our very first edition we’ve relied on reader subscriptions to protect our independence.

Digital subscribers get access to all of our award-winning journalism, including premium features, as well as exclusive events, newsletters, webinars and the cryptic crossword. Click here to find out how to join them and get a 57% discount in your first year.

Andrew Moore
Andrew Moore works from ÜT: 43.663279,-79.38497. Andrew Moore, President of Governance Services at Computershare. Lives in the world of Tech&Compliance. All views are my own. Andrew Moore has over 549 followers on Twitter.

Related stories


If you’re reading this, you clearly have great taste

If you haven’t already, you can subscribe to the Mail & Guardian for less than the cost of a cup of coffee a week, and get more great reads.

Already a subscriber? Sign in here


Subscribers only

Fears of violence persist a year after the murder of...

The court battle to stop coal mining in rural KwaZulu-Natal has heightened the sense of danger among environmental activists

Data shows EFF has lower negative sentiment online among voters...

The EFF has a stronger online presence than the ANC and Democratic Alliance

More top stories

South Africa needs to make pension system more inclusive, study...

South Africa’s pension system is ranked 31st out of 43 countries, receiving a C-grade which indicates major risks and shortcomings that should be addressed

Fears of violence persist a year after the murder of...

The court battle to stop coal mining in rural KwaZulu-Natal has heightened the sense of danger among environmental activists

Data shows EFF has lower negative sentiment online among voters...

The EFF has a stronger online presence than the ANC and Democratic Alliance

Greenpeace investigation exposes countries trying to dilute climate report

Greenpeace team says it has obtained leaked records of countries asking scientists to water down upcoming scientific report on climate change

press releases

Loading latest Press Releases…