OmniDocHow to mark cells containing specified text in Excel conditional formatting
Use conditional formatting formula rules to mark cells that contain or exclude certain text. You can also mark multiple keywords at the same time, or set different colors based on content. Provide formula writing methods, application scope settings and item-by-item verification methods.
Key points of this article
- Contains formulas that specify text using ISNUMBER plus SEARCH. SEARCH is not case-sensitive. Use FIND instead if you want to do so.
- References to cells in formulas must be written as relative references, and the upper-left cell of the application range must be aligned with the starting cell in the formula.
- It does not include the non-empty judgment, otherwise blank cells will also be marked.
- Multiple keywords can be placed in a column of cells, and can be judged uniformly using SUMPRODUCT. There is no need to change the formula when changing keywords.
- When dividing colors according to content, use one rule for each color, and pay attention to the order and stop conditions of the rules.
It is a very common need to find records containing certain text in a table. For example, the address containing "Beijing" is marked in the customer list, the row containing "Extension" is marked in the task table, or all cells containing a certain model are highlighted in the product description. Searching row by row with the naked eye is slow and easy to miss. Using conditional formatting can automatically change the color of cells that meet the conditions.
Many people only use numerical comparison rules in conditional formatting, such as greater than, less than, and between. When encountering text, you need to change your thinking: use a formula to determine whether each cell contains the specified text, and then apply the format to the cells that match the formula. This article talks about this general approach, which is applicable to the common desktop version of Excel. After reading, you can complete the four situations of including, not including, multiple keywords and color by content.
How to mark cells containing specified text? One sentence conclusion
Direct answer: Create a new formula-based rule in conditional formatting, use SEARCH to search for keywords, and then use ISNUMBER to determine whether it is found. This way of writing can cover most text markup needs.
| your situation | Recommended practices | reason |
|---|---|---|
| Mark only one keyword, regardless of case | =ISNUMBER(SEARCH("Keyword",A2)) | The writing method is short and English capitalization does not affect the results. |
| Mark only one keyword and be case sensitive | =ISNUMBER(FIND("Key",A2)) | FIND is strictly case sensitive |
| Highlight cells that do not contain certain text | =AND(A2<>"",ISERROR(SEARCH("Keyword",A2))) | Add non-empty judgment to prevent blank cells from being marked |
| Mark multiple keywords at the same time | Put the keywords in one column and use SUMPRODUCT to judge | Adding or deleting keywords only changes cells, not formulas. |
| Different content displays different colors | Create a formula rule for each color | The rules do not interfere with each other and are easy to adjust individually. |
Before you start: Confirm these points first
Judging a few things before taking action can save you a lot of detours.
- Whether the text itself is standardized. If there are extra spaces before and after the text in the cell, or if full-width and half-width are mixed, SEARCH will not find it. You can use TRIM to organize the data first, or write the complete text you actually see in the formula.
- Whether case sensitivity is required. SEARCH is case-insensitive and suitable for Chinese and ordinary English; FIND is case-sensitive and suitable for case-sensitive content such as models and codes.
- Whether keywords are written in formulas or placed in cells. When there are only one or two words, it is simple to write them directly in the formula; when there are many words or need to be changed frequently, it is more convenient to maintain them by putting them in a separate column.
- Which grid does the application range start from? The cell references in the formula are relative and must be written based on the first cell in the upper left corner of the application range, otherwise the entire area will be misaligned.
- Whether numbers also need to be judged. The inclusion judgment is mainly for text. If the cell is a number, SEARCH will treat it as text, but the number format (such as decimal places and thousandths) may affect the matching. If necessary, use TEXT to unify the format first.
Use formulas to mark steps containing specified text in Excel
The following uses column A as an example to mark the cells containing "Beijing" from A2 to A200.
- Select the area to be marked, A2:A200, and note that the upper left corner is A2.
- Open conditional formatting, create a new rule, and select the type of formatting based on a formula, that is, let the result of the formula determine which cells need to be formatted.
- Enter =ISNUMBER(SEARCH("Beijing",A2)) in the formula box, where A2 should correspond to the upper left corner of the selection.
- Click Format, select a bold color in Fill or Font color, and then OK.
- Return to the rule management interface and check whether the "Apply to" range is $A$2:$A$200 or A2:A200.
- Check the results after confirmation. The cells containing "Beijing" should have changed color.
How to write excluding, multiple keywords and color by content
Mark cells that do not contain the specified text
If you want to mark cells that do not contain a certain word, just use ISERROR(SEARCH(...)). But blank cells will also be judged as "not containing", so a non-empty judgment needs to be added, written as =AND(A2<>"",ISERROR(SEARCH("Beijing",A2))). In this way, only cells that actually have content and do not contain "Beijing" will change color.
Mark multiple keywords at the same time
Assume that the keywords are written in H1 to H5, and any one that appears in the cell of column A is colored. The formula can be written as:
=SUMPRODUCT(--ISNUMBER(SEARCH($H$1:$H$5,A2)))>0
$H$1:$H$5 is an absolute reference, indicating that the keyword area is fixed, and A2 is still a relative reference. To add or delete keywords, you only need to modify the contents of column H, and the rules themselves do not need to be rebuilt. If there are few keywords, you can also use OR to write multiple ISNUMBER(SEARCH(...)) side by side, for example =OR(ISNUMBER(SEARCH("Beijing",A2)),ISNUMBER(SEARCH("Shanghai",A2))).
Color based on content
The idea of dividing colors according to content is: each color corresponds to a keyword, and a rule is created for each. For example, "Delayed" is marked in red, "Complete" is marked in green, and "Pending" is marked in yellow. Note a few points:
- There is an order between rules. When the same cell matches multiple rules, the format ranked first will be displayed first.
- If you want to stop judging other rules after hitting a rule, you can check "Stop if true" in the rule settings.
- When there are too many rules, you can adjust the order in the management rules or delete rules that are no longer used.
Check each item after completion
Just writing out the formula does not mean that the result will be correct. It is recommended to check it item by item.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the discoloration range correct? | Scroll to the end of the table to see if the last row is also judged | Go back to rule management and correct the "Apply to" area |
| Are all keywords recognized? | Spot-check a few cells that obviously contain keywords | Check whether there are any extra spaces or full-width differences, and use TRIM to sort them out |
| Are there any spaces marked incorrectly? | Check whether the blank rows below the table change color | Does not contain class rules to supplement non-empty judgment |
| Is capitalization as expected? | Find an example with different capitalization | When distinction is needed, replace SEARCH with FIND |
| Do multiple color rules conflict? | Find cells that contain two keywords at the same time | Adjust the order of rules and check stop conditions if necessary |
Several common situations
If you only view it temporarily, you can also use the search function manually to locate the text, but the color mark will not be retained. Conditional formatting is suitable for tables that require long-term maintenance and frequently updated data, because the formula rules will automatically refresh with the data.
For particularly long tables, formula rules can make scrolling and calculations slower. You can only set the necessary application range instead of applying the rules to the entire column or even the entire table, which saves resources.
❓ Frequently Asked Questions (FAQ)
1. There is no problem with the formula, but why does the cell not change color?
The common reason is that the application range does not correspond to the starting cell of the formula, or the quotation marks are missing in the formula. It may also be that the font or fill color is set to the same as the original color. Check the formulas and ranges in rule management one by one, and then see if the format settings take effect.
2. What is the difference between SEARCH and FIND?
Both are used to find text positions. SEARCH is not case-sensitive and also supports wildcard characters question mark and asterisk; FIND is case-sensitive and does not support wildcard characters. It is more convenient to use SEARCH for daily Chinese forms. Use FIND when accurate comparison of models and codes is required.
3. Is there an easier way without writing formulas?
In the highlighted cell rules of conditional formatting, there is usually an option to mark by text inclusion, which is suitable for marking only one word. However, the requirements for multiple keywords, no inclusion, and color classification based on content still require formulas to be implemented.
4. If there are asterisks or question marks in the keywords, will there be an error?
meeting. SEARCH treats asterisks and question marks as wildcard characters. If the text to be searched contains these two symbols, it needs to be escaped with a tilde in front and written as ~* or ~?.
📝 Summary
Using the formula rules of ISNUMBER plus SEARCH, you can mark the cells that contain the specified text. With the non-empty judgment, you can also handle the non-containing situation. Multiple keywords and color classification according to content can also be implemented using the same idea. After writing the formula, remember to check the application range, blank spaces, and rule order to confirm that the results are as expected.
