highlight123

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?

conditional formatting

Example 1

Highlight how many drivers who have been scheduled 3 times ?

Sample conditional formatting

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

condition format using a formula

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

conditional format select

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?

sample4

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?
highlight sentencesGo 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 StackOverflow Forum

Recommended Reading

Introduction to Condition formating

Leave a Comment:

Your email address will not be published. Required fields are marked *