**Bottom Line:** Learn how to apply conditional formatting to cells that contain a partial match to a list of values.

**Skill Level: **Intermediate

## Watch the Tutorial

## Download the Excel File

The Excel file that I use in the video can be downloaded here:

Conditional Formatting Partial Match List Of Values.xlsx (27.0 KB)

## Conditional Formatting for Partial Matches

Let's say you have a list of items and you want to** highlight the ones that start with a certain character** or series of characters. You can use a formula to** search, find, and** **apply formatting based on a partial match** against a list of data.

In my example today, I want to color cells for any Part Numbers that **begin with a particular list of characters**.

## Using the Conditional Formatting Window

To start, we're going to open up the **Conditional Formatting window**. You can access this by going to the **Home** tab on the Ribbon and selecting the **Conditional Formatting** drop-down menu. Then select **New Rule**.

For the **Rule Type**, choose the option that says **Use a formula to determine which cells to format**. That will open up a field where you can type or paste a formula. I prefer to write my formulas in a cell and then paste it in, but it's entirely up to you.

## Writing the Formula

There are many different lookup formulas and ways to go about this, but we're going to use the COUNTIF function. There are **two arguments** to COUNTIF. The first is the **range**, and the second is the **criteria**.

For** range**, the formula is looking for the set of data that it should look through to find the criteria. In our case it is our list of **5-character codes that the part numbers should start with**. That's F3:F5 on our worksheet. But we want to make those values absolute ($F$3:$F$5) since they won't be changing.

For the **criteria**, we want to look at the** first five characters **of the product number. To do that we will use the formula called** LEFT**. It looks at the leftmost characters up to the number of places you determine. To fill out **the arguments for the LEFT function**, you choose the **first cell in the list** (A3) as the **text** argument, and the number 5 for **num_chars** argument.

When we close our parenthesis, our complete formula reads:

**=COUNTIF($F$3:$F$5,LEFT(A3,5))**

When written in the cell, this function returns a value of 1 or 0 depending on **if it found those five digits **at the beginning of the entry. Any value other than 0 equates to **TRUE** in the Conditional Formatting window, while a 0 is the same as returning **FALSE**.

## Applying the Formatting

One thing to note is that if you are writing your formula directly into the conditional formatting rule, if you **select** cell A3 instead of actually typing it, Excel is going to make it an **absolute reference ($A$3) **by default, which is not what we want.

Once the formula is copied or typed into the **Conditional Formatting** rule, click the **Format** button. This gives you all of the **formatting options** you can choose from that will apply when the formula is true.

After hitting **OK**, the new formatting will appear in the cells where the partial match is true.

If you find that you've **accidently applied the rule** to the cell where you wrote the formula instead of the list of Part Numbers, you can easily make edits to that range by selecting **Manage Rules **in the **Conditional Formatting **drop-down on the **Home** tab. (That's an edit I often have to make because I forgot to highlight the range I wanted before opening up the rule manager.)

## Applying Conditional Formatting to Entire Rows

If you want to **expand your conditional formatting** so it extends across the entire row, that's easy to do. As mentioned above, you can edit the existing rule by going to the **Home** tab, clicking the **Conditional Formatting** drop-down menu, and choosing **Manage Rules**. From there you can just **change the range** that the conditional formatting applies to.

In order for this to work, **the column reference in our formula must be absolute** ($A3). This is because we want the formatting to be determined based on the values from the original column.

The criteria for the conditional formatting **does not have to be on the same sheet** as the cells being formatted, and if you want to add, change, or delete some of the criteria for the formatting, just ensure that **your formula includes the updated range** of cells.

## Partial Matches that Aren't at the Beginning

What if the characters you are trying to match fall **at the end of the entry **instead of the beginning? For example, maybe you want to highlight any entries with the suffix “yahoo.com” in a column of emails. As you can probably guess, you'd **simply use the RIGHT function instead of LEFT**.

