Excel: Conditional Formatting Based on Multiple Text Values

by Published on

To apply conditional formatting to cells in Excel that contain one of several specific text, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

The following example shows how to use this option in practice.

Example: Apply Conditional Formatting Based on Multiple Text Values

Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

Now suppose we would like to apply conditional formatting to each cell in the Position column that contains one of the following specific texts:

  • Starting
  • Forward
  • Center

To do so, we can create a new column that contains these text values.

We can then highlight these values in the range D2:D4 and type specific_text in the Name box in the top left corner:

Next, we can highlight the cells in the range A2:A11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type =SUM(COUNTIF(A2,”*”&specific_text&”*”)) in the box, then click the Format button and choose a fill color to use.

Excel conditional formatting based on multiple text values

Once we press OK, all of the cells in the range A2:A11 that include either Starting, Forward, or Center somewhere in the position name will be highlighted:

If we change the text values in column D, the formula will automatically update to highlight the cells that contain the new specific text values.

For example, suppose we instead look for the text values Point, Shooting, and Power:

All of the cells in the range A2:A11 that include either Point, Shooting, or Power somewhere in the position name are now highlighted.

Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: Apply Conditional Formatting to Overdue Dates
Excel: Apply Conditional Formatting if Cell is Between Two Dates
Excel: Apply Conditional Formatting Based on Multiple Conditions

