How to Change Grades to Numbers in Excel

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 STARTER Excel file for this video.

Click here to download the COMPLETED Excel file 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.

Match a number to a range in Excel thumb

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.

The final step is to copy the formula across to the second grades column, and to implement a simple arithmetic formula to establish the difference between the grades. And that’s it! You have combined VLOOKUP with a table and a simple arithmetic formula to find the difference between two student grades.

Did you get the difference between two letters in Excel? Let Chris know in the YouTube video comments, he will get back to you.


FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.