But there may be times when the data you are looking for** isn't always in the front or the back** of the entry. Or maybe you you're not sure where it would fall because you are not familiar with the entire list of data. For example, maybe you have a list of thousands of inventory product names and you want to call attention to how many contain the description “chocolate” and/or “coffee” anywhere in the name. **For this type of partial match, we can use the COUNT and SEARCH functions. **

The **SEARCH function** would look for the entries that contain the words or characters in your list. Then the **COUNT function **returns a value of 0 if no match is found and 1 (or more) if a match (or more than one match) is found. Here's an image showing how this formula looks.

## Conclusion

I hope this helps you to understand how to **apply conditional formatting to cells based on partial matches**. I've got several other posts that use conditional formatting that I invite you to check out:

- How to Apply Conditional Formatting to Rows Based on Cell Value
- Highlight Rows Between Two Dates with Conditional Formatting in Excel
- How to Filter for Duplicates with Conditional Formatting
- Progress Doughnut Chart with Conditional Formatting in Excel
- How to Apply Conditional Formatting to Pivot Tables

If you have any questions about this process, leave a comment. Thanks for reading!

Couldn’t you use Range Names in the conditional formatting formula? Wouldn’t that make it easier to add to the data formated or criteria lists? Could you use a Table Name? Or will this mess up the Absolute reference $A2 cell?

Hi Diane,

Great idea! Yes, the named range for the criteria Table would work. It would not mess up the mixed reference for $A2.

The formula would look like the following if we created a named range “rngCriteria” for the Table column that contains the criteria values.

=COUNTIF(rngCriteria,LEFT($A3,5))

Then any new criteria added to the table would be automatically included in the conditional formatting.

We’ll update the post to include this technique.

Thanks again and have a nice day!

Great Tip.

Also if you make the criteria a table (Control+T) you can add criteria and the conditional format will be updated automatically.

Great suggestion Carl! Thanks for sharing. 🙂

I should have mentioned that the Conditional Formatting Manager will not let you reference Tables (structured references) in formulas. However, Diane mentioned a workaround in her comment on creating the named range for the table. Then referencing the named range in the formula.

Do you also teach MS Access or have a website who teaches like you do?

Hi David,

I don’t specifically teach Access at this time. We do have some training on how to connect to it with Power Query in our Elevate Excel Training Program. However, it does not cover how to use Access.

Unfortunately, I don’t use Access often and can’t recommend anyone that teaches it. We’ll see about adding more training on it in the future.

Thanks again and have a nice day!

Good topic choice, and excellent presentation. Thanks

Thanks for these amazing videos.I am learning alot.

Very good suggestion from Diane.

Another one could be to turn the criterion length (5 currently written in “hard”) into a more general one (LEN function). The only thing is that it has a constrain: all criteria items must be same length. Thanks again for this new video John.

Great suggestion Florent! Thanks for sharing!

Hello

I love the COUNT(SEARCH conditional formatting. Using this one formula overcomes the need for creating multiple formulas to meet different search search requirements.

Could you please explain how I can prevent the relevant cells from picking up the formatting when the search criteria cell(s) are empty?

Thanks

I am facing a problem, I have written a formula =COUNTIF(carcgl!$A$2:$C$63830,@$A$2:$A$3820) to compare data in two column in two different sheets.

this formula is giving the value for XCYZ when it is finding XCYZ.ccy.com.

I just want that it should throw 0 for such situation. Please let me know what modification should I do with the formula.

how can i highlight names such as rama, shama, bhama at one short using conditional formatting in a table.

What is working best for me is to create the table of criteria as a dynamic table in Name Manager & put that name in the formula. Then criteria can be added or removed from the table w/o ever having to edit the formula again.

If you look in Name Manager immediately after creating any table, in this case Criteria, you’ll see it already created a name for the table as Table1, Table2, etc. This is not the one you want in your formula. In this case, I would name the auto-created table as z_Criteria to throw it to the bottom of Name Manager. Then select any data cell in the table, open Name Manager, select add a NEW name, name it Criteria, hover over the table header cell top right until a black arrow down appears, then just click while the arrow is showing. Now you have your named dynamic table for use in the formula.