How to do Excel Conditional Formatting Based on Another Cell Text

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.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

Tips and Tricks for Effective Conditional Formatting

Here are some tips and tricks for effective conditional formatting:

• Clearly define the purpose of your conditional formatting.

• Select conditions that accurately represent the data patterns you want to highlight.

• Prioritize rules to ensure the desired outcomes.

• Use formula-based rules for advanced scenarios and custom conditions.

• Utilize relative and absolute references to control cell-specific formatting.

• Experiment with formatting options to make data visually appealing and easy to interpret.

• Thoroughly test and review the formatting with sample data.

• Document your conditional formatting rules for clarity and future reference.

• Update and adapt the formatting as your data changes.

• Explore pre-defined conditional formatting presets to save time.

FAQ

Can I use an IF formula in conditional formatting?

Yes, you can use an IF formula in conditional formatting. You can create a custom formula that returns TRUE or FALSE based on your criteria, and then apply the desired formatting to the cells that meet the condition.

How do I use dynamic conditional formatting?

To use dynamic conditional formatting, you need to create a custom formula that returns TRUE or FALSE based on your criteria, and then apply the desired formatting to the cells that meet the condition. You can also use functions like AND, OR, NOT, IF, etc. to create more complex conditions.

How do you have two conditional formatting rules in one cell?

You can have two conditional formatting rules in one cell by creating two separate rules with different criteria and formatting options. You can also use the AND, OR, or IF functions to combine multiple criteria in one rule.

For example, if you want to highlight the cells that are either greater than 100 or less than 50, you can create two rules like this:

Rule 1: Cell value > 100, format as green fill

Rule 2: Cell value

Summary

In conclusion, this comprehensive guide has provided easy and simple steps to master conditional formatting based on another cell. By exploring advanced techniques such as using formulas and duplicate texts, you can enhance your data analysis skills and streamline your workflow

With its user-friendly interface and robust features, WPS Office seamlessly integrates these techniques, simplifying the process of conditional formatting and optimizing your data organization and analysis. By mastering conditional formatting in WPS Office, you can confidently make data-driven decisions and elevate your spreadsheet skills.