I am sorting pupils grades, and would like to show their progress in a line graph. The only problem is that the grades are not numerical.

I would like it so that whenever I put in the grade, it is assigned a numerical Value for the chart.

So, the lowest grade is P1c So would like that to be (1) then P1b (2) P1a (3) P2c (4) etc.

Is this possible?

This is what I have tried so far


Then the look up is on a seperate tab

Look up table


You need to change your formula to:

=VLOOKUP(D13,'Look up'!A1:B27,2)

VLOOKUP parameters are

(lookup_value, table_array, col_index_num, [range_lookup])

---{where range_lookup is optional}

You had FALSE as the col_index_num parameter, which equates to 0, instead of 2. The second column of your table_array range is where you want to look for the numeric equivalent to your grade.


enter image description here

How it works:

  • Write this Formula in cell G127 to get Score for respective Grades in Sheet 1 & fill it down.


  • Select the Blue line in Graph's Plot area & Right Click the Mouse.
  • Click Add Data Labels & finish with Ok.
  • Click anywhere in Graph's Plot area and click Right Mouse & hit Select Data command from menu.
  • From Select Data Source dialog click Add button & for Edit Series Input Box select F127:F134 (Grades in Alphabets).

You find the Graph, is looks like shown in Screen Shot.

Adjust cell references in the Formula & for Graph Data Series as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.