Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Referencing the Last Cell in a Column

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Referencing the Last Cell in a Column.

Patty asked about a common scenario, in which column B contains quite a bit of data, and information can be added to the cells in the column at any time. In a formula in cell C4, Patty wants to see the value at the bottom of those cells in column B that contain values. Thus, if cells B1:B27 contain data, then in cell C4 Patty wants to see the value that is in cell B27. If three more pieces of data are added to column B, then the value in C4 should contain the value in B30.

The solution to this problem depends on whether you can count on the data in column B containing blank cells or not. If the data is contiguous—it doesn't contain any blank cells—then you can use the following formula in C4:

=INDIRECT("B"&COUNTA(B:B))

This constructs an address based on the last cell in the column, and then uses the INDIRECT function to return the value at that address.

If it is possible for there to be blanks in column B, then the following formula will work:

=INDIRECT("B"&MAX(ROW(1:65535)*(B1:B65535<>"")))

Again, the INDIRECT function is used to fetch the actual value, but the address used by INDIRECT is put together differently.

A different approach is to use the VLOOKUP function to return the value. If column B consists of numeric values, then the following formula in C4 will work just fine:

=VLOOKUP(9.99999999999999E+307,B:B,1)

If column B contains text, then the numeric lookup won't work, but the following will:

=VLOOKUP(REPT("z",50),B:B,1)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2103) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Referencing the Last Cell in a Column.

Related Tips:

Tame Your Data! ExcelTips: Filters and Filtering provides all the details necessary to let you manage large sets of data with confidence and ease. Its information-packed pages demonstrate how to use the two types of filters provided by Excel: AutoFilters and advanced filters. Check out ExcelTips: Filters and Filtering today!

 

Comments for this tip:

Michael (Micky) Avidan    10 Oct 2013, 10:27
To find the last row that holds an AlphaNumeric value - you can use:

=MATCH("*",$A:$A,-1)

In order to return the last cells row in a Mixed column INCLUDING EMPTY(!) cells - use:

=MAX(MATCH(9^15,A:A),MATCH(CHAR(255),A:A,1))

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Carl Colijn    10 Oct 2013, 04:17
I struggled with this one as well, but came to a better solution using array formula's.

Array formula's allow you to evaluate a formula on each cell in a range, so if you use the array formula:
  =MAX(IF(A5:A500="",0,Row(A5:A500)))
then it will tell you the last used row, even if there are blanks in the range A5:A500. Keep in mind that you enter array formula's by pressing Ctrl-Shift-Enter. Excel will present the formula in curly { } braces for you, but you do not enter them yourselves.

You could even use the range A:A, but I do not know what impact that'll have on performance.
Andrew McQuillen    04 Jun 2013, 06:19
Hi Jorge,
I have been looking at this for another issue i had.
Place this code in A55
=INDIRECT("a"&COUNTA(A1:A54)-0)
This will look at all the values between A1 and A54 and give you the last used value.
if you want to look at all the values in column A then you will need to move the piece of code to another location to avoid a circular reference error.
the code to look at the whole of column A is
=INDIRECT("a"&COUNTA(A:A)-0)
this will work in any cell which is not in column A.
NB there should be no missing cells of data ie nothing in row 4 but something in row 5. this will stop it working.
Hope this helps.
Andrew
Jorge Vega    03 Jun 2013, 13:48
Need to get my last cell with data on to another cell.

     A
1 $213.00
2 $256.33
3 $100.00

I need to get A3 to A55 cell.
when A4 get updated then A55 will show A4.
When A5 get updated then A55 will show A5.

Hope you can help me here.
Blaine    05 Jul 2012, 21:53
This works - I am able to use the last cell in a particular column in another cell - HOWEVER... what I want to [also] so is add the last cell from rows in other worksheets in the same workbook. How do I do that? What am I missing? Thanks!

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.