Format an Excel Row When a Cell Starts With ROA
Question details
Apply conditional formatting to an entire row when a cell contains a value beginning with a specified text prefix, such as ROA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to highlight full data rows when the identifying value in column A starts with ROA, for example ROA-John Smith.
- Observed behavior
- The conditional format must evaluate the text in the identifying cell and apply the selected format across the full row.
Identify the column that contains the text codes and select the complete row range that should receive the formatting. The formula must use an absolute column reference and a relative row reference, such as $A2.
Use LEFT to Format Rows That Start With ROA
Use this option when ROA must be the prefix at the beginning of the cell value.
The LEFT function checks the first three characters in the identifying cell. Apply the rule to the entire table range so every matching row receives the format.
In Excel, select the full range to format, such as A2:H100. Start with the first actual data row rather than the header row.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =LEFT($A2,3)="ROA". Replace A with the column containing the text code and replace 2 with the first row of your selected range.
Select Format, set the fill color, font, border, or other style you want, and then click OK twice to save the rule.

Use SEARCH to Format Rows Containing ROA Anywhere
Use this option only when ROA may appear anywhere within the identifying cell, not necessarily at the start.
Highlight WPS Spreadsheets Rows by a Text Prefix
WPS Spreadsheets can apply a formula-based conditional formatting rule to an entire row. Use a LEFT formula to highlight records whose code column starts with ROA, then customize the row fill, font, or border style.
- 1. Open the worksheet in WPS Spreadsheets: Open the Excel-compatible .xlsx or .xls file in WPS Office and select the table rows you want to highlight.
- 2. Open conditional formatting: On the Home tab, choose Conditional Formatting and create a new formula-based rule.
- 3. Add the prefix condition: Enter =LEFT($A2,3)="ROA", updating the column letter and first data-row number for your worksheet.
- 4. Set the matching row style: Choose the desired fill color, font, or border in the format options, then confirm to apply the rule to the selected range.

Frequently Asked Questions
Why does the formula use $A2 instead of A2?
The dollar sign locks column A while leaving row 2 relative. When Excel evaluates each selected row, it checks A3, A4, and later rows while applying the format across all selected columns.
How do I format the entire row instead of only column A?
Select the entire target range before creating the rule, such as A2:H100. Keep the formula reference pointed at the identifying column, for example =LEFT($A2,3)="ROA".
Can I match a prefix longer than three characters?
Yes. Change both the character count and comparison text. For example, use =LEFT($A2,5)="ROA-1" to match values beginning with ROA-1.
What is the difference between LEFT and SEARCH in conditional formatting?
LEFT checks characters at the beginning of the value, making it appropriate for prefixes. SEARCH finds text anywhere in the cell, so it can match values that do not start with ROA.
How can I check more than one text prefix?
Use OR in the formula, such as =OR(LEFT($A2,3)="ROA",LEFT($A2,3)="ADM"). Apply the rule to the full row range you want to format.




