Excel Sequence Function
HTML-код
- Опубликовано: 26 июл 2024
- In this video, You will learn how to use the SEQUENCE function in Excel. I will demonstrate the use on several Real Life examples that you should be able to convert to your needs in no time.
This tutorial was made using Excel 365 and since the SEQUENCE function is one of the new Dynamic Array Functions in Excel it is only available in Excel 365.
Blog Post on the Topic: bit.ly/3EjTNXU
Chapters:
0:00 Excel Olympics Animation
0:08 Introduction to the SEQUENCE function
1:21 Simple Examples
2:38 Combining with other Excel Functions
3:25 Creating a Date SEQUENCE
5:11 Dynamic Calendars Using SEQUENCE function
7:07 Single Column sorting of Values
9:09 Transformations using SEQUENCE function
12:00 Food for Thought
15:00 Outro
Gear Used:
US Links
Camera: Canon EOS R6 - amzn.to/3e9zxML
Microphone: AKG C3000 (Analog Mic) - amzn.to/3tYd37w
Audio converter: Zoom UAC-2 - amzn.to/3dgM8wS
Laptop: Dell XPS 15 - amzn.to/3w8CyoK
Lights: Elgato Key Light - amzn.to/3rurte7
EU Links
Camera: Canon EOS R6 - amzn.to/3tubwGy
Microphone: AKG C3000 (Analog Mic) - amzn.to/32pi2m0
Audio converter: Zoom UAC-2 - amzn.to/3amDRXP
Laptop: Dell XPS 15 - amzn.to/3effMmT
Lights: Elgato Key Light - amzn.to/3vcs6v9
Green Screen (visible in bloopers): amzn.to/3ajUm6Y
Recorded With: Camtasia - www.techsmith.com/video-edito...
Subscribe for more Excel tutorials on the Excel Olympics RUclips Channel: bit.ly/EOYTsub
Subscribe for the Excel Olympics Newsletter: bit.ly/3w3rkjK
Disclaimer: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!
#Excel #ExcelOlympics #gasperkamensek
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
Brilliant !!!
DAs are back! Yesssssssss! and off to a great start.
Thanks, Roderick. It looks like I made your day, and I couldn't be happier for it! See you on the next one :)
Another brilliant one! Thanks, Gasper!
The idea that you can transform single-column data into a table is just amazing for small data sets.
Thanks for the kind words Stefan. Yes. It makes a lot of transformations possible and is therefore extremely useful.
Wow man. Thank you, Thank you, Thank you. I have been cramming excel video tutorials for the past week. Your videos are exactly what I was hoping to find. Thank you, I can tell you really enjoy excel. It makes learning a lot easier. :)
Great Stuff! Thanks Gasper.
Thanks David.
Hi Gasper. Love that INDEX/SEQUENCE formula for unstacking! SEQUENCE is definitely awesome. Thanks for sharing :)) Thumbs up!!
Thanks Wayne. A lot of very cool demos didn't make the cut with this video but I knew I had to show transformation tricks.
This was a great video explanation of SEQUENCE formula! INDEX/SEQUENCE is great!!
Thanks Walmer.
incredible capability...limit is your imagination to use this dynamic formulas!
True Vasif. Like with all Excel functions. It's not just their primary role. Their true value is determined by their capability of combining with other functions and this is where the dynamic arrays truly shine.
brilliant and inspiring ... thanks a lot
Thanks!
Okay - that is BRILLIANT!
Spot on Jared.
11:22 GOLD! Thank you!
True Irene. That transformation was always a Power Query thing but now... It's a formula thing :)
Great viideo, thanks Gašper!!!
Thanks Davor. Means a lot!
Gasper, Thanks for the video. I become "nervous" when you set Ctrl+1 to change number format. You could do it quickest by clicking in the the number section of the home tab. 😁😁😁🤣🤣🤣
I hear you Javier. Isn't it funny how at one point we all think there is only one correct way to use Excel 😃. No worries. We all do it 😃
Great video again. Thanks a lot Gasper. I definetly have to use dynamic arrays more often in my reports....Very inspiring. Would love to see a video about dashboarding with dynamic arrays instead of pivot tables etc.;)
That's great feedback Dennis. I do have in mind a "Let's put it all together" video at the end of the Dynamic Arrays Series after I feature all the new functions. It wasn't going to be a dashboard but I think that is a great idea and I think that is the direction I will take with it. Thanks a lot 😀
Wow! Fantastic. Thank you so much.
Glad you liked it Joshua. Hope you got great ideas you will transfer to Excel.
@@ExcelOlympics Yes I do. I have used the sequence formula to extract certain days of the year. Great!
Great Joshua!
Hi Gasper, thanks for your segment on incremental months in rows using a combination of DATE and SEQUENCE formulae. Excel reads your formula as beginning at the start of the year - January, since SEQUENCE(18) will start with 1 (and then till 18).
Now, I wanted the months to start at December. So I modified your formula with this to get my result - starts at December 2022 ends at May 2024 (18 months)
=DATE(2022,SEQUENCE(18,,TEXT(DATEVALUE("1/12/2022"),"mm"),),1)
Thought I'll share with you and your audience.
Are there any alternate methods?
Great video.
Pls how did you change it from numbers into date ?
Hi Adedotun, it was just good old cell format. Ctrl+1 or right click and select Format Cells.
What if there are blank arrays, item, description, qty, unit price, total price are the columns, description is on multiple arrays within 1 column, i.e a BOQ of construction company
Hello Gasper! Great video on how to use the sequence function. I have a question, for example, when creating a calendar or warehouse plan, is it possible to split the sequence? For example, if the sequence is from 1 to 20 but after 10 split and make two cells empty and then continue the sequence from 11 to 20?
Once again, many thanks for the interesting analysis of the sequence function.
Hi aleksander. Sure its possible. I would do it like this
=LET(MyName;SEQUENCE(22);IFS(MyName
Hello Gasper! It`s just woow! I would never have thought of combining the two functions in this way. Please excuse me for the late answer! I had seen your answer only now. And immediately tried your solution and am just thrilled! Thank you so much for the neat solution!
Don't mention it Alexander. Glad to hear you found the solution valuable.
@@ExcelOlympics Hello Gasper,
I have managed to combine the two sequences together so that, it the first sequence in each column remains the same and with each new column is incremented by one. And the second sequence increments by one in each row and column. Now the next challenge is, how to get empty columns after each column?for example 3 empty columns. I have already tried a few things but can't come up with the solution. I will be very happy to see your solution.
=LET(left_num;"0"&SEQUENCE(1;5;1;1);dash;"-";right_num;SEQUENCE(20;1);IFS(right_num
@@Alexfm2000 =IF(Formula="","","0"&MAKEARRAY(ROWS(Formula),5,LAMBDA(r, c, c))&"-"&Formula)
I want to make a sequence LIKE THIS; AAA AAB AAC AAD AAE AAF AAG.......AAX AAY AAZ ABA ABB ABC ABD and so on. How can I do this?
How can sequence function be used so it starts renumbering under a certain condition:
If cell=male, then start numbering, other wise restart numbering if cell= female
Not with the sequence function itself but with the subtraction of a number that would be conditional...
Hi Gasper, hope you can help me, I need to create the following sequence
1+00
1+10
1+20
...
1+90
2+00
2+10
...
3+00
3+10
...
I have been using the following formula ="1+"&SEQUENCE(10,1,0,10) but it only works for the first 10 cells then I have reentered the formula and change the 1st number.
Yes cuz you also need to make the 1,2,3 sequence which you do by creating a sequence starting with 1 and going up by 0.10 sand then you wrap this sequence in ROUNDDOWN(SEQ...,0)
Is there a sequence that will allow 2 rows with same number series? Like A1 =1; A2=1; A3=2;A4=2..and so on…
Sure thing but in two steps. Your SEQUENCE function would have a step of 0.5 which gives you a sequence 1, 1.5, 2, 2.5... and then you do a ROUNDDOWN to a whole number.
@@ExcelOlympics you’re a genius. Thank you ❤️
i dont have this formula in my excel 2019
how do i get it pleeeeeeeeees
The Sequence function is only available in Excel 365, but the good news is you can get the online version of that for free.
@@ExcelOlympics thnks for Résponding 🥰🥰🥰
Don't mention it.
@@ExcelOlympics 🥰🥰🥰