How to Highlight Cells Containing Specific Text with Background Color in Excel

How to Highlight Cells Containing Specific Text in Excel

Using conditional formatting in Excel, you can automatically highlight cells containing specific text with a background color. This technique is useful for emphasizing important data in a spreadsheet. This article explains how to set background colors for cells containing specific text.

Using Conditional Formatting to Set Background Colors

With conditional formatting, you can automatically apply formatting based on the content of cells.

	A	B	C
1	Apple	Orange	Grape
2	Strawberry	Pear	Apple
3	Cherry	Apple	Banana
4	Peach	Kiwi	Grape

Steps

1. Select the range where you want to apply the background color (e.g., A1:C4). To select the entire sheet, press Ctrl + A.
2. Click the “Home” tab, then select “Conditional Formatting” → “New Rule.”
3. In the “New Formatting Rule” dialog box, choose “Use a formula to determine which cells to format.”
4. Enter the following formula:
   =ISNUMBER(SEARCH(“Apple”, A1))
   You only need to specify A1 as the reference cell for the formula.
5. Click “Format” and set the desired background color.
6. Click “OK” to apply the rule.

Result

Cells containing the specified text will automatically be highlighted with the chosen background color.

	A	B	C
1	Apple	Orange	Grape
2	Strawberry	Pear	Apple
3	Cherry	Apple	Banana
4	Peach	Kiwi	Grape

Formula Explanation

• SEARCH(“Text”, A1): This function returns the position of the specified text (“Text”) within the cell A1. If the text is found, it returns a number; otherwise, it returns an error.
• ISNUMBER(): This function checks whether the result of the SEARCH function is a number (i.e., the text is found) and returns TRUE or FALSE accordingly.

Applying Multiple Conditions

To highlight cells containing multiple specific texts, you can use a formula with the OR function:

1. For example, to highlight cells containing “Test” or “Sample,” use:
   =OR(ISNUMBER(SEARCH(“Test”, A1)), ISNUMBER(SEARCH(“Sample”, A1)))

Important Notes

• Conditional formatting only applies to the selected range. It won’t affect cells outside this range.
• The SEARCH function is case-insensitive. If case sensitivity is required, use the FIND function instead.
• The formula should be based on the top-left cell of the selected range (e.g., A1).

Summary

Using conditional formatting in Excel, you can highlight cells containing specific text by combining the SEARCH and ISNUMBER functions. This method improves data visibility and makes your spreadsheets more user-friendly.