Skip to main content

Conditional Formatting in Excel - Averages

 - Have a look again at the student scores spreadsheet from an earlier tutorial:
An Excel spreadsheet showing student averages
If you haven't got this spreadsheet, download it using the link below 

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:
The student averages spreadsheet with Conditional Formatting applied
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:
The Conditional Formatting menu
You should then see the following dialogue box appear:
Format cells dialogue box
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:
Cell fill 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:
Red colour selected for the formatting
With the scores still selected, go to Home > Styles > Conditional Formatting again. This time from the menu, select Top/Bottom Rules > Below Average:
Conditional formatting menu - Below Average
When you get the dialogue box up, select a different colour for the Below Average scores:
Format cells dialogue box - below average
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:
The student averages spreadsheet with Conditional Formatting applied

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:
Conditional formatting menu - clear rules
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:
Conditional formatting with Top/Bottom rules applied
(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.
Labels:

Comments

Post a Comment

Popular posts from this blog

How to change your Windows XP Desktop Wallpaper - Wallpapers are images that appear in the background on your Desktop, behind all your icons. To change your Desktop wallpaper, do the following: Click anywhere on the Desktop that is not an icon, but click once with your right hand mouse button A menu pops up. The one below Click on  Properties , once with your Left mouse button The following dialogue box pops up This is the Display Properties dialogue box. As you can see, there are tab strips for Themes, Desktop, Screensaver, Appearance, and Settings. Click on the Background tab strip and the dialogue box will change to this one: There is a list of wallpapers we can use. Click on one, and you get a preview of what it will look like. Scroll down to see more wallpapers. If you're satisfied, click the OK button to set your wallpaper. You can use your own images. You are not limited to the ones on the list. If you have an image somewhere on your compute...
Post a Comment
Read more
Beginners PHP  -This is a complete and free PHP programming course for beginners. It's assumed that you already have some HTML skills. But you don't need to be a guru, by any means. If you need a refresher on HTML, then click the link for the Web Design course on the left of this page. Everything you need to get started with this PHP course is set out in section one below. Good luck! Home Page > PHP Section One - An Introduction to PHP 1. What is PHP and Why do I need it? 2. What you need to get started 3. Installing and testing Wampserver 4. Troubleshooting > PHP Two - Getting Started With Variables 1. What is a Variable? 2. Putting text into variables 3. Variables - some practice 4. More variable practice 5. Joining direct text and variable data 6. Adding up in PHP 7. Subtraction 8. Multiplication 9. Division 10. Floating point numbers > PHP Three - Conditional Logic 1. If Statements 2. Using If Statements 3....
Post a Comment
Read more
Finishing the Spreadsheet  - To finish off the Excel spreadsheet you have been working on in this section, we'll add figures for the weekly cost and yearly costs of the chocolate addiction. We'll use AutoFill and SUM. The bottom of our spreadsheet looks like this: We now have how much each individual chocolate bar is costing us each week. The next things to do is to add them all up to arrive at a weekly figure for all chocolate bars. To calculate the weekly cost of the chocolate addiction, you can use the Excel SUM function. But there's an even easier way - use Auto Fill and SUM. Try this. Click inside cell F20 Click inside the Formula bar at the top and enter   = SU When you see the drop down list of functions, double click   SUM Now click inside D15 of you spreadsheet Excel will enter the Cell for you in the formula bar: Notice the marching ants around Cell D15, and that there is a blue border with blue squares Hold your mouse over the bottom r...
Post a Comment
Read more