Click here if you would like to open a starting spreadsheet to work through this example.  

1. Select all the cells to which you want to apply conditional formatting.
2. Click on Format | Conditional Formatting
3. Choose equal to from the drop-down list and type the word “Distinction ” as shown below.

4. Click on Format and click on the pattern tab to set the Cell shading to green .

5. To set a second conditional format , repeat, click in Add and choose equal to from the drop-down list and type in the word “Fail”. Set the cell shading to red and your conditional formatting box should appear as shown below.

  Once you click OK, the worksheet should now appear as shown below.

6. Set the conditional formatting for Test B as done for Test A. Your final worksheet should appear as below.

TO EXPLORE
Explore the advantages of absolute referencing and setting up a separate section for grade boundaries. Let's say that you as a teacher decided to change the grades boundaries for both Test A and B. It's simple because all you have to do is change the values in the Grades boundaries section. All the rest is automatically generated. If you changed the Grade Boundaries as in the example below, all the cells which are dependent on these grade boundaries change automatically.

This demonstrates one of the main advantages of using a spreadsheet: they are DYNAMIC and FLEXIBILE