Excel: Formula To Find Last Valid Cell in a Range

 Last reviewed: November 26, 1997
Article ID: Q85234
3.00 4.00 | 3.00 4.00 | 3.00 
MACINTOSH | WINDOWS   | OS/2
kbother

The information in this article applies to:

  • Excel for Windows, versions 3.0 and 4.0
  • Excel for the Macintosh, versions 3.0 and 4.0
  • Excel for OS/2, version 3.0

Summary:

Microsoft Excel can return the value of the last valid cell in a range of cells using an array formula. The following formula is an example: 

   =IF(NOT(ISERROR(range)),OFFSET(startcell,COUNT(range)-1,0))
where "range" is the address of the cells containing the data and "startcell" is the address of the first cell of that range.

This formula is an array formula and must be entered by pressing COMMAND+RETURN on the Macintosh, and by pressing CTRL+SHIFT+RETURN for Windows and OS/2.

More information:

If you had a range of cells containing data and you wanted to return the last entry of that range, the formula described above would return the desired result. The following is an example: 

   A1: 23
   A2:  7
   A3: 78
   A4:
   A5:
   A6: =IF(NOT(ISERROR(A1:A5)),OFFSET(A1,COUNT(A1:A5)-1,0))
Cell A6 would return 78. However, if the cell A3 was blank and A4 contained 78, A6 would return a zero. This formula requires data entries to be contiguous, with blank cells, if any, at the end of the range.

References:

"Microsoft Excel User's Guide 1," version 4.0, pages 156-157

"Microsoft Excel User's Guide," for Windows or OS/2, version 3.0, pages 119-120

"Microsoft Excel User's Guide," for the Macintosh, version 3.0, pages 113-114

KBCategory: kbother
KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00 max

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 26, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.