If conditional formatting formula in Excel (Highlight a row and multiple cells)
We can mark the cells that meet certain conditions with color using If conditional formatting formula in Excel.
Chapters:
0:00 If conditional formatting formula
1:01 Highlight a row using conditional formatting in Excel
2:15 Ifs conditional formatting in Excel
Description:
Note: The cc should be changed to greater than sign, the ee should be changed to less than sign in the formula.
If conditional formatting formula in Excel (Highlight a row and multiple cells)
In the process of making an Excel spreadsheet, sometimes we need to mark the cells that meet certain conditions with color, and sometimes we need to mark the whole row that meets certain conditions, and they can be achieved with the "Use a formula to determine which cell to format" in "Conditional formatting" with the if condition. In this way, we can mark not only cells or entire rows that meet one condition, but also cells or entire rows that meet multiple conditions, and we can also freely choose which color to use.
One. If conditional formatting formula (How to use the If formula to mark the cells that meet the conditions in Excel?)
1. Suppose we want to mark the names of students with an average score of 80 or more in a certain class in green. Select all grade records of students(i.e., the A2:E18 range), select the "Home" tab, click "Conditional Formatting", select "New Rule" from the pop-up menu, open the "New Formatting Rule" window, select "Use a formula to determine which cell to format“, and enter the formula =IF(E2cc80,B2) under ”Format values where this fomula is true"; Click "Format" to open the "Format Cells" window, select "Green", click "OK", return to the "New Format Rule" window, and then click "OK" to mark the names of all students with an average score of 80 or more in green.
2. Note:
A. The header column of the table cann‘t be selected, it should be selected from the table record row (i.e. from the A2), otherwise an error will occur, and the error usually occurs in the first row under the header row, which is often not in line with the requirements and is also marked with color.
B. The first column is required to be marked with color, but the formula =IF(E2cc80,B2) cann't be written directly A2, because the text in column A is not numeric values, if A2 is written directly, the name of the student who meets the conditions will not be marked, so the numerical column B is used instead.
Two. Highlight a row using conditional formatting in Excel (Highlight a row in Excel if condition, How to use the If formula to mark the whole row that meets the conditions in Excel)
1. For example, if the average score of a student in a class is 90 points or more, the row is marked in blue. Hold down Alt, press H,L,N once, open the "New Formatting Rule": window, select “Use a formula to determine which cell to format”, enter the formula =$E2cc90 under ”Format values where this fomula is true", click "Format", select the "Fill" tab, select "Blue", click "OK" twice, hold Alt again, press H,L,R once to open the "Conditional Formatting Rule Manager" window, and select Formula: =$E2cc90, change the reference to =$2:$18 in the input box for "Applies to", click "Edit Rule", the previously entered formula has been changed, change it back to the original formula =$E2cc90, click "OK" twice, then the students with an average score of more than 90 points are marked in blue.
2. After modifying the reference cell in the "Conditional Formatting Rule Manager" window, the previously entered formula will be often changed, so be sure to return to check, and change it back to the original formula if it has been changed.
3. =$2:$18 is an absolute reference to the row, that is, the second to the 18th row is absolutely referenced, that is, only the record row is referenced, not the header row of table, otherwise an error may occur.
Three. Ifs conditional formatting in Excel (How to use multiple If conditions in Excel to mark the cells that meet the conditions)
1. For example, the name of the student with the average score of 80 or more and the Python score of 90 or more is marked in orange. Select the A2:E18, hold down Alt, press H, L, N once, open the New Formatting Rule window, select “Use a formula to determine which cell to format”, and enter the formula =IF(E2cc80,IF(C2cc90,B2)).
2. Click "Format" to open the "Format Cells" window, select the "Fill" tab, and select "Orange" under "Standard Colors".
3. Click "OK" to return to the "New Format Rule" window, and click "OK" again to mark the names of all students with the average scores of 80 or more and Python scores of 90 or more.
4.Explanation of the formula =IF(E2cc80,IF(C2cc90,B2))
There are two IFs in the formula, i.e., nested another IF within IF, where E2cc80 is a condition for the first IF, if E2cc80 is true, the second IF is executed, if false, nothing is returned, and if C2cc90 in the second IF is true, B2 is returned, otherwise nothing is returned.
Watch video If conditional formatting formula in Excel (Highlight a row and multiple cells) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 19 December 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 118 once and liked it 0 people.