Excel Fill Number the right way using the SEQUENCE function
HTML-код
- Опубликовано: 9 июл 2024
- There are five ways to fill sequential numbers in Excel. One of the least used, but possibly the best way, is to use the SEQUENCE function. We will look at all five methods in this video, including using AutoFill and Fill series.
Chapters:
00:00 Introduction and example
01:15 Method 1
01:29 Method 2
01:57 Method 3
02:43 Method 4
03:20 Method 5
06:15 Closing remarks
SEQUENCE Function
The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4, 5, 6, 7.
Available for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2021, Excel 2021 for Mac, Excel for iPad and iPhone, and Excel for Android tablets.
Popular Excel Videos
• Create a PivotTable in...
• What-If analysis in Ex...
• Excel - Conditional Fo...
• Sum up hours and minut...
• Break-even analysis in...
• Excel: extract email a...
• Mail Merge with Outloo...
• Calculate the earliest...
• Excel: find the lowest...
#chrismenardtraining #microsoftexcel #chrismenard #msexcel
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
I've always known there is a better way than 1, 2, select, drag but never knew what else to do. This is great!
Perfect! I was looking for just this type of functionality. I'll be using this from now on. Thanks, Chris!
Your instructions are great because you get right to the point and you keep it simple.
I've always thought there has to be a better way to fill in numbers. Awesome. Thanks
You bet!
Fantastic !!! Thank you
Thank you Chris, I watched this video with closed captioning on because my wife is watching TV right beside me and I could follow you very well from the captioning.
Good to know. We use another app most of the time to make sure our captions are correct. Thanks for the positive comment.
Thank you, Chris! Very helpful. I knew there was a way to have the sequence automatically update. Enjoyed the video!
i use the method, put 1, click fill, click series, column and put in the number. thank u so much.
I like that method but if you delete a row, the numbers are off.
Thanks for the video. I use the sequence function pretty often for this kind of thing but also use the Row - header Row especially if there is any danger of the dataset being changed to an Excel table - as we all know spilling functions and tables do not play nice together. This also avoids problems if you want to get the data into Power Query in the same workbook and forget to name the range to avoid it being automatically changed to a table.
Thank you so much sir! This was very helpful
You are welcome!
I like the sequence funcation and have already added it into some of my spreadsheets. Thanks!
You're welcome!
This is great,,, I just used it and it works wonderfully! This channel is great!
I definitely use this function. Thanks.
Great info, Chris. Love the little shortcuts you show just in demonstrating other features. Once in a while there is a benefit of using Page Layout, Sheet and then check, Print Row and Column headings. Mostly it would qualify as an inappropriate way of numbering rows though.
Thank you thank you I’m trying to organize my audiobooks in sequence by author,title series, genre , narrator, length etc so I need to number them and this helped. Eventually I’ll want to sort everything but this helps for now. One step at a time. ❤
great info! very useful! thank you❤
Thanks for showing different ways to add numbers. Thanks
Happy to help
Thank you excellent tutorial
I will be using option #5.Thank you Much.
Was struggling to find out how to do this. Came across your video and figured it out immediately! Thank you
You are welcome!
Yes, this is new to me. I used the fill method most often, I will try this one now.
Thanks, Lorenzo.
Nice presentation and good examples. I am going to use the sequence (count function). Thank you.
thanks, good way to sequence columns!
Thank you 🙏
Yes. This is new to me. I always did the 3rd method, but I thought there had to be a better way. I have an old version of Excel on my home computer, but my work computer has 365 - so I'll use Sequence the next time at work when the situation arises. Thank you.
You should be able to use Excel on the web also for Sequence.
I will use this feature. Thank you again Chris!
Thanks, Irving.
Thank you very much Chris.
You are very welcome
Super Cool!! TY.
Welcome.
I've always used =Row()-Row(HeaderRow) but I will definitely give Sequence a try. Thanks Chris!
That works also Robert. I didn't even think about that, and I have a video on it. :-) My memory is going.
Thank you. This is very helpful.
You're welcome!
Thank you, I love new ideas
You are so welcome!
I also didn’t know about this. Great way to do it.
Thank you.
I am definitely going to use that. Thanks.
Thanks, Johnny.
That's our guy. Thank you.
Thank you.
Well., ur method of explaining is very good, and experience speaks❤
Thank you very much this saved me so much time on a report!
Glad it helped!
Thank you made this task so easy for me a novice user
I salute you bro. Thanks bro. You are the answered to my prayer! :)
My pleasure
Thank you, you made it so easy
Glad to hear that! Thank you.
awesome thanks
You bet!
Just used awesome thank you
No problem 👍
good stuff. thank you
You bet. Thanks, Robert.
Thanks!
Welcome!
Thank you for the tip. I need to hide a row and would like the sequence function to ignore the hidden row? Is there a way to do that?
Hi Chris, great tutorial. Question, can I use sequence function for a row where I have repeated numbers and every nth column I need to increase that value with a specific number? Any suggestions please? Thank you.
thank you
You're welcome
Thanks a lot for this very nice way. I have a question: Can I apply this method for a sequence of say two groups: A & B so that IF I added one case in group A or B it will be numbered according to the sequence for that group?
Especially the last one function is awesome, and it is really work if in any excel sheet there is some blank cells is B column because of some semi data of above cells in C column is coming down. Than B column will be left blank and we need to put numbering with regards to B column. Than it will be pretty good. Awesome❤❤❤
Thank you!
Great info
TY!
What if i Wanted a column that automatically fills the last cell when i add a row, BUT locks the other numbers to the rows. To better explain myself i'd want a spreadsheet with incremental numbers that don't stay put when sorting for date, by assigning the numbers to the product.. sorry english is not my main language
GREAT
happy to help
I have learnt a lot from you today
Thank you!
its really helpful
Thank you!
Thank you! I'm so happy I don't have to make my table a formal table. It's too hard to use. It never lets me re-insert a row that's been cut. Do you know how come when I drag it down, it shows spill?
Good afternoon Chris...thank you for showing this...im working on a new project to where my manager is asking me to use 2023-001 as tracker....would you be kind to show me how I would execute? Thank you
Great. I have a question when you have the sequence function in excel table is there a specific to write the formula
Thank you very much for your effort. But there's a little, yet, inconvenient problem with method 5: when you apply filters, it won't let you change the sequence/order, and an error message pops up alerting you that you can't apply that with ranged array.
Hey Chris, can you use the sequence function and do that same thing for every 5th cell in a column? And if yes, can you also not have it count hidden cells?
Cool brother
Thanks from India❤
Welcome!
thank you! What if I had the following sequence to enter: img 1 of 200, of 200, of 200, img 3 of 200 , of 200, of 200, img 5 of 200 etc... where the EVEN numbers of images are repeated twice but the odd number only appear once?
Hi Chris, Thank you for the wonderful tutoring! I have a question related to this topic. I want the sequence numbers of the left-hand side five digits in a column like A33575-27800 next A33575-27801 next A33575-27802. Can you explain how to do it, please?
Another not-so-great mentioned:
A1 = "1"
A2 = "A1+1", pull down. Breaks down when deleting row.
I have specific lists. Can I add a row within a list and renumber automatically?
Hey Chris, there might be an easier option try using "=row()"
I have one.. is there a shortcut for sequencing two groups of number together.. like 4-20ma in relations to 0-500lbs load cell... So the middle of the scale would be 12ma is equal to 250lbs
Cool. What about if I want to create an "endless" record of data, and I need to keep creating the sequential numbers as soon as I add data?
The function I used will do it.
I'm using office 2019, do you have any alternative method to get the same job done.
ROW function. =ROW() will return the row you working in. For example, if I am on B7 and and in A7 type =ROW() I will get the number 7. To get a 1 I would do =ROW()-6.
Thank you Chris. However it wouldn't work in an official table. What would you use?
Hello, Mark. Unfortunately and for some unknow reason, Dynamic array functions will not work in a Table. You will get a Spill error. The workaround I use is to put the Sequence column outside the table. I'm not sure why dynamic arrays won't work in tables. Hopefully, Microsoft can fix that.
May i ask sir, why do when i did the =SEQUENCE(31 or any number it automatically become date 1-Jan on sequence?
I want to list the number 1 8 times, then a blank cell, then the number 2 8 times and so on. I also want it to stop after listing the number 18 8 times. So let's say I copy the formula to Row 300, I don't want to see numbers greater than 18. Could you help me with a formula/function, please?
Is there a way to contact you directly? I need help creating a sequence with breaks that insert a comment from another cell, but not lose the overall sequence count.
I try to use sequence(counta(b:b)), but the value is NAME?
What if you want to sequence filtered rows?
I do not believe that will work with Filters.
thanks but it doesnt work in excel tables. is there another solution please?
I add a column outside the table. Hopefully Microsoft will fix this soon.
Great feature and would save a lot of time, but doesn’t seem to work in tables. If I convert my table to a range, it works fine, but not in the large tables I use daily.
I'm using WPS, I don't have a sequence function, but, the row function, the row number minus 1 smaller, say 13-12, row()
Hello sir, how to ad that function sequence in excel please ? I don't have it in my function list !
The SEQUENCE function only works with Microsoft 365 accounts and Excel 2021 and Excel for the web.
@@ChrisMenardTraining Many thks sir! will change my office version! thks again !
WHY DONT APPEAR THAT FUNCTION IN MY EXCEL what version are you working?
This won't work in an excel table because of the #spill problem when the formula is automatically copied down
I have a table where i have same types of data in a column and i want all those data in next column in a serial format. for example 123456 123456 123456 in a column and i want 123456 1 2 3 in next column. just because 123456 3 times. if another data 654321 654321 654321 654321 4 times then data in next column 654321 1 2 3 4. Please share any formula available.....
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 to start a sequence inbetween like
456/2023-24
457/2023-24
458/2023-24 and so on" can u help me with this???
Do the sequence for 456, 457, and so on in one column. In another column, have 2023-24 and autofill down. Now, add a helper column and concatenate the first two columns. =A2&/"&B2 drive.google.com/file/d/1ek8AMY6RCtzszGhk7iNkfN8GZYA-NaAO/view?usp=drivesdk
thank u so much...this helps me save lot of time @@ChrisMenardTraining
using in Singapore at therapy department
row-1
I didn't know how to add a column to get started. Lol
=SEQUENCE(COUNTA(B2:B40))
seq and seq with counta don't work if you have hidden rows.
True
i don't understand where you're going when you say "method one"... and then you start typing =seq(counta
Does not work on tables - fyi. Dynamic arrays will not work in tables.
Also, this sequence(groupa) is very memory and processor expensive - DO NOT use it on large datasets - best to use power suite