23 Replies to “Excel: Conditional Formatting Based on Multiple Text Values”

    1. Hi Chris…Please elaborate on what you are experiencing so that we may better assist you.

      Reply

      1. have the same as above – copied your exact example and get no highlighting!
        my reason for doing this is that I have a series of numbers which are stored as text – I need them to be stored as text as they’re references to age e.g. 6.1 is 6 yrs 1 month, 6.10 is 6yrs 10 months. if they’re stored as numbers obviously excel converts 6.10 to 6.1 which doesn’t work for what I need.
        I was hoping to have excel read the cells and anything that is say 6.1 6.2 6.3 & 6.4 be red, 6.5 6.6 6.7 & 6.8 be orange, 6.9 6.10 & 6.11 be green (it’s a little more complicated than that but I can make it work if I get the building blocks!)
        I can make it work easily if the cells are *numbers* but as described I can’t have them stored as numbers because it breaks the prescribed format (which I don’t have a choice about)

        Reply

        1. Hi JJ…To color-code cells based on these age references stored as text, we can use Excel’s conditional formatting with a formula. Here’s how to set it up:

          1. **Select the Range**: Highlight the cells you want to apply the conditional formatting to.

          2. **Conditional Formatting Rules**:
          – Go to **Home** > **Conditional Formatting** > **New Rule**.
          – Choose **Use a formula to determine which cells to format**.

          3. **Setting Up the Formula**:
          – For the range “6.1” to “6.4”:
          – Use a formula like this:
          `=OR(A1=”6.1″, A1=”6.2″, A1=”6.3″, A1=”6.4″)`
          – Set the formatting color to red.

          – For the range “6.5” to “6.8”:
          – Use a formula like:
          `=OR(A1=”6.5″, A1=”6.6″, A1=”6.7″, A1=”6.8″)`
          – Set the formatting color to orange.

          – For the range “6.9” to “6.11”:
          – Use a formula like:
          `=OR(A1=”6.9″, A1=”6.10″, A1=”6.11″)`
          – Set the formatting color to green.

          4. **Apply the Rules**: Repeat steps for each color-coded range and make sure the cell references are adjusted if you are applying these rules to a different column or range.

          This approach will apply the colors you need while preserving the text format. Let me know if you need further adjustments!

          Reply

          1. Thanks! I couldn’t get those formulae to do what you described but your response did trigger another approach in my head and I worked it out, I appreciate the reply!

  2. Hi, is there a way of using the formula to flag when cells contain multiple strings of text? For example, I want to flag on a risk log when the incorrect language is used. The 3 strings are:
    There is a risk that
    because of
    leading to
    I want to be able to colour the cell a different colour when that language is not used. There will be text included in between those 3 strings of text and all the text will be in one cell.
    Thank you

    Reply

    1. Hi Suzanne…To achieve this in Excel, you can use conditional formatting with a custom formula to flag cells that do **not** contain all three specified strings (“There is a risk that,” “because of,” and “leading to”). Here’s how you can do it:

      1. **Select the cells** where you want to apply the conditional formatting.

      2. Go to the **Home** tab, and click on **Conditional Formatting** > **New Rule**.

      3. Choose **Use a formula to determine which cells to format**.

      4. In the formula field, enter the following formula:

      “`excel
      =NOT(AND(ISNUMBER(SEARCH(“There is a risk that”, A1)), ISNUMBER(SEARCH(“because of”, A1)), ISNUMBER(SEARCH(“leading to”, A1))))
      “`

      This formula works as follows:
      – `SEARCH(“There is a risk that”, A1)`: Searches for the first string in the cell A1. If found, it returns the position; if not found, it returns an error.
      – `ISNUMBER(…)`: Converts the position found by `SEARCH` into TRUE (if the string is found) or FALSE (if not).
      – `AND(…)`: Checks if all three conditions (presence of all three strings) are TRUE.
      – `NOT(…)`: Inverts the result, so it flags cells where **any of the strings is missing**.

      5. Click **Format**, choose the color you want to use for cells that **do not** meet the criteria, and then click **OK**.

      6. Click **OK** again to apply the rule.

      This rule will color the cell if it does not contain all three specified strings in any order within the text. If all three strings are present, the cell will not be formatted with the specified color.

      Reply

  3. Hi, is there a way I can use conditional formating to identify a cell that contains a letter? the cells should only be numerical but I want to be able to format the cell a certain colour if there is a letter in there. I can do the long version and have 26 seperate rules for each letter but I was wondering if there is an easier way to have all 26 letters in the 1 rule?

    Reply

    1. Hi Gary…Yes! You can use a single conditional formatting rule with a formula that checks if a cell contains any letter.

      ### Steps:
      1. Select the range of cells you want to apply the conditional formatting to.
      2. Go to **Home** → **Conditional Formatting** → **New Rule**.
      3. Choose **”Use a formula to determine which cells to format”**.
      4. Enter the following formula:
      “`excel
      =SUMPRODUCT(ISNUMBER(SEARCH(MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,ROW(A1:A26),1),A1))*1)=0
      “`
      – This checks if **A1** contains any letter (uppercase or lowercase).
      – Adjust **A1** in the formula to match the first cell of your selected range.
      5. Click **Format**, choose a color, and press **OK**.

      ### Alternative Simpler Formula:
      If your Excel version supports **REGEXMATCH**, you can use:
      “`excel
      =ISNUMBER(SEARCH(“[A-Z]”, A1))
      “`
      However, REGEXMATCH is only available in Google Sheets and newer Excel versions.

      ### Explanation:
      – `SEARCH(MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,ROW(A1:A26),1),A1)` checks if any letter exists in the cell.
      – `SUMPRODUCT(…)=0` ensures that if any letter is found, the condition is met.

      Now, all cells containing letters will be formatted in the color you choose. 🚀

      Reply

  4. Thank you for your help. I am using excel 365.
    I’ve tried the first method but when I apply the rule it highlights all the cells within then rule the formated colour (before i enter anything into the cell). when I then enter a value (be it number or letter) the cell then turns white? it’s very confusing

    I’ve tried the 2nd but that doesn’t seem to work for me.
    thanks

    Reply

    1. Hi Gary…It sounds like your conditional formatting rule is applied to all cells before any data is entered, which usually happens when the formula evaluates as `TRUE` by default. Let’s troubleshoot and refine the approach.

      ### **Solution 1: Adjusting the Formula**
      It seems that Excel is highlighting empty cells because the rule may not be correctly evaluating the condition. Try the following:

      1. **Check your Conditional Formatting Formula**
      – If you are using a formula like:
      “`
      =OR(A1=”Yes”, A1=”No”, A1=”Maybe”)
      “`
      Make sure **A1** is the first cell in your selected range.

      – Instead, use:
      “`
      =AND(A1<>“”, OR(A1=”Yes”, A1=”No”, A1=”Maybe”))
      “`
      This ensures that only non-empty cells are highlighted when they match the conditions.

      2. **Ensure Proper Cell References**
      – If you applied the rule to a range (e.g., `A1:A100`), make sure the formula references **only the first cell in the range** (e.g., `A1` and not `$A$1` unless needed).

      3. **Adjust the Rule Priority**
      – If other conditional formatting rules exist, they might be interfering.
      – Open **Conditional Formatting > Manage Rules**, check if any conflicting rules are applied, and adjust or delete unnecessary ones.

      ### **Solution 2: Use “Text Contains” Rule**
      1. Select the range (e.g., `A1:A100`).
      2. Go to **Home > Conditional Formatting > Highlight Cells Rules > Text That Contains**.
      3. Enter `Yes`, set the formatting color.
      4. Repeat for `No` and `Maybe`.

      This method ensures that only specific text entries trigger the formatting without affecting blank cells.

      Let me know if this helps or if you need further troubleshooting! 🚀

      Reply

  5. Hello,
    I recreated your example exactly and wasn’t able to replicate your results. I’m using Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20542) 64-bit.

    Reply

      1. Hi James,
        I followed all the steps in the example that you provided in the main article, including copying the exact example cells, but could not recreate the conditional formatting.

        Reply

          1. Hi James. I did a step by step recreation of the example in the article by Zach and when after I did the conditional formatting step, the conditional formatting didn’t work as he shows.

            “To apply conditional formatting to cells in Excel that contain one of several specific text, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

            The following example shows how to use this option in practice.

            Example: Apply Conditional Formatting Based on Multiple Text Values
            Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

            Now suppose we would like to apply conditional formatting to each cell in the Position column that contains one of the following specific texts:

            Starting
            Forward
            Center
            To do so, we can create a new column that contains these text values.

            We can then highlight these values in the range D2:D4 and type specific_text in the Name box in the top left corner:

            Next, we can highlight the cells in the range A2:A11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:

            In the new window that appears, click Use a formula to determine which cells to format, then type =SUM(COUNTIF(A2,”*”&specific_text&”*”)) in the box, then click the Format button and choose a fill color to use.

            Excel conditional formatting based on multiple text values

            Once we press OK, all of the cells in the range A2:A11 that include either Starting, Forward, or Center somewhere in the position name will be highlighted:

            If we change the text values in column D, the formula will automatically update to highlight the cells that contain the new specific text values.

            For example, suppose we instead look for the text values Point, Shooting, and Power:

            All of the cells in the range A2:A11 that include either Point, Shooting, or Power somewhere in the position name are now highlighted.

            Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.”

          2. Thanks for the detailed explanation — it helps a lot. Based on your description, it sounds like you followed Zach’s steps closely, but the conditional formatting **did not apply as expected**. There are a few common pitfalls in this kind of setup that might be causing the issue:

            —

            ### ✅ **Checklist to Make Sure Everything Works**

            #### 1. **Named Range “specific_text”**
            Make sure:
            – You selected **only the cells** with your keywords (e.g., D2:D4), not the entire column.
            – You typed `specific_text` **exactly** (no quotes) into the **Name Box** (above column A).
            – After defining, go to **Formulas > Name Manager** to confirm `specific_text` exists and is pointing to the correct range.

            #### 2. **Formula Syntax in Conditional Formatting**
            In the **Conditional Formatting Rule**, use:
            “`excel
            =SUM(COUNTIF(specific_text, “*”&A2&”*”))
            “`

            ⚠️ Zach’s article may have had:
            “`excel
            =SUM(COUNTIF(A2, “*”&specific_text&”*”))
            “`
            But this is **backward** — it checks if each `specific_text` term is inside `A2`, so **correct formula** should be:

            “`excel
            =SUM(COUNTIF(specific_text, “*”&A2&”*”))>0
            “`

            OR (even more reliable for **partial matches**):
            “`excel
            =SUMPRODUCT(–ISNUMBER(SEARCH(specific_text,A2)))>0
            “`

            #### 3. **Absolute vs Relative Cell Reference**
            Ensure you apply the formula relative to **A2** (top of your selection), **not fixed like $A$2**, or the formula won’t shift down the column correctly.

            #### 4. **Your Data Range**
            Ensure:
            – Your actual range is `A2:A11` (no blank rows or extra columns).
            – Column A contains plain text — no trailing spaces or hidden characters.

            #### 5. **No Extra Quotes or Smart Quotes**
            Make sure quotes in the formula are straight quotes (`”`) not curly or “smart” quotes (these can sneak in from copying from websites).

            —

            ### 🧪 Quick Test
            Paste this in an empty sheet and try the conditional formatting:

            | A (Position) | D (Keywords) |
            |————–|————–|
            | Point Guard | Point |
            | Center | Shooting |
            | Power Forward| Power |
            | Shooting G | |
            | Center G | |
            | Forward | |
            | Guard | |

            1. Highlight `D1:D3` → name it `specific_text` via Name Box.
            2. Highlight `A1:A7`.
            3. Conditional Formatting → New Rule → Use formula:
            “`
            =SUMPRODUCT(–ISNUMBER(SEARCH(specific_text,A1)))>0
            “`
            4. Pick a fill color → OK.

            ✅ All positions with “Point”, “Shooting”, or “Power” should now be highlighted.

            —

  6. Hi James,
    I tried to recreate your quick test, but conditional formatting still didn’t apply as expected.

    Steps I took as follows:
    1. Create new book
    2. Manually typing the values in A1:A7 and D1:D3 per test examples
    3. Highlighted D1:D3 and manually typed “specific_text” (no quotes) in the name box
    4. Checked in *Formulas > Name Manager** to confirm “specific_text” exists and is pointing to the correct range
    5. Highlighted A1:A7; Conditional Formatting → New Rule
    6. Pasted following formula with no quotes:
    “=SUMPRODUCT(–ISNUMBER(SEARCH(specific_text,A1)))>0”
    7. Confirmed formatting Applies to “=$A$1:$A$7” (no quotes)
    8. Filled in green > OK
    No cells in A1:A7 were had conditional formatting applied.
    I went back into Conditional Formatting and manually typed in the formula “=SUMPRODUCT(-ISNUMBER(SEARCH(specific_text,A1)))>0” (no quotes), with no results either.

    I’m using Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20542) 64-bit

    Reply

    1. Thanks for the clear and detailed steps — very helpful. It looks like you’re following everything correctly, but the formula may be stumbling over how named ranges are interpreted **within array functions like `SEARCH` inside conditional formatting**. Here are a few things to check and a **reliable fix**:

      —

      ### ✅ **Preferred Working Formula Using `COUNTIF` (More Robust):**
      Replace your formula with:

      “`
      =SUM(COUNTIF(A1, “*” & specific_text & “*”))>0
      “`

      ### 🔄 Explanation:
      – `specific_text` is a named range (`D1:D3`)
      – `COUNTIF(A1, “*” & specific_text & “*”)` checks whether any of the values in `specific_text` appear as substrings in `A1`
      – `SUM(…) > 0` returns TRUE if any match occurs

      This is more compatible with Excel’s conditional formatting logic than `SEARCH(…)`.

      —

      ### ✅ Step-by-Step Fix:

      1. In your sheet:
      – A1:A7: values like `”Starting Center”`, `”Bench Guard”`, etc.
      – D1:D3: values like `”Starting”`, `”Forward”`, `”Center”`

      2. **Create the named range** `specific_text` from cells `D1:D3`

      3. Select **A1:A7**

      4. Go to **Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format**

      5. Enter the formula:

      “`
      =SUM(COUNTIF(A1, “*” & specific_text & “*”))>0
      “`

      6. Set your formatting (e.g., green fill), and click OK.

      —

      ### ⚠️ Extra Notes:

      – `SEARCH` inside `ISNUMBER` with a named range sometimes **fails silently** in Conditional Formatting due to array context limitations.
      – `COUNTIF` is better behaved in this context and more readable.

      —

      Reply

      1. That last formula worked!! Thank you so much, James! I was about ready to give up at this stage if the conditional formatting didn’t apply properly.

        The copy and paste of your last formula didn’t take, but I manually typed the formula “=sum(countif(a1,”*”&specific_text&”*”))>0″ (no quotes) and conditional formatting applied. You’re a wizard!

        Reply

Leave a Reply

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