Highlight the First or Last Populated Row in Excel
Question details
Apply conditional formatting to the first or last populated row in a range.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to highlight a row based on its position among populated cells in a chosen reference column.
- Observed behavior
- The first or last row with a nonempty cell in the reference column is highlighted within the selected range.
Choose a reference column that reliably indicates whether a row contains data. The examples check A2:A100 and apply formatting to A2:D100; adjust both ranges for your sheet and exclude headers.
Highlight the first populated row in Excel
Use this rule to highlight the first row whose reference cell is not empty.
Select A2:D100, starting at the same row as the reference range.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =ROW()=MATCH(TRUE,INDEX($A$2:$A$100<>"",0),0)+ROW($A$2)-1. Replace $A$2:$A$100 and $A$2 if your reference column or starting row differs.
Click Format, choose a fill or other style, and click OK to save the rule.

Highlight the last populated row in Excel
Use this rule to highlight the last row whose reference cell is not empty.
Highlight the First or Last Populated Row in WPS Spreadsheets
Use a formula-based conditional formatting rule in WPS Spreadsheets to identify the first or last populated row in your chosen reference column.
- 1. Open your spreadsheet: Open the workbook in WPS Spreadsheets and select the target range, such as A2:D100.
- 2. Add a conditional formatting rule: On the Home tab, choose Conditional Formatting > New Rule, then select the option to use a formula to determine which cells to format.
- 3. Enter the position formula: For the first populated row, enter =ROW()=MATCH(TRUE,INDEX($A$2:$A$100<>"",0),0)+ROW($A$2)-1. For the last, enter =ROW()=LOOKUP(2,1/($A$2:$A$100<>""),ROW($A$2:$A$100)). Adjust the reference range to match your data.
- 4. Apply the style: Choose a formatting style in the rule dialog and click OK to apply it to the selected range.

Frequently Asked Questions
Can I highlight the entire row instead of just one cell?
Yes. Select the columns you want to highlight, such as A2:D100, before creating the rule. The formula still checks the fixed reference range in column A.
What happens if there are blank rows between populated rows?
The formulas look for the first or last nonempty cell in the reference range, so blank rows between populated rows do not prevent a match.
How do I check a different column for data?
Replace $A$2:$A$100 with the range in your chosen column. For the first-row formula, also replace $A$2 with the first cell of that range.
Can I highlight both the first and last populated rows?
Yes. Create two separate rules under Home > Conditional Formatting, one with each formula. If only one row is populated, both rules apply to that row.




