Conditional Formatting - Last Record

[Duvnjak]

Hello,

I am wondering if there is an easy way to conditionally format the last record in my spreadsheet. Seems simple enough but there is a catch: my dilemna is that the location or row of the last record might change (i.e. row 35 one day and row 39 the next). I created a PivotTable using Excel 2007 and then converted this to be formula based. The number of records produced by this report might grow over the course of the year and I would like to be able to highlight or bold the last row showing the grand total numbers.

Thanks to all in advance.

 

[Richard Schollar]

Hi

Assuming you know that your last row in column A will be numeric then I would do the following:

1. Create a defined name wcalled LastRw with a definition of:

=MATCH(9.9E+307,$A:$A)

2. Select a sufficiently large range in your worksheet eg A1:Z2000 and go Format>Conditional Formatting.
3. Change to Formula Is and use:

=ROW()=LastRw

and then hit the Format button and format as required.

If column A contains a text value in the last row (eg "Grand Total" then replace the 9.9E+307 with "zzzzzzz"

 

[Andrew Poulsom]

If you data is contiguous starting in row 1 you can also use a conditional formatting formula like:

=ROW(A1)=ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))

 

[Duvnjak]

Thank you both for your responses.

I'm still having some difficulty getting this to work properly in Excel 2007. The data in my Column A is all text, with one row in between being null.

Something I forgot to mention - the last row in my column will always show up as "Grand Total". I tried using this formula:

=MATCH("Grand Total",Sheet1!$A:$A)

It provides me with a result of 54 (the correct value). However, I also get an error message that shows up as a tool tip saying:

"The formula in this cell refers to cells that are currently empty."

When I go to trace the empty cell it points to the top cell (A1).

Any ideas?

 

[Richard Schollar]

If it is the only instance of "Grand Total" in that column then you could use:

=MATCH("Grand Total",Sheet1!$A:$A,0)

 

[jim may]

Richard,
..been meaning to ask, but is this 9.9E+307 a constant
that can be used to bring back the highest possible number imaginable
by Excel?

 

[Richard Schollar]

Jim - it's pretty darned close to the largest number which Excel can handle! It's a standard number written in scientific notation (so the E+307 represents "times 10 to the power of 307") - sufficiently large for everything I can think of!

 

[brinraeven]

I none of the cells are blank, you could use this under your conditional formatting:

=ROW($A1)=COUNT($A$1:$A$10)

If you know that a certain number of cells (let's use 1) is always going to be blank, you could use:

=ROW($A1)=COUNT($A$1:$A$10)+1

If the column is text only or numbers and text, use:

=ROW($A1)=COUNTA($A$1:$A$10)

Let me know if that helps.

 

[Duvnjak]

Thank you all. The following worked like a charm!

For the named range LastRow (column A):

=MATCH("Grand Total",Sheet1!$A:$A,0)

Condition:

=ROW()=LastRow