Highlight Cells Rules under Conditional Formatting in Excel

Highlight Cells Rules is a useful functionality in Excel. In this post, I’ll show you how to use the Highlight Cells Rules under Conditional Formatting in Excel.

You can use Highlight Cells Rules to automatically highlight the cells that meet your defined rules.

Let’s look at an example to better understand how you can benefit from using the Highlight Cells Rules.

Example – Highlight Cells Rules

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

Now let’s Say that you want to automatically highlight each of those grades based on the following rules:

• Highlight in green any grade that is above 60. This range represents the students who passed your course

• Highlight in red any grade that is below 60. This range represents the students who failed your course

• Highlight in yellow any grade that is equal to 60. This range represents the students who conditionally passed your course

You can definitely use the Highlight Cells Rules to automatically highlight those grades based on the rules above.

But before we start, you’ll need to copy, or type, the above 5 by 5 matrix into an Excel spreadsheet.

Your Excel worksheet should look as follows:

Highlight Cells Rules – Greater Than…

(1) First, select all the cells that contain your 5 by 5 matrix. Here, you’ll need to select the range of cells of A1 to E5

(2) Then, press the Conditional Formatting icon. A drop-down menu will be populated on your screen as below:

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

(4) Then, select “Greater Than…”

(5) You’ll now need to type 60 under the “Format cells that are GREATER THAN” box.

Earlier, we indicated that any grade that is greater than 60 should be highlighted in green (to represent the students who passed your course)

(6) Therefore, you’ll need to set the color to green by selecting the “Green Fill with Dark Green Text” from the drop-down menu

(7) Press OK, and you’ll see that all the grades which are greater than 60 are now highlighted in green:

Highlight Cells Rules – Less Than…

Now let’s define the rule to highlight in red any grade that is less than 60:

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

(2) Then, press the Conditional Formatting icon

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

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

(5) Type 60 under the “Format cells that are LESS THAN” box.

Earlier, we indicated that any grade that is less than 60 should be highlighted in red (to represent the students who failed your course)

(6) Maintain the red color: “Light Red Fill with Dark Red Text”

(7) Lastly, press OK

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

Highlight Cells Rules – Equal To…

For the last piece of the puzzle, we will set the color to yellow for 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 of A1 to E5)

(2) Then, press the Conditional Formatting icon

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

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

(5) Type 60 under the “Format cells that are EQUAL TO” box.

Previously, we indicated that any grade that is equal to 60 should be highlighted in yellow (to represent the students who conditionally passed your course)

(6) Therefore, you’ll need to set the color to yellow by selecting the “Yellow Fill with Dark Yellow Text” from the drop-down menu

(7) Finally, press OK

This is how your 5 by 5 matrix would look like after applying all the rules: