How to use excel conditional format formula if

Select the cells you want to operate and click "New Rule" in "Conditional Formatting". In the pop-up dialog box, select "Use Formula" in "Select Rule Type" "Determine the cells to be set", enter the IF formula in the formula bar, then click "Format", select "Fill" in the pop-up dialog box, select a color, and then click OK.

1. Excel conditional formatting uses the if formula to mark cells that meet the conditions

1. If you want to use Green marks the names of students in a class with an average score of 80 or above in each subject. Select all student grade records (i.e. the area A3:K30), select the "Home" tab, click "Conditional Formatting", select "New Rule" in the pop-up menu, open the "New Format Rule" window, and select "Use a formula to determine the cells to format", enter the formula =IF(K3>=80,B3) under "Format values ​​that match this formula"; click "Format" to open "Format Cells" window, select "Green", click "OK", return to the "New Format Rule" window, and then click "OK", then the names of all students with an average score of 80 or above will be marked in green. The operation steps are as shown in the figure As shown in 1:

2. Note:

cannot frame the title column of the table, it should start from the table record row (that is, from cell A3) Select the box, otherwise an error will occur. The error usually occurs in the first line under the title line, and it is often marked in color if it does not meet the requirements.

It is required to mark the first column with color, but in the formula =IF(K3>=80,B3), you cannot write A3 directly, because the column A3 is not a numerical value. If you write A3 directly, you will not be able to mark it. The name of the student who meets the conditions is required, so use numerical column B3 instead.

2. Excel uses multiple if conditions to mark the cells that meet the conditions

1. If you want to use orange to mark the average score above 80 points, C language Names of students with scores above 90. Frame the student grade table (that is, select the area A3:K30), hold down Alt, press H once, press L once, press N once, open the "New Format Rule" window, select "Use formulas to determine the format to be set" Cell", enter the formula =IF(K3>=80,IF(G3>=90,B3)), as shown in Figure 3:

2. Click "Format", open the "Format Cells" window, select the "Fill" tab, and select "Orange" under "Standard Color", as shown in Figure 4:

3. Click "OK", return to the "New Format Rule" window, click "OK" again, and the names of all students whose average scores are above 80 points and whose C language score is above 90 points will be marked in orange, as shown in the figure 5 shown:

4. Formula description: Formula =IF(K3>=80,IF(G3>=90,B3)) There are two IFs, namely IF Nested IF; where K3>=80 is the condition of the first IF. If K3>=80 is true, the second IF will be executed; if it is false, nothing will be returned; if the second IF is true , then return B3, otherwise nothing will be returned.

For more Excel-related technical articles, please visit the Excel Basic Tutorial column!

The above is the detailed content of How to use excel conditional format formula if. For more information, please follow other related articles on the PHP Chinese website!