This was the most amazing Excel tutorial I've seen thus far. Very simple to understant and it provides you with multiple options to create both a static number list and a dynamic one. I was also very grateful when the facilitator went the extra mile and explained how we could add a symbol next to our numbers!
I’ve found that this method stumbles when you add a new row at the bottom of a table (as in when you’re in the bottom cell and you tab to add the next row in the table automatically). It works perfectly if you add or delete rows within the table.
Ohh yeah this is great, I am starting to build a timetable and was wondering how to tell to Excel some ideas that I have. I think our brains are connected, this video is build for me 😁😁. Many thanks 🙏
Great video, great tricks!!✌ For fun, reference to "Dynamic Array" spreadsheet: more dynamic (using latest new functions VSTACK/HSTACK) =LET(a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(SEQUENCE(ROWS(a)),a)) -full dynamic, entire array: =LET(t,{"No.","Task","Assigned To"},a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),VSTACK(t,HSTACK(SEQUENCE(ROWS(a)),a)))
If we want to add number format: (f variable) =LET(f,"0.",a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(TEXT(SEQUENCE(ROWS(a)),f),a)) we can choose "0." or "(0)" or "0-"
@@simona7517 You can get them only if you are signed up for Beta channel. (for MS 365 users: Open Excel, go to: File/Account/Office Insider /Change Channel click Beta; Office Updates/Update now)😉✌
@@Excelambda Thanks and understood - I've always held off on Beta items as Excel is mission-critical for me for work, plus I don't want to get ahead of my colleagues from a compatibility perspective. So I just pine for the newest stuff and wait for GA. Cheers.
This helped me... =ROW worked a treat. Thank the heavens! Thank you very much! Then all i needed to do was copy the row of 7,000 and paste as value only. PROBLEM solved. Thank you!
Hello Genius, I never miss your any of the videos and you are absolutely amazing in this. Well, i have one query if you could guide me here, - Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query. - Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder? - as we know after refracing of query it would load fresh data only in model. basically, i want to preserve old data also in my excel where i have load the query. Thanking you in advance.
Very nice tutorial. Is there a way to add a sequence number automatically when you insert a new row above? The idea is from the table, the latest entry goes above the last data entered in a row.
Am trying to get the unique series of number including numeric and alpha ets say I have dataset of duplicate I'ds and IDs with same and in diff projects..want to give an unique series of number and it should not change it should be unique to map other datasets can we do it
hello Sir, i really appreciate your detailed work. Thank you so much. but i wan to request you please attach that file in the comments. also please share how did you apply data validation in Cell C3 in method 4 - Dynamic List with Dynamic Array Formula.
Hi John. I've spilled a single column via a filter of a list--however I want the spill to populate 3 columns of similar rows... the single column contains a simple word in each row. I'd like the spill to flow into maybe 3 columns of single words. Can you figure it out?
Very helpful. I have been chasing a re-numbering challenge for doing hierarchy or Outline numbering like MS Project can do. That is, 1.1, 1.1.1, 1.1.2, 1.1.3 If you want to insert a new row as a new 1.1.2, you need the existing 1.1.2 to become 1.1.3 and the current 1.1.3 to become 1.1.4 etc. This is needed when crafting a hierarchy that will then be imported into something that required hiearchial numbering or a way to understand child / parent relationships. e.g. 1.1.1 row is a child of row 1.1 Any help on how to do that is appreciated. Think of it as an ontology. That is, each row starts with its outline number in column A, then there are multiple columns that have properties for that row's main subject (the main subject likely is in column B) All the other columns have headers, and the content at each row is either from a fixed list or open list
Would be interested if you found a solution for this! I am trying to do exactly this for a research and evaluation framework, with outline structure in three levels: 1, 1.1, 1.1.1
Hi Jon, First, I'd like to say that your tutorial is so good because you are articulate on the subject matter. People inject words like: ah, um etc., which makes for disjointed speech or, what they are trying to show does not work so they lose me with my ADHD attention span! ... I wish I could upload a screenshot so that you could see I typed your formula correctly. I tried the 4th numbered list [=SEQUENCE(rows(b5#)], and it did not work. I tried it at home and work, and I get #REF! All of your other formulas work. What could it be? Thanks, and keep tutoring!
Hi Marci, Thank you for the nice feedback. There must be a spill range starting in cell B5. If not, then the formula will return the #REF error. You must also be on a version of Excel that supports dynamic array formulas and has the SEQUENCE function. This will be a modern version of Excel. Excel 2021 or later. I hope that helps get you started in solving this issue. Thanks again and have a nice day! 🙂
@@ExcelCampus Thank you so much for responding! I kept at that formula and what I found was that I was not closing the formula at the end with a paren! I think I kept missing it because of the header information in the formula. So simple yet so easily missed!
A very interesting tutorial. I am verifying that it does not allow me to customize the format using "." in the spilled-array with the SEQUENCE function.
Here is another way: =SEQUENCE(ROWS(A1)+COUNTA(table1)-1 ,1) table1 is the name of the named table. CountA will contain the number of rows in the table. The only problem with this formula is that if you insert a blank row, the table numbers will not update UNLESS you put some values in the empty rows. To overcome this issue, I used the count blank =SEQUENCE(ROWS(A1)+COUNTA(table12)-1 +COUNTBLANK(table12),1)
Hi Sheik, We have a video on 5 ways to remove blank spaces. Here is the link. ua-cam.com/video/zNLZcqOeMcw/v-deo.html I hope that helps. Thanks again and have a nice weekend! 🙂
This was the most amazing Excel tutorial I've seen thus far. Very simple to understant and it provides you with multiple options to create both a static number list and a dynamic one. I was also very grateful when the facilitator went the extra mile and explained how we could add a symbol next to our numbers!
Great one Jon! Another option is: =ROWS(A$4:A4) and copy down. I use that one often. Thanks for the tips. Thumbs up!!
Ah yes, great suggestion Wayne! Thanks! 🙏
I’ve found that this method stumbles when you add a new row at the bottom of a table (as in when you’re in the bottom cell and you tab to add the next row in the table automatically). It works perfectly if you add or delete rows within the table.
@@brucecheatham7667 True.. for CTRL+T tables, a different method is required. Thanks for the feedback.
Not sure how I missed this video when it came out, but great tips again, thanks Jon.
Thanks for posting this amazing videos on "Number List" in Excel.
Thank you for the tutorial. Very simple and easy to understand.
Thank, for your feedback, @radenmasandalusia! 😀
That SEQUENCE function is tremendous. Thanks!
Yes, there are a ton of uses for it and it's one of my favorite new functions.
Thanks. You instantly increase my knowledge.
That's great to hear. Thanks Bashir! 👍
Thank you so much for sharing wonderful tips, Jon.
Thanks Shiffa! 😊
Very nice Jon. Thank you very much. Eddy from Turin Italy
Thanks Eddy! 😊
I hv been this situation many time, its really cool stuff. Like as you said during sorting/ ranking not to use.
Thanks John for your great videos and effort,
Moreover I think subtotal is more dynamic especially in filtering :
=subtotal(3,$B2$:B2)
Ohh yeah this is great, I am starting to build a timetable and was wondering how to tell to Excel some ideas that I have. I think our brains are connected, this video is build for me 😁😁. Many thanks 🙏
Haha, I'm happy to hear it. Thanks Ismael! 👍😁
Amazing tricks for excel!
Just found this channel, great tips and clear explanations 👌 much appreciated!
Awesome, thank you for your feedback! 😀
Thanks Jon. Very useful tip 👍
Thanks Joydeep!
You saved my day! Thank you so much!
You're welcome, Julia! :)
Hello Jon,
Thanks for explaining those nice tips, Keep it up!
Cheers
Ravi
Thanks Ravi! 👍
Always good to know new methods. Thanks!
Happy to hear it. Thanks Orel!
Thank you so much Dear, I got a very usefull tips. good luck
Thank you so much for sharing!
Great examples and explanation. I greatly appreciate it.
useful tips thankyou Jon
Great tips! Thanks.
Great useful tips
Thanks Jon ✌🏻
Thanks Vinod!
Thanks so much for the learning.
Great video, great tricks!!✌
For fun, reference to "Dynamic Array" spreadsheet: more dynamic (using latest new functions VSTACK/HSTACK)
=LET(a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(SEQUENCE(ROWS(a)),a))
-full dynamic, entire array:
=LET(t,{"No.","Task","Assigned To"},a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),VSTACK(t,HSTACK(SEQUENCE(ROWS(a)),a)))
If we want to add number format: (f variable)
=LET(f,"0.",a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(TEXT(SEQUENCE(ROWS(a)),f),a))
we can choose "0." or "(0)" or "0-"
Whoa, that's some super slick stuff! I hadn't even heard of VTACK/HSTACK and don't have them yet in my Excel but cool.
@@simona7517 You can get them only if you are signed up for Beta channel. (for MS 365 users: Open Excel, go to: File/Account/Office Insider /Change Channel click Beta; Office Updates/Update now)😉✌
@@simona7517 this is the build nr:
Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15209.20000) 64-bit
@@Excelambda Thanks and understood - I've always held off on Beta items as Excel is mission-critical for me for work, plus I don't want to get ahead of my colleagues from a compatibility perspective. So I just pine for the newest stuff and wait for GA. Cheers.
Super Cool And Useful Tips...Thank You Jon :)
Thanks Darryl! 🙌
This helped me...
=ROW
worked a treat. Thank the heavens! Thank you very much!
Then all i needed to do was copy the row of 7,000 and paste as value only. PROBLEM solved. Thank you!
That's good to know, Elise! Glad it helped. 😀
Thank you, man.
Thank you Jon.
Just awesome 👍.
Great sir 😎
Succinct and Success. Great video, new sub! Thank you!!
Mind blowing 😊
Cool trick 👍
Thanks mutlunun!
ould you please link the video, that how did you create the Filter that you used sequence for that? 6:05
Thank you!
Thank so much it’s useful
Glad it helped, Rayan! 😀
Cool tips Bro 👍... Let's Row ()
Thank you 😊.
Haha! Thanks Karim! 🙌
BIG THANKS
You're welcome, Rere! :)
Hi Jon. Did you do a video on how to remove duplicates when column values can be swapped like (1,2) and (2,1) would be a duplicate. Thanks
I don't believe we have a video on that topic but we'll add it to our list for future videos. Thanks Matt!
@@ExcelCampus Thanks, just keep in mind that column values can be numbers, letters or any combination of both.
Hello Genius,
I never miss your any of the videos and you are absolutely amazing in this.
Well, i have one query if you could guide me here,
- Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query.
- Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder?
- as we know after refracing of query it would load fresh data only in model.
basically, i want to preserve old data also in my excel where i have load the query.
Thanking you in advance.
4:56 how to Skip Blank Row ? In Table (Ctrl+T)?
Great!
Thanks Jon, the sequence spill is great and i will use it. I am using if(A4="","",counta($B$4:B4). this will re sequence your numbers as well.
Very nice tutorial. Is there a way to add a sequence number automatically when you insert a new row above? The idea is from the table, the latest entry goes above the last data entered in a row.
Thanks
Hi thank you for video. But it appears to be #ref when I use =sequence(rows(D2#)), why is that ?
Am trying to get the unique series of number including numeric and alpha ets say I have dataset of duplicate I'ds and IDs with same and in diff projects..want to give an unique series of number and it should not change it should be unique to map other datasets can we do it
hello Sir, i really appreciate your detailed work. Thank you so much.
but i wan to request you please attach that file in the comments. also please share how did you apply data validation in Cell C3 in method 4 - Dynamic List with Dynamic Array Formula.
ok i got it. i watched it again. how did you create list in Cell C3. Thanks
Hi John. I've spilled a single column via a filter of a list--however I want the spill to populate 3 columns of similar rows... the single column contains a simple word in each row. I'd like the spill to flow into maybe 3 columns of single words. Can you figure it out?
Very helpful. I have been chasing a re-numbering challenge for doing hierarchy or Outline numbering like MS Project can do. That is, 1.1, 1.1.1, 1.1.2, 1.1.3 If you want to insert a new row as a new 1.1.2, you need the existing 1.1.2 to become 1.1.3 and the current 1.1.3 to become 1.1.4 etc. This is needed when crafting a hierarchy that will then be imported into something that required hiearchial numbering or a way to understand child / parent relationships. e.g. 1.1.1 row is a child of row 1.1 Any help on how to do that is appreciated. Think of it as an ontology. That is, each row starts with its outline number in column A, then there are multiple columns that have properties for that row's main subject (the main subject likely is in column B) All the other columns have headers, and the content at each row is either from a fixed list or open list
Would be interested if you found a solution for this! I am trying to do exactly this for a research and evaluation framework, with outline structure in three levels: 1, 1.1, 1.1.1
Hi Jon, First, I'd like to say that your tutorial is so good because you are articulate on the subject matter. People inject words like: ah, um etc., which makes for disjointed speech or, what they are trying to show does not work so they lose me with my ADHD attention span! ... I wish I could upload a screenshot so that you could see I typed your formula correctly. I tried the 4th numbered list [=SEQUENCE(rows(b5#)], and it did not work. I tried it at home and work, and I get #REF! All of your other formulas work. What could it be? Thanks, and keep tutoring!
Hi Marci,
Thank you for the nice feedback. There must be a spill range starting in cell B5. If not, then the formula will return the #REF error. You must also be on a version of Excel that supports dynamic array formulas and has the SEQUENCE function. This will be a modern version of Excel. Excel 2021 or later.
I hope that helps get you started in solving this issue. Thanks again and have a nice day! 🙂
@@ExcelCampus Thank you so much for responding! I kept at that formula and what I found was that I was not closing the formula at the end with a paren! I think I kept missing it because of the header information in the formula. So simple yet so easily missed!
I can not use #sign in Sequence function with Rows function.Show up Ref error. Help me.
A very interesting tutorial. I am verifying that it does not allow me to customize the format using "." in the spilled-array with the SEQUENCE function.
Yeah!!! All's is ok now!!! Thank you Jon!
Thanks Ivan! 🙂
Here is another way:
=SEQUENCE(ROWS(A1)+COUNTA(table1)-1 ,1)
table1 is the name of the named table. CountA will contain the number of rows in the table. The only problem with this formula is that if you insert a blank row, the table numbers will not update UNLESS you put some values in the empty rows. To overcome this issue, I used the count blank
=SEQUENCE(ROWS(A1)+COUNTA(table12)-1 +COUNTBLANK(table12),1)
How to remove the space before and after the contents in excel in large number of line items
try using the trim function
Hi Sheik,
We have a video on 5 ways to remove blank spaces. Here is the link. ua-cam.com/video/zNLZcqOeMcw/v-deo.html
I hope that helps. Thanks again and have a nice weekend! 🙂
Thanks for these wonderful tips always👍
# is giving REF error, in sequence formula, Please help