Use conditional format in Excel -- let Excel adjust row color based on column Value

Currently, when creating an Excel table, you can select "yes" or "no". You want to select a "yes" row to display different colors. After checking this information, I tried to use the "Conditional format" function in Excel to achieve this goal.

See the following table:

 

 

In this worksheet, all the tasks and their completion statuses are highlighted. This format is dynamic: If the status changes, the format is automatically adjusted.

The procedure is as follows:
1. Create a worksheet for exercise as shown in Figure 1.

2. SelectCell areaA2: F4, select the menu command "format> condition format", select "formula" on the far left of the "condition format" dialog box, and enter the following formula in the input box on the right:

= $ F2 = "yes"

 

3. Click the "format" button to open the "cell format" dialog box and set the format for qualified cells. For example, set the cell shading to light green,

4. Click "OK" after setting, return to the original dialog box, and click "OK". The worksheet is displayed.

 

[Description]

The formulas in the condition format settings applySelected RegionInstead of acting on 2nd rows, this is because $ is used in the formula column parameters, so that the column becomes an absolute reference, while the row is a relative reference. You can click another line (such as row 3rd) and select format> condition format from the menu command. The formula used for this line is:

= $ F4 = "yes"

 

In addition, compare two cells:

Application Example: A instructor wants to highlight the students whose final score is higher than the interim score in a worksheet, as shown in Worksheet 1 after implementation.

 

Figure 1

This worksheet displays the scores of all students for two exams. The condition format function highlights the scores of students whose final scores are higher than the interim scores. This format is dynamic: If you change the score, the format is automatically adjusted.

The following describes the procedure.

1. Create a worksheet for exercise as shown in Figure 1.

2. select cell A2: C11, select the menu command "format> condition format", and select "formula" on the far left of the "condition format" dialog box ", enter the following formula (2) in the input box on the right ):

 
= $ C2> $ B2
 

Figure 2

3. Click the "format" button to open the "cell format" dialog box and set the format for qualified cells. For example, set the cell shading to light green, as shown in 3.

Figure 3

4. Click "OK" after setting, return to the original dialog box, and click "OK", as shown in worksheet 4.

Figure 4

Click on another line (such as line 1) and select the "format> condition format" menu command. The formula shown in Figure 5 is displayed. The formula used for this line is:

 
= $ C10> $ B10
 

Figure 5