How to create alphanumeric data chart in Excel

**Shaina Na** · 07-01-2012

I'm trying to put together a spread sheet that records the progress of pupils through the year in 3 subjects over 3 terms. I'd like to create a line chart for each pupil showing a line for each subject, Y axis as level and X axis as term (1, 2 & 3). The problem I’m having is that the levels are alphanumeric: low to high in the following order: 1c - 1b - 1a - 2c - 2b - 2a - 3c - 3b - 3a. Any help with this will be gratefully received.

**Panop** · 07-01-2012

Unfortunately I can't think of a way to get Excel to recognize a1, a2...c3 as a sequential order of values and chart them accordingly. Your best bet is to replace them (in another worksheet if required) with numeric values and chart these instead (with a key to reference back to).In this way a1=1, a2=2, a3=3, b1=4, b2=5, b3=6, c1=7, c2=8, c3=9 And this doesn't mean you have to lose your a1, a2...c3 grades, keep those and use the formula below in a new sheet. To automatically replace the marks in your workbook with numeric values, you could do a VLOOKUP where you have a1, a2, c3 in column X and 1, 2 ...9 next to each in column Y. If Columns A, B and C hold the marks for the 3 subjects, in a new cell, or even worksheet, put in the formula: =VLOOKUP(A1,$X$1:$Y$9,2,FALSE) This will look in cell X for a value matching A1 and return the corresponding number held in column Y. Copy and paste this formula for each mark you have assigned on your original worksheet and each mark should be replaced with a number between 1 and 9 (anything outside of the a1, a2,...c3 will return a #N/A error). Once you have all of the numeric values on a new worksheet, you can plot these in a chart instead, and just create your own key to what the values 1-9 mean (or paste a text box over the Y-axis with the grades instead).

**Go!pee** · 07-01-2012

That only works if you just really want a1 to be at the top of the chart and c3 at the bottom, so your values should be:
a1=9, a2=8, a3=7, b1=6, b2=5, b3=4, c1=3, c2=2, c3=1 Where a1, a2,...c3 are in column X and 9, 8,...1 in column Y.