如何在Excel中對多個單詞應用條件格式搜索？

對於我們而言，根據特定值突出顯示行可能很容易，本文中，我將討論如何突出顯示A列中的單元格，具體取決於是否在D列中找到它們，這意味著，如果單元格內容中包含任何文本，特定列表，然後突出顯示如左圖所示的屏幕截圖。

突出顯示單元格的條件格式包含多個值之一

篩選器單元格包含特定值並立即突出顯示它們

突出顯示單元格的條件格式包含多個值之一

事實上， 條件格式 可以幫助您解決這項工作，請執行以下步驟：

1. 首先，請為特定單詞列表創建一個範圍名稱，選擇單元格文本並輸入範圍名稱 我的清單 （您可以根據需要重命名）到 您的大名 框，然後按 Enter 鍵，請參見屏幕截圖：

2。然後選擇要突出顯示的單元格，然後單擊首頁 > 條件格式 > 新規則在 新格式規則 對話框中，完成以下操作：

（1.）點擊 使用公式來確定要格式化的單元格 在 選擇規則類型 列錶框;

（2.）然後輸入以下公式： = SUM（COUNTIF（A2，“ *”＆Mylist＆“ *”）） (A2 是要突出顯示範圍的第一個單元格， 我的清單 是您在步驟1）中創建的範圍名稱 格式化此公式為真的值 文本框;

（3.）然後單擊格式按鈕。

3。去的 單元格格式 對話框，然後選擇一種顏色以突出顯示填標籤，請參見屏幕截圖：

4. 然後點擊 OK > OK 要關閉對話框，包含任何特定列表單元格值的所有單元格會立即突出顯示，請參見屏幕截圖：

篩選器單元格包含特定值並立即突出顯示它們

如果你有 Kutools for Excel，其 超級濾鏡 實用程序，您可以快速篩選包含指定文本值的單元格，然後立即突出顯示它們。

Kutools for Excel : 帶有300多個便捷的Excel加載項，可以在30天內免費試用.

前往下載
30天免費試用购买
貝寶/ MyCommerce

安裝後 Kutools for Excel，請執行以下操作：

1。點擊 Kutools Plus > 超級濾鏡，請參見屏幕截圖：

2。在 超級濾鏡 窗格，請執行以下操作：

（1.）檢查規定選項，然後單擊按鈕選擇要過濾的數據范圍；
（2.）根據需要選擇過濾條件之間的關係；
（3.）然後在條件列錶框中設置條件。

3。設置條件後，單擊篩選過濾單元格包含所需的特定值。然後為下面的選中單元格選擇一種填充顏色首頁標籤，請參見屏幕截圖：

4。並且所有包含特定值的單元格都突出顯示，現在，您可以通過以下方式取消過濾器：重設按鈕，請參見屏幕截圖：

單擊立即下載並免費試用Kutools for Excel！

演示：過濾器單元格包含特定值並立即突出顯示它們

Kutools for Excel：具有300多個方便的Excel加載項，可以在30天內免費試用，沒有任何限制。 立即下載並免費試用！

最佳辦公效率工具

Kutools for Excel 解決了您的大部分問題，並將您的生產力提高了 80%

重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西；加密單元帶密碼創建郵件列表並發送電子郵件...
超級公式欄（輕鬆編輯多行文本和公式）；閱讀版式（輕鬆讀取和編輯大量單元格）；粘貼到過濾範圍...
合併單元格/行/列不會丟失數據；拆分單元格內容；合併重複的行/列...防止細胞重複；比較範圍...
選擇重複或唯一行; 選擇空白行（所有單元格都是空的）；超級查找和模糊查找在許多工作簿中；隨機選擇...
確切的副本多個單元格，無需更改公式參考；自動創建參考到多張紙；插入項目符號，複選框等...
提取文字，添加文本，按位置刪除，刪除空間; 創建和打印分頁小計；在單元格內容和註釋之間轉換...
超級濾鏡（將過濾方案保存並應用於其他工作表）；高級排序按月/週/日，頻率及更多；特殊過濾器用粗體，斜體...
結合工作簿和工作表; 根據關鍵列合併表；將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
300 多項強大功能。支持Office / Excel 2007-2019和365。支持所有語言。在您的企業或組織中輕鬆部署。完整功能30天免費試用。 60天退款保證。

閱讀更多... 免費下載... 採購...

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！

閱讀更多... 免費下載... 採購...

19 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
aparnag228@gmail.com · 1 years ago
Hello,

is the formula sensitive to space?
- Reply
To post as a guest, your comment is unpublished.
pri · 1 years ago
I have a List that contains "Numbers" and when I run this formula it only work on "Word/Text". What shall be my approach for numbers? For example, I have two rego ( registration) number for management team eg 1234 and 6789, so my data in A column will contain a lot of different vehicle number, i want to hightlight GREEN only these two regos.
- Reply
To post as a guest, your comment is unpublished.
Jesse · 1 years ago
This is for the "OR" function. How do I go abouts this with the "AND"?

As in I need to highlight ONLY the cells that have all of the list and not just either of the list.
- Reply
To post as a guest, your comment is unpublished.
Neel · 2 years ago
In the countif function, a comma did NOT work for me. I had to use a semicolon instead.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Neel,
  Maybe you have a different language version of Excel instead of English version.
  Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
umairaziz · 2 years ago
The problem with above KUtools function is 01. It doesn't give the option to highlight the entire row. 02. It searches based on exact match of the cell, and partial match option is not there...... Anyone got a solution, please share as I need this way... thank you!
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Umair,
  The Kutools function in this article can solve your problems, the first one, if you need to highlight the entire rows, please select all the filtered whole rows, and then apply the fill color for them; the second once, you should choose the criteria Contains from the criteria drop down list to filter the rows based on the partial text.
  Please try it again, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
HCAZ · 2 years ago
First great formula my only problem is that I have 3 list and although I changed the list names and used different colors - when I use a drop down all the names have the same colors.
- Reply
To post as a guest, your comment is unpublished.
Antony · 2 years ago
this formula doesnot work for me
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello，Antony，
  Could you explain your problem more clearly, or you can insert a screenshot here. Thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Gwen · 2 years ago
  
  I actually have the same problem, the formula highlights ALL cells in the range, why would that be?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hi, Gwen,
  The formula in this article works well in my worksheet, could you give your problem more detailed? Or you can insert a screenshot here for your problem.
  Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Rebecca · 3 years ago
I made my named range table on another tab.

I highlighted the row before I started making my rule. =$B$7:$X$7

=SUM(COUNTIF(E7,"*"&CANCELLED&"*"))

I double checked the 'applies to" and it correctly includes the whole row of the table.

The rule is working - but only colors the first cell in the row, not the entire row.

Any idea what I'm doing wrong?

Can the fact the E7 cell a dynamic drop-down based on a drop-down in D7 be the cause?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hi, Rebecca,
  If you want to highlight the whole row in your selected range, please use this formula:
  
  =SUM(COUNTIF($A2,"*"&Mylist&"*"))
  The cell reference should be converted to column absolute ($A2).
  
  Please try it, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
Shirtman · 3 years ago
Hi, I am using this formula to find IP addresses in a column (from a list).

It misses out some cells which it should highlight, and vice versa (highlights some which shouldn't be highlighted). The cells do seem to have some similarity to the IP addresses which I am looking for.

Is it something to do with the fact that there are 4 decimal points? (E.g. if I am looking for 10.54.31.7, it might highlight 10.54.31.5 as well).

Any ideas as to how I can fix this would be greatly appreciated - it's so close to working!
- Reply
- To post as a guest, your comment is unpublished.
  Gwen · 2 years ago
  
  I have the same problem! Did you ever fix it? I wonder why they replied others but never you...
  
  Reply
To post as a guest, your comment is unpublished.
su · 3 years ago
im unable to do use that formula, can you help in what i might be missing?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, su,
  Make sure the range name in the formula is your own, or you can insert your problem screenshot here.
  Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Rob · 4 years ago
That's useful technique. Would you mind walking through how it works? Thanks
- Reply

登錄到您的帳戶

重設密碼