Google Sheets - Get Last Non-Empty Cell in Row or Column
ฝัง
- เผยแพร่เมื่อ 7 ก.พ. 2025
- Learn how to get the last non-empty cell in row or column. In this tutorial we'll first create a formula to get the row number for last non-empty cell and then get the data value in the the cell.
#googlesheets #lastrow #lastcolumn
TH-cam is amazing. I just picked my car apart and put it back together. Now Im learning excel whoop whoop!
Was poking around on the internet looking for a solution to this. Most of them were convoluted for no reason. This was straight to the point! Thanks for the great tutorial!
Hello. I was watching your videos for the last 3 days and i can tell you the way you explain is perfect. I am SAP consultant with ABAP development skills but i wanted to do something for me with google sheets and java script in which i never worked before ( only excel formulas ). Based on your videos i was able to write my own little program to track some consumptions in the house, etc...by passing values to google sheets and getting values from google sheets in java script. Great teacher !
As always, you make it easy with no boring explanations, thank you, I'm glad I've found your channel !
OMG, your channel basically covers all the functions needed for all situation !!
Thank you so much! I've been struggling so hard to do this for a long time.
What a sophisticated solution to multiply by 0 if the column is empty.
best explanation ever....
I was always afraid of spreadsheet formulas but you made it super easy and understandable for me!
Thank you so much!
Great Tutorial! Quick and to the point! Thank You
Great way of explaining! You are a born teacher! Thanks form the bottom
of my heart!
Your way of teaching is really cool and intuitive. As always, really good video!
Yes! Not just "paste this code" but building the formulas. Then I can tell how my code will be similar/different.
Thank you for taking you time to make a video about this. This is really helpful. God bless!
That is extremely useful. I have combined it with the indirect function to use with dynamic dropdown lists which is something I have been trying to do for a few weeks using information from another of your videos, but couldn’t get it to work.
superb video....i am learning from your videos.... very help for archive my automation tasks....from Sri Lanka
Nice idea. It works too for a range : =max(arrayformula(ROW(A1:H)*(--(A1:H"")))) Very cool when used with indirect function
Exactly what I needed, Thank you for this educational video!
Very good videos Sir great 👍👍
Your videos are amazing. I have learned a lot. Thank you !
Great video. Thanks. Just to add that if you don't start form Column 1 or Array 1, you need to manually offset the value for MAX
This one has a problems if the formula is in the actual row analysed. =(INDEX(FILTER(H44:44;NOT(ISBLANK(H44:44)));COUNTA(H44:44)) This one solves the problem.
god bless you! thank you very much!
Great video, thank you for sharing this method!
Everything works right up until 12:08 with the new INDEX function. The cell simply remains empty!
Same
Thanks for this amazing video. Very easy to understand and great shortcuts shared as well
that was some genius move, really. thanks for that!!
You are AWESOME. Thank you!
Are you sure you're not David Copperfield! Great stuff, these videos are priceless, Thanks
Thank you so much ! This is very well explained ! :D
Your videos are amazing!
Thank You!
thanks god I found this helpfull tip about COUNTA, which I use now in an empty cell on top to get the number of rows in my App-script.
I had a big problem with an ArrayFormula within the Data-range and had a ton of non-blank rows in my search-result.
👍
Oh my. Thank you so much. This helps a lot 🙂
If possible, I will give you a millions like bro. You solve my big problem with a small trick. 😍😘
😀
That's fantastic! Very useful technique.
Superb!! highly appreciated
This is such a great video. Great method of explaining!
Thanks a lot, exactly what i was looking for !
Fascinating way to tackle the problem. I've played with this formula a bit and it is quite handy. I am, however, curious if it is possible to adjust this formula to do the same, but instead return the second last item in the list?
Use LARGE instead of MAX
That's elegant! Thank you for sharing!
You are the GOAT
love it
thanks. Sir.
I find it long time
You have amazing explaining skills, thank you so so very much for your video!!!!!
:)
Good explication, thank you so much
How can I get this to work in an array formula? To just fill out the entire column?
Thank you very much.
You’re the best!! Thank you for sharing this!
Excellent, thank you!
PLEASE, MY SHEET DID NOT RETURN THAT RESULT "GRAPES" @ THE TIME STAMP: 12:29 ; WHAT COULD BE THE REASON? I ENSURED THE FORMAT WAS ON "AUTOMATIC"
beautifully explained as always - many thanks
You da real MVP! Thank you.
Does this work if the rows you're trying to array index are outputs of another formula?
Great video, you explain everything well and in detail. However, in my case the result comes as an empty cell.
I figured it is because my array starts from the third row in a column, and my result should be in the second row of the same column.
Any way to fix this?
This is a fantastic job. Love your videos
Thanks. Very well done.
is there a way to 2nd to last value, 3rd to last, etc? Love your videos
add -1 -2 -3 after MATCH function
Hi I love your Videos have learnt so much from them and been able to do so much but i do have 1 question. Is there a way to use this formula to get the last known number from spasific date? I work with a group from germany and we get funds in euros but convert them to IDR when they arrive. The currency exchange rate changes a lot and quite drasticly over the years. I want to be able to get the euro prices for all expensise we have made. To do so correctly i wanna use the date of the expense to find the last time we converted euro to IDR to get the exchange rate. Hope u can help me
I love you! and your videos! thank you so much!!!
please let us know sum function in google query function, i mean to say that total showing in bottom like if ram sales repeat in 5times than not required group total of ram, we required total showing on both with each entry of ram ,
Hi sir...
New type of thing I have learned.
Please make more videos on merge google sheets with google docs without add-on. For certification or pre-defined letter pads like structure.
Thanks and regards
Waiting for your response.
Google docs Mail Merge w/o add-on available from his previous video:
th-cam.com/video/QNPPEB64QbI/w-d-xo.html
Maybe we both want w/o add-on and no scripts too.
Waiting for that too.
Meanwhile, I guess pre-printed forms and a tab with query to display all necessary data should do the job.
Hi! Im a data analyst here from the philippines, Im working with my data table which leads me to here (your video), Although, what im really looking for is: how can I add a text after the last value on my data (row) like for example: if the last value is at row 10, row 11 automatically shows "Grand total" or any text that would fit the data table. Thanks a lot for your help!
thanks
So amazing!! Thank you so much!
Glad you like it!
Dude this was awesome work, thanks
Dude... You are one Smart Guy! How would you make Flash Cards that Auto Expand in Google Sheets?
thank you so much. You just save my day!
This video was very helpful in what I have been trying to do but I am needing to get the data form a different column. I have a sheet that the array formula would be for column F but I need to get the data from column E based on the array. How is the best way to do that? I also need to have the same data displayed on a separate sheet within the same workbook. I have found your videos very helpful. Trying to learn google. Thank you
When you use INDEX function just highlight column E
th-cam.com/video/iGvvK8O5BpQ/w-d-xo.html
Check out this video th-cam.com/video/iGvvK8O5BpQ/w-d-xo.html
Watch this video th-cam.com/video/iGvvK8O5BpQ/w-d-xo.html
th-cam.com/users/LearnGoogleSpreadsheetssearch?query=google%20sheets%20linking%20data
Hi, thank you for this great video. My question is: Now that I have known the last row, how would I be able to concatenate or join them to make a simple range? For instance I have A1:A & maxrow. How can attach that number to the A1:A to make a range with the lastrow? I hope I am making sense. Please help.
I need to use the row number he finds, in a cell. e.g. he find 8 as the row number. How i can use in another function C8 ?
Another awesome video! thanks. you should open a patreon too. I wouldn't mind chipping in some beer money
But please keep making. I'm loving learning from your videos
what about if used in data filtered columns to get last value ?
Me: "Why can't I figure this out, how hard can it be? It's probably a simple function like _SUM()_, that I just don't know about."
Tutorial: 16 minutes long
Me: "oh boy..."
you are the best
Thank you. Very helpfull for me
thanks so much
thanks for the learning's. if Instead of row no.., I want to get the value in the last non empty cell, what changes do I have to make.
He showed it in the video
i really like your video because you explain it broken down to dum bits. However I have a question, I don't want to disable the re-iterative calculation but if I inquire about the last filled row on column A in a cell located in column A, what to do with the circular dependency?
The tutorial was very clear and well done, but can someone explain what this formula is typically used for in a real world situation?
I have a trading journal where I populate each new cell with a win or a loss in EUR. I have another cell "Current Balance" which only needs to check the last populated cell and take the last value - the new balance on my account.
Thanks so much for this. However, the row function points to an absolute row position starting from Row 1. My array doesn't start from the first row. Is there a way to reference a relative value such that Row 1 is seen as the row where my data starts rather than the actual first row on the sheet? #NoobAlert
Isn't the last row number still the same? Just select the whole column.
@@ExcelGoogleSheets But Index function is not considering absolute position. For it first entry in the column is the first position.
I just subscribed :)
👍
So I need to do some kind of index and match function that incorporates this. I want to see the last status of en employee. So i need this to match with a certain employee based on their last status. What you did in this video is close to what I need. But I also need to figure out how to tell the last status of each worker all in the same column. any advice on this?
Excellent explained master, but I want know if is possible with scripts web app google create an user and password depending of spreadsheet
How can add a filter to this? like give me the last row for a specific fruit. Where abouts would i add the filter?
Hello good sir,
I have a question - suppose you want to make a list of all the fruits after banana and the list is not uniform i.e. sometimes banana might be every other row, sometimes there are blank rows, sometimes the word banana might come up 3 rows later etc. Is there a reliable way to solve this problem?
Many thanks
Hi. Thanks for this video. I am looking in getting the last 5 rows (multiple columns in these 5 rows). Any way?
I would like to use what you have explained in this Video: “Google Sheets - Get Last Non-Empty Cell in Row or Column” The only additional thing is that I would like to sum up 2 cells in columns from 2 separate work sheets into my Dashboard Sheet. If this is possible, would you send me the formula lay out I need. I really enjoy your videos. I am still to expand my overall knowledge of google sheets. Thank you!
5:24 / 16:09
•
In the worksheet where you want the number there
type in the = then go to your worksheet and press the cell then enter and done
how about Get all non empty cell?
Showing REF# error..please help. And error says circular ref. Check spreadsheet settings
We have same problem.
How if we want to pick the second last item?
This works great BUT doesn't work when the blank cells have formulas inside. I have NOT been able to find anything that works in a blank range filled with formulas.
Good one. I need to request to combine multi column into one with same row values... Thanks
I need this formula to arrayformula down the column. It works in the initial cell, but doesn't populate down the column. Can that be done?
Hi, thank you for the video. Is there a way to get this to work for a range on another sheet? I've tried using importrange and keep getting an error for the COLUMN or ROW function.
ROW & COLUMN require a range, IMPORTRANGE returns an array.
You could use SEQUENCE function to generate similar results to ROW or COLUMN, but it could be tricky if you use open references.
How could I do an importrange() referencing a different sheet and pull the last non-empty cell from a column? For example, I've tried =Importrange("my.google.sheet.address", index(b2:b, counta(b2:b))), but the index and counta only seem to look at the sheet the formula is entered on, NOT the referenced sheet.
I've also tried =index(importrange("my.google.sheet.address", counta(b2:b)) without success also.
=counta(importrange("my.google.sheet.address","b2:b"))
@@ExcelGoogleSheets thanks for the quick reply...i actually kinda figured it out from a different video you published (th-cam.com/video/4XMB03BqNm4/w-d-xo.html) & just swapped in counta for match. 👍🏼
How can I highlight a last non blank cell in a column using conditional formatting.. ? Please help
How can we display the last record of the google sheet inside the google form whenever we open the google form?
you can't.
Thanks!
why does it show in 1 and 0, when u had "--" , what does "--" mean?
how get row number active cell automatic
?
Nice!
This formula can also be used =ArrayFormula(MAX(IF(A:A="",,ROW(A1:A))))
HEY ITS GOOD HELP TO ME, BUT I WANT THAT INDEX RETURNS ALL THE LIST WITHOUT CELL NUM HAVING ZERO OR BLANK CAN YOU HELP?
maybe FILTER function?
What if cell (A) contains value comes from a formula? And entire column has formula which produce nil value (means "empty") Then?
I tried but not working in above case.
AWESOME...YEAH!