Automatically find the last filled cell in a Google Sheets column

Other Questions Google Sheets
1

Hey everyone, I’m trying to figure out a way to make my spreadsheet more efficient. Right now, I’m using this formula to work out the days between two dates:

=DAYS360(A2, A35)

The problem is, I have to keep changing that A35 part every time I add new stuff to my sheet. It’s getting annoying!

Does anyone know if there’s a trick in Google Sheets to automatically spot the last cell with data in a column? I’d love to use that in my formula instead of manually updating it all the time.

I’m not great with advanced formulas, so a simple explanation would be super helpful. Thanks in advance for any tips!

2

hey Nova56! yea, theres a neat trick for that. try using the COUNTA function. it’ll count non-empty cells in a column. so you could do something like:

=DAYS360(A2, INDIRECT(“A”&COUNTA(A:A)))

this should grab the last filled cell in column A automatically. hope it helps!

3

There’s actually a more efficient way to handle this without using INDIRECT. You can utilize the INDEX function combined with MATCH to dynamically find the last non-empty cell in column A. Here’s the formula:

=DAYS360(A2, INDEX(A:A, MATCH(1E+100, A:A)))

This approach is generally faster and more reliable, especially for larger datasets. The MATCH function looks for a very large number (1E+100) in column A, effectively finding the last cell with content. INDEX then retrieves that cell’s value. This method also updates automatically as you add or remove data from the column.

4

I’ve been in a similar boat, Nova56. After struggling with manual updates, I stumbled upon a nifty solution using the FILTER function. Here’s what worked for me:

=DAYS360(A2, FILTER(A:A, A:A<>“”))

This formula dynamically filters out all non-empty cells in column A and returns the last one. It’s been a game-changer for my spreadsheets, especially when dealing with frequently updated data.

One caveat: if you have a lot of data, it might slow down your sheet a bit. In that case, you could limit the range, like FILTER(A1:A1000, A1:A1000<>“”), to speed things up.

Give it a shot and see if it solves your problem. It’s made my life so much easier!