Conditional formatting for non-alphabetic data

Hi there,

I have managed to sort some data according to non-alphabetical criteria. Specifically, I have sorted a range of data for children's attainment levels where 4E (year 4 emerging) comes before 4D (year 4 developing), which comes before 4S (year 4 secure), etc. So I have used 'Custom List' to insert the levels, like this: 3E, 3D, 3S, 4E, 4D, 4S.

Is there a way to use the same principle in conditional formatting? For example, if column B has data for each child for July and column C has data for September, I'd like Excel to highlight in column C the levels that have gone down (e.g. from 3S to 3D), the ones that have stayed the same, and the ones that have improved (e.g. from 3S to 4E).

Thank you in advance.

All the best,

David

So if you have your levels written in a range, and sorted, then you can use the Match function to get a numerical value for a given level.
Then you can use standard If x>y type of functions..

Example

Thank you for your reply. I have (as I should've done in the first place!) attached an example of what I mean. I would like to see if the levels for each child are better than, worse than, or the same as, in September compared to July.
I'd like to highlight, in column C, any levels that are higher to be green, the same in yellow, and worse in red. Does the attachment help you figure out how to do what I'm asking?

Your help is much appreciated!

It's not clear which levels are Higher than others.

Your sheet will need to include a list of all the levels, sorted in Descending order (Highest level on top, lowest on bottom)
Let's say that list is in H2:H7

Higher:
=MATCH(C2,$H$2:$H$7,0) < MATCH(B2,$H$2:$H$7,0)
Lower:
=MATCH(C2,$H$2:$H$7,0) > MATCH(B2,$H$2:$H$7,0)
Same:
=MATCH(C2,$H$2:$H$7,0) = MATCH(B2,$H$2:$H$7,0)

It's kind of what I want, but not quite. Please forgive me for not understanding - I wouldn't consider myself an advanced user of Excel.

3E (year 3 emerging) is lower than 3D (year 3 developing), which is lower than 3S (year 3 secure).

I've attached a new Excel file that is how I want it to end up looking. (I have changed the fill colours manually.) Hopefully that will help!

You will need to 'CREATE' a list that has all the levels sorted in order, either from Highest to Lowest, or Lowest to Highest.
And that list needs to be written in a range of cells on your sheet.

Okay, I have sorted the data now, using the own list I created. (See attached.) How do I now get Excel to colour the boxes according to who has gone backwards (red), who's stayed the same (yellow) and who's improved (green)?

I don't see a list anywhere that tells me which level code is higher or lower than the others..

Please create this list, and physically write it in a range of cells in your sheet. Similar to how I did in post #2.

Okay, I have now done that and attached the file.

OK, great.

So now you make 3 conditional formatting rules.

Set for Color Yellow
=MATCH(C2,$E$2:$E$7,0) = MATCH(B2,$E$2:$E$7,0)

Set for Color Green
=MATCH(C2,$E$2:$E$7,0) > MATCH(B2,$E$2:$E$7,0)

And for Colo Red
=MATCH(C2,$E$2:$E$7,0) < MATCH(B2,$E$2:$E$7,0)

I have no idea what the formula actually means, or why it works, but it works! Thank you so much for your help and patience.

You're welcome.

Match finds the value of C2 within the Range E2:E7. And returns a number representing the position where it was found in that range.
So if C2 was found in say E4, then Match returns 3 because E4 is the 3rd position in E2:E7 (E2 is 1st, E3 is 2nd, E4 is 3rd)

It then does the same for B2 and compares the 2 results.
Which one is larger, or are they the same..

Thank you for the explanation.