Formatting Issues

How to Highlight Excel Rows Based on Company Name Text

Huma Ashraf ChHuma Ashraf Ch Nov 10, 2026 869 views

Question details

The user needs to apply a conditional formatting rule to highlight entire Excel rows when a specific column contains business entity suffixes like CORPORATION, LLC, INC, or ENTERPRISES.

How to Highlight Excel Rows Based on Company Name Text
Product
Excel
Device & OS
not provided
Scenario
Applying advanced conditional formatting rules to highlight specific organizations in a data set while avoiding partial word matches.
Observed behavior
The goal is to accurately trigger the row highlighting only for exact business suffixes, preventing false positive matches such as finding 'INC' within the name 'LINCOLN'.
Before you start

Ensure your dataset is organized with clear column headers and select the full range of rows you wish to format before creating any rules.

Solution 1Recommended

Use SEARCH and ISNUMBER to Prevent False Positives

This widely compatible formula ensures precise matching by padding search terms with spaces, preventing the rule from highlighting partial text matches.

When dealing with text searches, simple wildcards often highlight rows incorrectly because they pick up substrings (like 'INC' in 'LINCOLN' or 'LLC' in 'MCCLELLAN'). By combining the ISNUMBER and SEARCH functions and intentionally placing spaces around your search terms, you guarantee only distinct words trigger the format.

1
Select your data range

Highlight the entire dataset where you want the formatting applied, for example, from A2 down to the end of your list.

2
Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.

3
Choose the formula option

In the dialog box, click on 'Use a formula to determine which cells to format'.

4
Enter the padded search formula

Type the formula: =OR(ISNUMBER(SEARCH(" CORPORATION"," "&$C2&" ")),ISNUMBER(SEARCH(" LLC"," "&$C2&" ")),ISNUMBER(SEARCH(" INC"," "&$C2&" ")),ISNUMBER(SEARCH(" ENTERPRISES"," "&$C2&" "))). Ensure the column letter matches where your company names are located.

5
Set the highlight format

Click the 'Format' button, go to the 'Fill' tab to choose your desired highlight color, and click 'OK' twice to apply the rule.

Use SEARCH and ISNUMBER to Prevent False Positives
Mixed References are Crucial: Make sure to lock the column reference with a dollar sign (e.g., $C2) while leaving the row number relative. This forces the formatting of the entire row to be based strictly on the values in column C.
Powerful Spreadsheet Tool

Easily Apply Conditional Formatting with WPS Office

WPS Spreadsheet provides intuitive and robust conditional formatting tools to seamlessly highlight specific rows based on complex text patterns. It fully supports standard Excel formulas, ensuring your data styling workflows remain uninterrupted.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the company data.
  2. 2. Select the data area: Drag to highlight all the rows you intend to dynamically format.
  3. 3. Access the Conditional Formatting rules: On the Home tab, click 'Conditional Formatting', then 'New Rule'.
  4. 4. Apply your text formula: Choose the formula option, paste your custom SEARCH or ISNUMBER formula, and click Format to assign a row highlight color.
Completely free to use with a lightweight installationFull support for advanced formula-based conditional formattingSeamless format compatibility with Microsoft Excel (.xlsx) filesIntuitive user interface for fast data management
microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula highlight the wrong rows?

This generally happens due to incorrect cell references. When highlighting an entire row based on a single cell's value, you must use a mixed reference. Ensure the column letter has a dollar sign in front of it (like $C2) so that as the rule checks across the row, it always refers back to the specific column.

How do I avoid partial text matches like 'INC' in the name 'LINCOLN'?

You can prevent partial matches by strategically adding space characters in your formula. By combining a space with your search term (e.g., ' INC ') and checking it against a padded cell value (e.g., ' '&$C2&' '), you ensure Excel only highlights distinct words instead of matching parts of a longer word.

Can I use multiple criteria in one conditional formatting rule?

Yes, you can nest multiple checks inside the OR() function. By placing several ISNUMBER(SEARCH()) conditions within a single OR statement, the row will be highlighted if any one of the specified company suffixes is found in the target cell.