# Highlight a Sample In a Range or Text that contain certain Values

Learning how to use conditional formatting in excel can save you a lot of time when you need to visually highlight important information in a worksheet.

At basic level, it can be used to highlight duplicates, values within certain threshold, Top or Bottom N items etc.

However, to get the full potential of conditional formatting, you need to learn how to use formulas to highlight what you want.

It is simple to highlight duplicates (see image below) but, how do you highlight triplicates, quadruplicates.. i.e.Values repeated n times?

### Example 1

Highlight how many drivers who have been scheduled 3 times ?

Go to►Conditional formatting►New Rule►Use a Formula to Determine which cell to format

Type below formula in the Formula bar ►Select your Format► Click Ok

=COUNTIF($B$3:$B$26,$B3)=$F$3

►Select the area to apply the format ►Apply ►Ok ► Close

### NB:

To highlight values repeated n times use below formula

=COUNTIF(range,criteria)=n

Highlighting a sample from a range is not any that tricky as long as you understand COUNTIF function

For example, how do you highlight the scheduled times for drivers with these Ids 135751, 127840 and 138498?

Go to►Conditional formatting►New Rule►Use a Formula to Determine which cell to format. Insert below formula

=COUNTIF($F$3:$F$5,$B3)=1

### How It works:

Formula counts how many times the Driver Id ($B3) appears in the List ($F$3:$F$5), if it appears 1 time, it returns a TRUE value which conditional format uses to highlight the cell.

### What if you want to highlight Sentences that contain certain words?

For example, how do you highlight sentences that contain either the word “excel” or “data” in below sample?

Go to►Conditional formatting►New Rule►Use a Formula to Determine which cell to format. Insert below formula

=SUM(COUNTIF($A2, "*"& $C$2:$D$2 &"*"))=1

### Download Worksheet for Practice

### How it Works

See this explanation from Stack**Overflow** Forum

#### Recommended Reading

Introduction to Condition formating