I use the following function

=DAYS360(A2, A35)

to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet.

Is there a way (in Google Sheets) to find the last non-empty cell in this column and then dynamically set that parameter in the above function?

12 Answers 12


There may be a more eloquent way, but this is the way I came up with:

The function to find the last populated cell in a column is:


So if you combine it with your current function it would look like this:

  • 1
    I am not really bothered about eloquence in this case as long as it works like a charm (which it does) - thanks a lot Sam! – MichaelS Nov 17 '11 at 9:46
  • 4
    I ended up comparing with the empty string rather than using ISBLANK, which treats some empty-looking cells (e.g. blank-returning formulas like ="" as non-blank. Thus: '=index(filter(A:A, A:A<>""), rows(filter(A:A, A:A<>"")))' – circlepi314 Oct 4 '16 at 19:30
  • I also had problems with the ISBLANK because of formulas. But using =INDEX( FILTER( F3:F; F3:F<>"" ) ) ; ROWS( FILTER( F3:F; F3:F<>"" ) ) results formula parse error. Any idea what is wrong? – klor Sep 7 '18 at 9:36
  • Question. If instead of A:A, there is an importrange(), how can this be rewritten without doing the same importrange() 4 times? – Ruby Mar 6 at 21:54
  • Slightly simplified answer that also handles blanks, by Doug Bradshaw: =INDEX(FILTER(A1:A,NOT(ISBLANK(A1:A))),COUNTA(A1:A)) (can change starting row A1) full description at: stackoverflow.com/a/27623407/539149 – Zack Morris Jun 18 at 18:49

To find the last non-empty cell you can use INDEX and MATCH functions like this:

=DAYS360(A2; INDEX(A:A; MATCH(99^99;A:A; 1)))

I think this is a little bit faster and easier.

  • 1
    This is simpler and I have tested this. Works great. – Ciaran Dec 30 '14 at 15:43
  • 18
    Your method appears to only find the last cell if the value is a number, my method will find the last row if is also of type string. The original question was about dates, so this would work, but seeing as the article still gets attention, it is worth noting the difference. It all depends on your needs. If you just need a number I recommend this way. – Sam Plus Plus Dec 30 '14 at 16:39
  • 2
    Here's another variant stackoverflow.com/a/13871043/44620 – Jonas Elfström Nov 2 '15 at 16:10
  • 3
    Can you please explain why/how this works? – glennr Dec 8 '17 at 8:27
  • This assumes the column is sorted – Andrej Adamenko Mar 18 at 18:32

If A2:A contains dates contiguously then INDEX(A2:A,COUNT(A2:A)) will return the last date. The final formula is

  • 8
    Personally I think this should be the accepted answer. Concise and simple. – SwampThingTom Oct 31 '16 at 17:30
  • 2
    This is the most precise answer. While @Poul 's answer above works for this case, I wanted to find the actual last cell with actual data and this gets it whether the data is in order or not. – Chuck Claunch Mar 5 '17 at 3:13
  • 2
    This is perfect. Second the motion that it should be the accepted answer. – shiri Aug 14 '18 at 20:41
  • 1
    Should be the accepted answer – user1452962 Jul 7 at 15:13

My favorite is:


So, for the OP's need:

  • ... although the "MAX" one posted Poul above is cleaner looking in the case when the last date is always the latest. – Eric Smalling Mar 31 '17 at 13:34
  • No point adding the ,1 at the end, is there? – The Pellmeister Oct 6 '17 at 12:37
  • 1
    He meant +1 not ,1 – Newbrict Aug 4 '18 at 22:55
  • This should be the accepted answer. – Happy Bird Apr 6 at 11:03

If the column expanded only by contiguously added dates as in my case - I used just MAX function to get last date.

The final formula will be:

=DAYS360(A2; MAX(A2:A)) 
  • 3
    Genius! Simple and effective. – Asu Sep 1 '16 at 16:57

Although the question is already answered, there is an eloquent way to do it.

Use just the column name to denote last non-empty row of that column.

For example:

If your data is in A1:A100 and you want to be able to add some more data to column A, say it can be A1:A105 or even A1:A1234 later, you can use this range:

  • 1
    I like this, but have seen issues where it sometimes returns the next-to-last item instead of the last one. – Eric Smalling Mar 31 '17 at 13:03
  • 1
    @EricSmalling that may happen because you have one extra linefeed at the end of all rows and when you copy paste it, it may be considered as non-empty cell. Do you have any sample spreadsheet exposing this issue? – Ramazan Polat Mar 31 '17 at 15:15

What about this formula for getting the last value:


And this would be a final formula for your original task:


Suppose that your initial date is in G10.


Here's another one:


With the final equation being this:


The other equations on here work, but I like this one because it makes getting the row number easy, which I find I need to do more often. Just the row number would be like this:


I originally tried to find just this to solve a spreadsheet issue, but couldn't find anything useful that just gave the row number of the last entry, so hopefully this is helpful for someone.

Also, this has the added advantage that it works for any type of data in any order, and you can have blank rows in between rows with content, and it doesn't count cells with formulas that evaluate to "". It can also handle repeated values. All in all it's very similar to the equation that uses max((G:G<>"")*row(G:G)) on here, but makes pulling out the row number a little easier if that's what you're after.

Alternatively, if you want to put a script on your sheet you can make it easy on yourself if you plan on doing this a lot. Here's that scirpt:

function lastRow(sheet,column) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (column == null) {
    if (sheet != null) {
       var sheet = ss.getSheetByName(sheet);
    } else {
      var sheet = ss.getActiveSheet();
    return sheet.getLastRow();
  } else {
    var sheet = ss.getSheetByName(sheet);
    var lastRow = sheet.getLastRow();
    var array = sheet.getRange(column + 1 + ':' + column + lastRow).getValues();
      if (array[i] != '') {       
        var final = i + 1;
    if (final != null) {
      return final;
    } else {
      return 0;

Here you can just type in the following if you want the last row on the same of the sheet that you're currently editing:


or if you want the last row of a particular column from that sheet, or of a particular column from another sheet you can do the following:


And for the last row of a particular sheet in general:


Then to get the actual data you can either use indirect:


or you can modify the above script at the last two return lines (the last two since you would have to put both the sheet and the column to get the actual value from an actual column), and replace the variable with the following:

return sheet.getRange(column + final).getValue();


return sheet.getRange(column + lastRow).getValue();

One benefit of this script is that you can choose if you want to include equations that evaluate to "". If no arguments are added equations evaluating to "" will be counted, but if you specify a sheet and column they will now be counted. Also, there's a lot of flexibility if you're willing to use variations of the script.

Probably overkill, but all possible.

  • 1
    "Unknown function LASTROW" – Berit Larsen Sep 27 '18 at 8:06
  • Should be +100 for actually answering the question headline: Last row, not value in last row – Norbert van Nobelen Oct 19 '18 at 19:54

I went a different route. Since I know I'll be adding something into a row/column one by one, I find out the last row by first counting the fields that have data. I'll demonstrate this with a column:


So, let's say that returned 21. A5 is 4 rows down, so I need to get the 21st position from the 4th row down. I can do this using inderect, like so:


It's finding the amount of rows with data, and returning me a number I'm using as an index modifier.


Calculate the difference between latest date in column A with the date in cell A2.


for a row:


for a column:


The way an amateur does it is "=CONCATENATE("A",COUNTUNIQUE(A1:A9999))", where A1 is the first cell in the column, and A9999 is farther down that column than I ever expect to have any entries. This resultant A# can be used with the INDIRECT function as needed.

  • Sorry. This only works if all the entries in the column are unique. – Conrad Lindes May 20 '16 at 21:08
  • 1
    While I appreciate your contribution to StackOverflow, Conrad, your answer has some issues. First, he didn't specify that the dates were unique, so you should use count(), instead of countunique(). Second, using indirect() and concatenate duplicates the existing functionality of index(), as you can see in the other answers. Third, instead of A1:A9999, why not just use A1:A? – sondra.kinsey May 16 '17 at 20:33

protected by Community Feb 2 at 0:10


Would you like to answer one of these unanswered questions instead?
