Retrieving the last value in a column is an easy task in a database, but Excel isn’t a database (even though a lot of people use it as one). Accomplishing this task is a bit harder in Excel, but only a little. In this article, I’ll show you a formula that works with a Table object and a regular data range. I’ll also show you the VBA equivalents.
I’m using Excel 2016 on a Windows 10 64-bit system. For your convenience, you can download the demonstration .xlsx and CLS files. The data set formula won’t work in the earlier menu versions, and macros don’t work in the browser edition. The browser edition doesn’t recognize the Table object, but the formula still works–Excel is smart enough to use the Table object’s structural references if you open the file in the browser.
SEE: Build your Excel skills with these 10 power tips (TechRepublic PDF)
Use a Table
Thanks to Excel’s Table object, this sort of task is simpler than it used to be. The formula uses structural references, so it’s long, but the formula references only the Table. That means you can position the formula almost anywhere and you can enter data above and below the column in question. In addition, structured references are easier to read than cell references; they’re similar to range names in this respect.
Figure A shows a simple Table of invoice data and a formula (E2) that returns the last date in column C, Invoice Date. As you can see, the formula and the Table accommodate the Table’s Total Row and adjacent data.
Figure A
This formula returns the last date in column C.
The formula uses the structured references to the Table and the Invoice Date column:
=INDEX(Invoices[Invoice Date],COUNTA(Invoices[Invoice Date]))
To understand how this formula works, you need to know how the INDEX() and COUNTA() functions work:
- INDEX() returns a value or reference of the cell at the intersection of a specific row and column within a given range using the syntax INDEX(array, row, [column]).
- COUNTA() counts the number of cells that aren’t empty within a specific range using the syntax COUNTA(value1, [value2]).
To learn more about Excel’s counting functions, read The scoop on Excel’s counting functions.
The COUNTA() function returns 30 because there are 30 rows in the Invoice Date column, excluding the header and total rows. (You can include these rows in a count by including the [#All] identifier.) Because array has only one column, the column argument isn’t necessary. Because COUNTA() returns 30, the INDEX() function returns the value in Invoice Data at row 30, 8/7/2017. The formula continues to work if you move the Table, if you add a row above the Table, and if you add data above or below Invoice Data.
SEE: 10 time-saving tips to speed your work in Excel (TechRepublic)
Although flexible, the formula has one flaw–it can’t handle a blank cell. COUNTA() evaluates all values, including text and even an empty string, “”. However, it doesn’t count blank cells. Consequently, the count returned will be the total number of rows minus the number of blank cells. If a record has no value, enter 0 or NA.
You can use VBA to accomplish the same thing using the code in Listing A. The End(xlDown) property refers to the last cell in the Invoice Date column. As before, the Table references know where the last row is, so text below the Table isn’t a problem. However, this reference does include the Totals row, which the Offset property accommodates.
Note: Don’t try to copy the code directly from this web page. Instead, download the CLS files or enter the code manually.
Listing A
Function ReturnLastValueTable()
  'Return last value in specified column.
   ReturnLastValueTable = Range("Invoices[Invoice Date]"). _
     End(xlDown).Offset(-1, 0).Value
   Debug.Print ReturnLastValueTable
End Function
Use a data range
The same general formula works without the Table, but it’s less flexible. In Figure B, you can see the formula has the same form but uses cell references:
=INDEX(C:C,COUNTA(C:C))
This time, the COUNTA() function returns 31 because it counts the header cell in row 1. Therefore, the formula returns the value in row 31 in column C, 8/7/2017. If you have a row of totals, you must accommodate it as well.
Figure B
The same formula in a regular data range works but is less flexible.
The formula works with a normal data range, but it’s less flexible. You can’t move the data set because the column references won’t update. Inserting rows above the data set can throw off the calculation since COUNTA() won’t evaluate the blank rows above the data set–COUNTA() returns the number of cells that contain data. However, if the cells above the data set in column C contain data, the formula still works. I don’t recommend that kind of arrangement because you must remember this detail. You can’t enter data below the data set because the column reference in COUNTA() evaluates the entire column; entering data below the data set will throw off the count.
You can accomplish the same thing using the code in Listing B. The End(xlDown) property stops at the first blank cell. That means you can enter data below the data set in column C if there’s at least one blank cell between the last row and the new data.
Listing B
Function ReturnLastValueDataSet()
   'Return last value in specified column.
   ReturnLastValueDataSet = Range("C1").End(xlDown).Value
   Debug.Print ReturnLastValueDataSet
End Function
Last call
This quick study isn’t comprehensive, but it should be adequate for most situations. The Table formula is more flexible, but neither formula accommodates a blank cell. If there’s no valid value for a record, enter 0.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Also read…
- Office 365 tip: Using Add To AutoCorrect to make you more productive (ZDNet)
- Become an Excel VBA Expert for Beginners (TechRepublic Academy)
- How to use a conditional format to highlight subtotal rows in Excel (TechRepublic)
- Two ways to speed up Excel macros (TechRepublic)
- Office Q&A: Reclaim old options and quickly find related messages (TechRepublic)
