How to Apply Conditional Formatting in Excel

In this short guide, I’ll show you how to apply Conditional Formatting in Excel.

The Example

Let’s suppose that you have the following 5 by 5 matrix, which contains the grades of 25 students:

The goal is to automatically highlight each of those grades based on the rules below:

• Highlight in green any grade above 60. This range represents the students who passed the course
• Highlight in red any grade below 60. This range represents the students who failed the course
• Highlight in yellow any grade that is equal to 60. This value reflects the students who conditionally passed the course

You can then use conditional formatting in Excel to apply those rules.

Steps to Apply Conditional Formatting in Excel

To start, copy the above 5 by 5 matrix into an Excel spreadsheet (to the range of cells A1 to E5 for example).

Your Excel spreadsheet should look like this:

Next, select all the cells that contain your 5 by 5 matrix. Here, you’ll need to select the range of cells A1 to E5. Then, press on Conditional Formatting:

From the drop-down menu, select the Highlight Cells Rules, and then select “Greater Than…”

Now, type 60 in the “Format cells that are GREATER THAN” box.

Earlier, we indicated that any grade above 60 should be highlighted in green (to represent the students who passed the course).

Therefore, set the color to green by selecting the “Green Fill with Dark Green Text” from the drop-down list:

Press OK, and you’ll see that all the grades above 60 will be highlighted in green:

Now let’s define the rule to highlight in red any grade below 60:

(1) First, select all the cells that contain your 5 by 5 matrix (i.e., the range of cells A1 to E5)

(2) Then, press on Conditional Formatting

(3) From the drop-down menu, select Highlight Cells Rules

(4) Finally, select the “Less Than…” option

Type 60 in the “Format cells that are LESS THAN” box.

Earlier, we indicated that any grade below 60 should be highlighted in red (to represent the students who failed the course).

In that case, maintain the red color: “Light Red Fill with Dark Red Text” and then press OK:

Here is how your new 5 by 5 matrix would look like so far:

Finally, let’s highlight in yellow any grade that is equal to 60:

(1) Initially, select all the cells that contain your 5 by 5 matrix (i.e., the range of cells A1 to E5)

(2) Then, press on Conditional Formatting

(3) From the drop-down menu, select Highlight Cells Rules

(4) Finally, select the “Equal To…” option

Type 60 in the “Format cells that are EQUAL TO” box.

Recall that any grade that is equal to 60 should be highlighted in yellow (to represent the students who conditionally passed the course).

Therefore, set the color to yellow by selecting the “Yellow Fill with Dark Yellow Text” from the drop-down list, and then press OK:

This is how your complete 5 by 5 matrix would look like after applying conditional formatting in Excel: