# Conditional Formatting - Last Record

#### Duvnjak

##### New Member
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.

### 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"

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))

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?

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

=MATCH("Grand Total",Sheet1!\$A:\$A,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?

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!

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.

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:

Replies
1
Views
1K
Replies
11
Views
1K
Replies
3
Views
431
Replies
2
Views
624
Replies
3
Views
346

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.

### Which adblocker are you using?

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

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