Exceljet

Quick, clean, and to the point

Conditional formatting last n rows

Excel formula: Conditional formatting last n rows
Generic formula 
=ROW()-INDEX(ROW(data),1,1)+1>ROWS(data)-n
Explanation 

To highlight the last n rows of a range or a table, you can use a formula based on the ROW and ROWS function. In the example shown, the formula used to apply conditional formatting to the data in B5:D15 is:

=ROW()-INDEX(ROW(data),1,1)+1>ROWS(data)-n

where data (B5:D15) and n (F5) are named ranges. This rule highlights the last n rows in the data. When n is changed, the highlighting is automatically updated.

How this formula works

This example is based on the formula explained in detail here:

=ROW()-INDEX(ROW(data),1,1)+1>ROWS(data)-n

The formula uses the greater than operator (>) to check row in the data. On the left, the formula calculates a "current row", normalized to begin at the number 1:

=ROW()-INDEX(ROW(data),1,1)+1 // calculate current row

On the right, the formula generates a threshold number:

ROWS(data)-n // calculate threshold

When the current row is greater than the threshold, the formula returns TRUE, triggering the conditional formatting.

Conditional formatting rule

The conditional formatting rule is set up to use a formula like this:

Conditional formatting last n rows rule

With a table

You can't use a table name in a CF formula at present. However, you can select or enter the table data range when creating the formula in the CF window, and Excel will keep the reference up to date as the table expands or shrinks.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.