How to do Excel Conditional Formatting based on another cell text

August 1, 2022
1.4K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

In excel we use various formulas to get our desired results. And instead of using formulas, we can otherwise use conditional formatting in order to generate our outcomes. Here we will be learning excel's conditional formatting formula as if a cell consists of text. Moreover, we will be using conditional formatting for our data set.

Now, in the given below table, we have shown the names of multiple students who are in varying grades they are in.

Manage to learn default rules in conditional formatting if the cell has the text:

Now, we will be learning if the cell of the spreadsheet consists of text only with default rules of the conditional formatting.

CELLS CONSISTING ONLY TEXTS:

Choose all cells which consist of texts.

2. In the ribbon select the home tab and then select the conditional formatting command.

3. Select highlight cells rules from there and then select more rules options.

4. Here, in the new formatting rules choose Format only cells that contain.

5. And on the bottom of an icon which is format only cells with, select no blank.

6. Now select the format option on the icon and a new window will pop up.

7.You will be given a different format option. But here we are supposed to select the fill option.

8. Enter Ok

.

9. At last we will be having a preview look after we selected the formatting.

10. Again click on OK.

11. Finally we will be seeing those cells that are marked and the blanks that are not marked.

Manage To Learn Cell Consisting Specific Texts:

Choose Text that contains from Highlight cell rules.

Now a new tab of text that contains will pop upHere, insert your desired character you want to highlight and select color.

Press Ok.

As, you can see now cells with a specific text are newly formatted.

Mange to learn Conditional Formatting on cells with duplicate Text:

From the ribbon select HOME, choose Conditional formatting.Choose Highlight Cell Rules > Duplicate Values.

An icon of Duplicate Values will pop up from their choose color of your choice.

Enter OK.

Manage to perform Conditional Formatting using Self-made formula in Excel:

Here, we will be applying conditional formatting by using a specific formula in the case of a cell consisting of text in an excel spreadsheet.

Texts in any Cells:

We are modifying the dataset here:

We will be using the ISTEXT Function which is a collection of IS functions. Here, if the function of logical value is TRUE if the value argument consists of text; else it will consequently be shown as FALSE.

Select the desired cells.From ribbon go to home tab > Conditional Formatting > More Rules.

A new icon will  pop

ChooseUse a formula to determine which cells to format.

Insert formula on marked box.

Now choose the formula.

And from the Fill option select color of your choice.

Click on Ok.

A preview window will be showed.

Click enter or press Ok.

Congratulations you now have learned multiple methods of Conditional formatting.

You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.