Excel Course: Conditional Formatting (examples 4 to 6)
4. Identify People Whose Names Are Missing from a Table
To determine whether a person whose name appears on a list is missing from a table, we will use the COUNTIF function.
The COUNTIF function will count the number of times that a first name appears in the table. If the result is 0, the formatting is applied.
![cf missing values excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/cf_missing_values.png)
Missing first names have been highlighted.
![display cf missing values excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/display_cf_missing_values.png)
You can use the same formula to find duplicate first names merely by replacing <1
with >1
.
![cf duplicate values excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/cf_duplicate_values.png)
Source file: cf4.xlsx
5. Shade Every Other Row
We will use the MOD function to shade every other row.
This function returns the remainder of division (in this case, division by 2). An even number will have a remainder of 0, but an odd number will have a remainder of 1.
![cf 1 row out of 2 excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/cf_1_row_out_of_2.png)
The CF uses the row number for the calculation.
![display cf 1 row out of 2 excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/display_cf_1_row_out_of_2.png)
For checkerboard-style formatting, add +COLUMN()
.
![cf checkerboard excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/cf_checkerboard.png)
Source file: cf5.xlsx
6. Search for a Word in the Text and Shade the Whole Row
The formula used here is =SEARCH($B$9;$D2;1)
.
![cf search excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/cf_search.png)
If the word entered in B9 is found in any of the cells in Column D, the whole row will be shaded.
![display cf search excel conditional formatting examples2](https://www.excel-pratique.com/view/en/course/img/excel_conditional_formatting_examples2/display_cf_search.png)
Source file: cf6.xlsx