Excel Conditional Formatting If a Cell Contains Any Text

Get FREE Advanced Excel Exercises with Solutions!

Conditional Formatting is one of the significant features of Excel. Using Conditional Formatting, we can specify cells or rows based on different criteria. In this article, we will discuss Conditional Formatting if a cell contains any text in Excel with suitable examples and proper illustrations.

Below we have shown how you can highlight cells that has blank values using the conditional formatting.

Overview of highlighting cells if cell contains any text using conditional formatting

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Ways with Conditional Formatting If a Cell Contains Any Text

We will discuss 2 methods and their sub-methods here to apply conditional formatting if a cell contains any text in Excel. We used a data set of students’ names who are studying in different standards.

Sample Dataset

1. Conditional Formatting Default Rules If a Cell Has Any Text

In this section, we will apply Conditional Formatting default rules to highlight cells if it consists of any text.

1.1 Conditional Formatting of Cells with No Blanks Condition


  • First, select the whole or the relevant part of the dataset.

selecting the range of cells B5:E9

  • Now, go to the Home tab.
  • Choose Conditional Formatting from the commands.
  • Select More Rules from the Highlight Cells Rules option.

Highlight cell rules are selected from the conditional formatting option from the Home tab.

  • Choose Format only cells that contain the Rule Type.
  • Select No Blanks from Rule Description.
  • Then, click Format.

In the New formatting rule dialog box, rules are being set up.

  • Select the Fill color and then click OK.

Color scheme of the cells that fulfill the conditions are being set up.

  • We will get a Preview of the output now.

Preview showing the cell format which will be effective If the cell fulfills the condition.

  • Finally, click OK.

Cells are highlighted based on whether they fulfill the condition or not

Here, we see that cells are formatted as our mentioned color.

1.2 Use Specific Text as Condition to Format Cells

We can search for a cell containing specific text by applying Conditioning Formatting. For example, we will search for John here to highlight the name.


  • First, select the range.

Range of cells B5:E9 which included empty cells is now selected.

  • Go to Highlight Cells Rules from the Conditional Formatting command.
  • Then choose Text that Contains.

Text that contains options is choosen from the conditional formatting on the Home tab.

  • After that, we will get a Pop-Up window.
  • Put John in the box named Format cells that contain the text.
  • Also, set the color format here.

Enter the word or name based on which the format will happen

  • Finally, press OK.

Cells formatted based on whether they contains the specific word or not

Here, we see those cells containing our given word are colored as our given choice.

1.3 Highlight Duplicate Cells Using Conditional Formatting

In this section, we will search if a cell contains duplicate texts. All cells containing duplicate text will be identified one time by applying this method.


  • Select the cells with data.

Range of cell B5:E9 is now selected which included the duplicate words

  • We will get Duplicate Values from the Highlight Cells Rules.

Duplicate selected from the Highlight cell rules to format cells that contains duplicate words

  • After clicking on Duplicate Values, we will get a Pop-up.
  • Choose Duplicate here.
  • Also, select the format color.

set formatiing to which the cell will be formatted if the cell contains duplicate cell

  • Then press OK.

Output highlighting those cells whose contains the duplicate cells

Here, we find that all the cells containing duplicate words are colored.

We can also specify the cells with unique words.

  • Follow the previous steps and select Unique and corresponding colors.

Setting unique in the duplicate value dialog box, we are going to highlight the cells that contains the duplicate values

  • Finally, press OK.

specific cells are highlighted based on whether they have the unique values or not

Here, we see that cells with duplicate and unique words are colored as per our given color.

Similar Readings:

2. Apply Formula in Conditional Formatting If Cell Consists of Any Text

In this section, we will use the formula with Conditioning Formatting.

2.1 Filter Cells with only Text Value by Setting Formula as Condition

The ISTEXT function refers to the IS function. It checks the specified value and returns TRUE or FALSE if the value is text.


Here, we will apply Conditional Formatting to find cells with text only. We will use the ISTEXT function here.

  • At first,select the cells to apply Conditional Formatting.

select range of cells B5:E9 to apply the conditional formatting.

  • Choose New Rule from Conditional Formatting.

add custom conditional formatting rule from the Home tab

  • We will get a new window.
  • Select Use a formula to determine which cells to format as Rule Type.
  • Write the formula on the marked box. The formula is:
  • Then, press Format.

formula enter based on which cells are going to be formatted whether they satisfy the rule or not

  • Therefore, we will choose a color for the Fill field.
  • Then press OK.

set cell Fill color int eh option based on whether the cell fulfill the condition or not.

  • Now, we will get a Preview.

A sample preview showing how the cell will look if the cell content satisfies the condition.

  • Finally, press OK.

Cell highlighted if they satisfy the condition

Here, we can see that cells containing any text are colored and the rest containing numeric values and blanks are not colored.

2.2 Filter Out Cell with Specific Text Using Cell Reference as Condition

Here, we will try to find out cells with specific text using text reference. We also modify the data set, and the data set will be like:


  • At first, select the cells first.

selection of the range of cell that needs to be conditionally formatted

  • Write down the formula on the marked box. The formula will be:

select the referenced cell value based on which the cells are going to be formatted.

  • Then, choose the color and see the Preview.

A sample preview showing whether the cell is satisfies the condition or not.

  • Finally, select OK.

cells are now highlighted whether they satisfy the condition or not

Here, we see those cells containing John are formatted. But this method is case-insensitive.

2.3 Highlight Cells with Specific Text Using the SEARCH Function as Condition

The SEARCH function returns the number of the character at which a specific character or text string is first found, reading from left to right.

Now, we add a row in the data set to find that word in the data set. Here, we chose John.

Cell C11 is the condition of the formatting


  • At first, select the data range.

range of cell B5:E9 is selected

  • Now, write the SEARCH function in Conditional Formatting. The formula is:

rules with function is entered in the formula rule box

  • After that, set the Format and see the Preview.

a sample preview box will show

  • Then, press OK.

the cell with the same value in cell C9 is now highlihgted

Now, see that all cells containing John are formatted. Also mentioned is that this method is also case-insensitive.

2.4 Deploying the FIND Function as Condition Extract Text with Case Sensitivity

The FIND function looks one text series among a big text series. It also provides the position of our desired text series from the given big text series. It looks upon each character of the given text series.


  • At first, select the data range first.

cell values in the range of cells B5:E9 is now selected for the formatting

  • Then write the Find function in Conditional Formatting. The formula will be:

specify the formula in the rule description box.

  • Then configure the Format and see the Preview.

sample preview showing how the cell would look if they met the conditions.

  • Finally, press OK.

Cell that met the condition is now highlighted

  • Finally, we noticed that case-mismatch words are not marked.


In this article, we described 2 methods to explain conditional formatting if a cell contains any text in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles


Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF