Creating a markbook using Excel

Excel provides you with the opportunity to enter your marks into a spreadsheet that will then calculate the percentages and determine grades. This can be imported into Synergy making report writing quicker and easier.

By far the easiest way to do this is to get a markbook ready-made from a department head. Many departments have already made these and, in order to use them, it is just a matter of saving it somewhere, entering the students’ names and then filling in the grades as you mark.

It is not difficult to create one of these markbooks yourself though. To do so you will need to know how to get Excel to work out percentages, use a table to determine grades and use an equation to make the overall grades. To do so, follow the instructions below:


The equation to create a percentage looks like this: =D4/25*100. In order to get there, do this:

  • Start with = so it knows it is an equation;
  • nominate the cell where you will enter the student’s mark (D4);
  • divide (/) by the total number of marks for the assignment (25);
  • times (*) 100 to get the percent

Fill down the column by clicking the plus at the bottom right of the active cell then drag this down the column and it will automatically adjust the formula for the other cells.


In order to get Excel to work out your grades, rename Sheet 2 to ‘VLOOKUP’ and insert a table with the scores and their corresponding grades then connect it to the appropriate cell with this formula: =VLOOKUP(H4,VLOOKUP!$A$1:$B$11,2)

The only thing that you have to change in this formula is to make the cell (in this case H4) lead to the right cell in your spreadsheet (the one that contains the percentage).

Fill down the column by clicking the plus at the bottom right of the active cell then drag this down the column and it will automatically adjust the formula for the other cells.

The Overall Percentage and Grade

To get to the overall percentage you just have to add the percentages together relative to their weighting. For two percentages of equal weighting, the equation looks like this:


Which means =(fifty percent (0.5) times (*) the mark for whatever (E4)) plus (+)(fifty percent (0.5) times (*) the mark for whatever else (H4)).

Unequal weighting looks like this:

=(0.25*E4)+(0.75*H4) where 0.25 equals 25% and 0.75 equals 75%.

Then you put in the VLOOKUP equation again to get the overall grade.

Good luck!


About timthelibrarian

Tim Harwood is a Teacher Librarian and eLearning enthusiast.
This entry was posted in Excel, Markbook. Bookmark the permalink.

One Response to Creating a markbook using Excel

  1. Tardy says:

    This is great. I’ve just started using electronic markbooks myself. Is it possible to make a cell which tells you how far a person is from the next grade? I presume you need some kind of SUMIF function followed by a list of numbers. I’ve tried messing around with this =LOOKUP(D2,{0,10,20,30,40,50},{“F”,”E”,”D”,”C”,”B “,”A+”}) to create grades, but how do I create a cell which will convert 46 in to a 4 (e.g. if you get 46 you’re 4 marks from 50, which is ‘A’)?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s