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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Board Regular
Joined
Nov 22, 2007
Messages
51
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

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

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,548
Members
417,151
Latest member
ChickenTenderer

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
Top