Conditional Formatting in Excel - Averages

 - Have a look again at the student scores spreadsheet from an earlier tutorial:

If you haven't got this spreadsheet, download it using the link below 

Student Averages Spreadsheet

When you open up the spreadsheet above in Excel, click File > Save As and save it with a different file name, as you'll be needing another copy later.

What we're going to do is some conditional formatting based on Average scores. We'll have one colour for those scores that are above average and another colour for those that are below average.

When you're done, the spreadsheet will look something like this:

With the Conditional Formatting applied, you can easily see that Mary achieved above average scores in all subjects (red). You can also see that poor Mark, meanwhile, has lower than average scores in all subjects (green). This information is not readily apparent without the Conditional Formatting. So how is it done?

Start by selecting the scores from cells B2 to I9:

With the scores selected, locate the Styles panel on the Home ribbon at the top of Excel. From the Styles panel, click on Conditional Formatting, then select Top/Bottom Rules > Above Average:

You should then see the following dialogue box appear:

The default colour for the cells is Light Red Fill with Dark Red Text. If you want another colour, click the arrow on the dropdown box to see more options:

If you're still not happy with the colour range, select the Custom Format option at the bottom. You can then set up a custom colour like you did in a previous lesson.

When you click OK on the dialogue box, your spreadsheet should look like this:

With the scores still selected, go to Home > Styles > Conditional Formatting again. This time from the menu, select Top/Bottom Rules > Below Average:

When you get the dialogue box up, select a different colour for the Below Average scores:

For the Below Average scores, we've gone for a Green Fill with Dark Green Text.

Click OK to apply the formatting. Your spreadsheet will then look like ours:

Exercise

Open up a new copy of the Student Scores spreadsheet, or just Undo the formatting you already have by clicking the Conditional Formatting item on the Styles panel. From the menu this time, select Clear Rules, and then clicking on one of the options:

Now set up a new Top/Bottom Rule. Colour only the top ten scores. Using a different colour, highlight the bottom ten scores. Your spreadsheet should look something like this, when you're done:

(You should notice the Excel colours 11 scores rather than 10. It does this because some of the scores are the same.)

In the next lesson, you'll see how to format cells so that they have bars in them.