Formatting Issues

How to Highlight Excel Rows When a Column Ends with Specific Text

Adam Davis Oct 20, 2026 868 views

Question details

Apply a conditional formatting rule to highlight entire rows if the value in a specific column (e.g., Column B) ends with a designated text string like "TE".

How to Highlight Excel Rows When a Column Ends with Specific Text

Product: Excel
Device & OS: not provided
Scenario: Formatting large datasets visually by evaluating the final characters of a cell in a specific column and expanding the highlight color across the entire row dynamically.
Observed behavior: The user requires a formula that locks the target column but allows the row reference to adjust automatically across the selected range, without returning formula errors.

Before you start

Identify the data range you want to highlight and note the exact starting row number, as the formula relies on a perfectly matched first-row reference to work correctly across your entire selection.

Solution 1Recommended

Use Conditional Formatting with the RIGHT Function

Apply a formula-based rule in Conditional Formatting using the RIGHT function to evaluate the ending characters of a cell and trigger the row highlight.

The RIGHT function extracts a specified number of characters from the end of a text string. By combining it with conditional formatting and an absolute column reference (using the $ symbol), you can highlight the entire row based on the value in that specific column.

Select the target data range

Click and drag to select the entire data range that you want to apply the highlighting to. For example, if your data starts at row 128, select A128:Z500.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula rule type

In the New Formatting Rule dialog box, select the option 'Use a formula to determine which cells to format'.

Enter the evaluation formula

Type the formula =RIGHT($B128,2)="TE" into the format box. Ensure you replace '128' with the actual starting row of your selected range. The $B locks the check to column B, and the number 2 tells Excel to check exactly two characters.

Apply formatting style

Click the Format button, go to the Fill tab to choose your preferred highlight color, click OK, and then click OK again to apply the rule to your spreadsheet.

Use Conditional Formatting with the RIGHT Function

Understanding Formula Parameters: The second parameter in the RIGHT function represents the number of characters to extract, not a row number. For example, =RIGHT($B128,2) extracts 2 characters. Avoid inserting high numbers like 129 unless your text string is actually 129 characters long.

Use WPS Spreadsheet to Easily Apply Conditional Formatting

WPS Office provides an intuitive interface for conditional formatting, making it effortless to highlight rows based on custom text strings. It seamlessly supports advanced formulas like RIGHT and LEFT for complex data visualization tasks.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to format.
2. Highlight your data range: Select the rows and columns where you want the conditional formatting to appear.
3. Access the Conditional Formatting tool: Go to the Home tab, click Conditional Formatting, select New Rule, and choose 'Use a formula to determine which cells to format'.
4. Set the rule and apply: Type your formula (e.g., =RIGHT($B1,2)="TE"), click Format to select a background fill color, and click OK to confirm.

Fully compatible with Microsoft Excel conditional formatting rules and syntax.Clean and intuitive user interface for managing multiple highlighting rules.Built-in robust formula error checking to prevent quotation mark mistakes.Free to use with a lightweight installation and fast performance.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the wrong row being highlighted after I apply the formula?

This usually happens if the row number in your conditional formatting formula does not match the very first row of your selected range. Always ensure the formula references the starting row (e.g., use $B128 if your mouse selection begins at row 128).

How do I modify the formula to check for a word of a different length?

Adjust the second argument (the number) in the RIGHT function to match the number of characters you want to check. For example, to check for 'END', which is three letters long, use =RIGHT($B1,3)="END".

Can I highlight rows based on how the text begins instead of how it ends?

Yes, you can use the LEFT function instead of RIGHT. For example, using the formula =LEFT($B1,2)="ST" will highlight the row if the cell in column B begins with the characters 'ST'.

What does the dollar sign ($) do in the conditional formatting formula?

The dollar sign creates an absolute column reference. In $B1, it locks the formula's focus to Column B. As Excel evaluates each cell across the entire row, it will always look back at Column B to decide whether to apply the highlight color.

Document & File Issues