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:

6065857590
4580358065
9025652070
8595604060
7030957055

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:

 

Numbers in Excel

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:

 

Highlight Cells Rules under Conditional Formatting in Excel 2010

 

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

(4) Then, select “Greater Than…”

 

Highlight Cells Rules - Greater Than - Excel 2010

 

(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

 

Conditional Formatting in Excel

 

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

 

Highlight Cells Rules under Conditional Formatting in MS Excel

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…”

 

Highlight Cells Rules - Less Than - Excel 2010

 

(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

 

Highlight Cells Rules in Excel

 

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

 

Conditional Formatting in MS Excel

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…”

 

Highlight Cells Rules - Equal To - Excel 2010

 

(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

 

Highlight Cells Rules under Conditional Formatting in Excel 2010

 

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

 

Cells Rules in Excel