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-course-on-excel
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.

Highlight-results-below-70-course-on-excel
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-course-on-excel
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.

Final-list-filter-by-colour-course-on-excel
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.  https://www.stl-training.co.uk/microsoft/excel-training-london.php

 

How to shade alternate rows in Excel – format as table

Here is a quick tip for customising your Excel worksheet.

I like my Excel worksheets to have alternate shading or colours so that it is easier for me to read across rows.

Now, I could set these up manually, by shading in my rows, but if I use sort, or add new rows, the shading will not be alternate. I will get some kind or random stripe effect.  What I want is Excel to do, is automatically apply shading to each alternate line, even if I add, or remove a row.

So here is a quick way to set it up

I have a list of students who have all taken an Excel exam.

student list plain format excel intermediate training
Here is my list of students. Plain format, so easy for me to read across lines incorrectly.

To make it easier for me to read, I want to add shading on alternate rows.

I select the information I want to shade, including the header, and go to the Home tab, and select Format as Table and select a colour scheme from the options given.

format-table-in-excel-intermediate-training
The Format Table button offers loads of ready made formats to choose from.

I then select the colour scheme I want, and I get a message from Excel confirming the cell range, and whether my list includes headers.  My list does, so I click ok.

format-table-message-excel-intermediate-training
Excel prompts me to check that my cell range is correct and to confirm if my selection has a header row.

I can then click out of the selection range and Excel has applied my colour scheme.  I can now add student details, or delete rows, and Excel will automatically update the format for me.

I have to admit, I’ve been wanting to be able to do this for ages.  One day of intermediate excel training boosted my skills and made it far easier to use the data in my worksheets.  If you are feeling curious and want to learn more, take a look at our courses https://www.stl-training.co.uk/excel-2010-intermediate.php.