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:

6065857590
4580358065
9025652070
8595604060
7030957055

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:

5 by 5 matrix

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:

How to Apply Conditional Formatting in Excel

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

Apply Conditional Formatting in Excel

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:

Conditional Formatting in Excel

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

Format Cells greater than

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

How to Apply Conditional Formatting in Excel

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:

Light Red Fill with Dark Red Text

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

5 by 5 matrix

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

Highlight Cells Rules

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:

Yellow Fill with Dark Yellow Text

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

Conditional formatting in Excel