I want to highlight the last 7 cells in a column in Excel.

[chrissy2014]

The column length changes from day to day with the addition of 1 or many values. I would like to do this using conditioning formatting so that I can colour the last 7 entries. The values can be numeric or alphanumeric but not blank. I have searched the web and found nothing that helps me with this.

 

[Somendra Misra]

Hi chrissy2014,

Welcome to Chandoo.org forum.

See the attached file. Hope I had understood you requirement. Thing to note

1. Column A has data which is a named dynamic range. See the formula in name manager.
2. Column B is hidden which is helper column for CF.
3. CF rule is based out of column B result.

PS: The named range is A1:A2100 and CF is also applied for the same range.

Just advise if any issue.

Regards,

 

[Luke M]

Condional formatting formula, starting in cell A2:
=COUNTA(A2:A$10000)<=7

 

[Mike H..]

The column length changes from day to day with the addition of 1 or many values. I would like to do this using conditioning formatting so that I can colour the last 7 entries. The values can be numeric or alphanumeric but not blank. I have searched the web and found nothing that helps me with this.

Hi,

You didn't say which column so this does column A. Select column A by clicking the header letter then apply this conditional format formula.

=AND(ROW()>(COUNTA(A:A)-MIN(7,COUNTA(A:A))),A1<>"")

It will highlight the last 7 or if less then 7 in the column it will highlight what's there.

 

[Hui]

I'd use: =AND(A1<>"",A8="")
Short and sweet