Conditional Formatting - Last Record

Duvnjak

New Member
Joined
Aug 14, 2003
Messages
27
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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"
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
If it is the only instance of "Grand Total" in that column then you could use:

=MATCH("Grand Total",Sheet1!$A:$A,0)
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,367
Messages
6,119,114
Members
448,869
Latest member
Ceblevey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top