Excel Training

Use conditional formatting to analyse data in Excel

An experiment using conditional formatting…

I have a list of fictitious students who have taken an Excel exam this morning. The pass mark was 55, but I want to help the whole group to achieve a Grade B with a pass mark of over 70%.

I want to be able to find who needs to attend an extra workshop to help boost their skills and get them to 70%.  I can identify the students quickly by using conditional formatting.

Here is my list of students and their grades

List of students and their exam results.

I want to find all those students whose grades were under 70.  I select my table, and then, use the Home tab, Conditional Formatting, and select Highlight Cells Rules, then select Less than.  A dialogue box appears and I need to put in the value.  I type in 70, as I want to know all the scores below 70, and press ok.

The dialogue box appears and I can type in the score. Excel will then identify all the results less than that value.

I now know, that out of 15 students, 13 would find a workshop helpful to boost their skills.  I can now set up the room, and the trainer can target his course on Excel to help the students reach their target score.

I can use the filter in my table to give the trainer the names of the students for his session.

I go to the filter arrow on the results column and select filter by colour

Filter by colour – is a quick way for me to create a delegate list for the session.

Here is my workshop attendance list, with the 13 students names.

Here is my final list.

This is a quick way to experiment with analysing data using conditional formatting, and using the filter to pick out the data you want (and hide the data you don’t need at that moment).  A course on Excel is a convenient way to upgrade your skills and experiment with data before you try it out in the workplace.


By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.