Formatting Issues

Highlight the First or Last Populated Row in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Apply conditional formatting to the first or last populated row in a range.

Highlight the First or Last Populated Row in Excel
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.
Before you start

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.

Solution 1Recommended

Highlight the first populated row in Excel

Use this rule to highlight the first row whose reference cell is not empty.

1
Select the target range

Select A2:D100, starting at the same row as the reference range.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the formula

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.

4
Choose the formatting

Click Format, choose a fill or other style, and click OK to save the rule.

Highlight the first populated row in Excel
How the reference column works: The rule checks column A only. A row with data elsewhere but an empty cell in column A is not considered populated.
WPS Spreadsheets

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. 1. Open your spreadsheet: Open the workbook in WPS Spreadsheets and select the target range, such as A2:D100.
  2. 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. 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. 4. Apply the style: Choose a formatting style in the rule dialog and click OK to apply it to the selected range.
Highlight a whole row based on the first or last nonempty reference cellAdjust the reference range to fit your dataOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.