Excel’s Highlight Cell Rules – Text That Contains is a powerful tool for identifying and emphasizing cells that include specific text. Whether you’re analyzing customer feedback, sorting through inventory lists, or finding key phrases in datasets, this feature ensures the data you need stands out instantly.
At The Coding College, we simplify Excel to enhance your productivity. This guide will help you use the Text That Contains rule efficiently.
What Is the Text That Contains Rule?
The Text That Contains rule in Excel highlights cells containing specific text or partial matches. It’s particularly useful for text-heavy datasets.
Examples:
- Highlight customer names containing “Smith.”
- Flag product descriptions with the word “Discount.”
- Identify comments with phrases like “urgent” or “pending.”
Steps to Apply the Text That Contains Rule
1. Select the Data Range
Choose the cells where you want to apply the rule.
2. Open Conditional Formatting
Go to the Home tab and click Conditional Formatting in the Styles group.
3. Select Text That Contains
Under Highlight Cell Rules, click Text That Contains.
4. Enter the Text to Highlight
- In the dialog box, type the text or phrase you want to find.
- Example: Enter
"Discount"to highlight all cells containing the word “Discount.”
5. Choose a Formatting Style
- Select a predefined style (e.g., green fill, bold text) or customize the format to suit your needs.
6. Apply the Rule
Click OK to see the cells containing your specified text highlighted.
Practical Examples of the Text That Contains Rule
1. Analyze Customer Feedback
Highlight feedback comments containing words like “complaint” or “excellent” to identify areas of concern or success.
2. Sort Inventory Lists
Emphasize products marked with “Sale” or “Out of Stock” for better tracking.
3. Organize Project Tasks
Flag tasks with labels such as “urgent” or “completed” to improve task management.
4. Manage Email Lists
Highlight email addresses containing specific domains like "@gmail.com" or "@yahoo.com".
Editing or Removing the Rule
To Edit a Rule
- Go to Conditional Formatting > Manage Rules.
- Select the rule and click Edit Rule to update the text or formatting.
To Remove a Rule
- Open Manage Rules.
- Select the rule and click Delete Rule.
Benefits of Using the Text That Contains Rule
- Efficient Text Analysis: Quickly identify relevant cells in large datasets.
- Enhanced Organization: Highlight important keywords or phrases for better focus.
- Streamlined Decision-Making: Easily locate critical information to act upon.
Tips for Effective Use
- Use Partial Matches: This rule identifies cells with your text even if it’s part of a longer phrase.
- Combine with Filters: Narrow down datasets further by applying filters to highlighted cells.
- Customize Formatting: Use bold text, colored fills, or borders to make highlighted data visually appealing.
Final Thoughts
Excel’s Text That Contains rule is a flexible and essential tool for managing text-heavy datasets. By highlighting specific words or phrases, you can extract valuable insights and streamline your workflow.
At The Coding College, we’re dedicated to helping you unlock the full potential of Excel. Visit our website for more tutorials that transform the way you work with data!