How to quickly convert grades to numbers in order to measure student progress in Excel?

How can you change grades to numbers in Excel? It’s something many teachers out there are trying to do in order to measure student progress. And it’s something I have been asked to do on projects numerous times! Learn how to do it, and avoid the need to hire somebody like me. Welcome to Never Hire Me.

Click here to download the Excel files for this video.

Excel cannot easily understand the difference between two letters. So, the best option is to convert the letters to numbers, and then to work out the difference between the numbers. The concept is simple enough, but how to actually do it in Excel?

First, we build a table to assign a value to a particular grade. It is sensible to position the table on another sheet for clarity; but, referencing other sheets in formulae can be tricky. So, we use a ‘named range’. Using a named range makes formula referencing much easier later – Chris shows you how to quickly set one up in the video.

With the first step complete, we return to the original sheet and implement a VLOOKUP formula. The formula ‘looks up’ a ‘lookup value’ (ie. the student grade) in a table, and returns a value from another column in the table that is on the same row. We work through the formula in the video, step-by-step – make sure you follow along with Chris and try to build it yourself.