Excel Fill Number the right way using the SEQUENCE function

[@HAVEY723]

Thanks!

[@ChrisMenardTraining]

Welcome!

[@salvatoreettoreserpe5541]

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

[@ingridv104]

I love this video. simple an easy to do. Can you tell me how to arrange alphabetical order with data please? would that miss-up the numbers?

[@ChrisMenardTraining]

Numbers from SEQUENCE will stay the same if you Sort the data. Doesn't matter what field you sort by.

[@tusharmukherjee3598]

I'm using office 2019, do you have any alternative method to get the same job done.

[@ChrisMenardTraining]

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.

[@safiyam3241]

Oh jeez, Chris! THANK YOU! I have been doing method 4 for so long and when adding/deleting rows, it was taking too much time to fix! Thank you!!!!!!!

[@1000frolly]

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?

[@stefaniesrc4724]

I've always known there is a better way than 1, 2, select, drag but never knew what else to do. This is great!

[@sivasankaran1342]

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???

[@ChrisMenardTraining]

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

[@sivasankaran1342]

thank u so much...this helps me save lot of time @@ChrisMenardTraining

[@stanTrX]

thanks but it doesnt work in excel tables. is there another solution please?

[@ChrisMenardTraining]

I add a column outside the table. Hopefully Microsoft will fix this soon.

[@rickchunko710]

I like the sequence funcation and have already added it into some of my spreadsheets. Thanks!

[@ChrisMenardTraining]

You're welcome!

[@AllenBennett-vp6gd]

I went through a couple of videos on using SEQUENCE along with CHOOSE to create a calendar, but I couldn't really understand them. Your explanations are always understadable, so I hope that you can teach me how to do this.

[@ingridv104]

I tried =SQUENCE (B2:B40 enter and it doesn't work?

[@ChrisMenardTraining]

what excel version are you using?

[@ingridv104]

@@ChrisMenardTraining Microsoft 365

[@V-cf9dh]

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?

[@Robert_512]

I've always used =Row()-Row(HeaderRow) but I will definitely give Sequence a try. Thanks Chris!

[@ChrisMenardTraining]

That works also Robert. I didn't even think about that, and I have a video on it. :-) My memory is going.

[@ramykhaleel3515]

Thanks I needed this. Also you are a great teacher

[@goutomkumargour2589]

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.....

[@sophiapradal1054]

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?

[@markk364]

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

[@jojovcpb3736]

What if you want to sequence filtered rows?

[@ChrisMenardTraining]

I do not believe that will work with Filters.

[@elbapino6531]

Hello sir, how to ad that function sequence in excel please ? I don't have it in my function list !

[@ChrisMenardTraining]

The SEQUENCE function only works with Microsoft 365 accounts and Excel 2021 and Excel for the web.

[@elbapino6531]

@@ChrisMenardTraining Many thks sir! will change my office version! thks again !

[@markk364]

Another not-so-great mentioned: A1 = "1" A2 = "A1+1", pull down. Breaks down when deleting row.

[@benjaminarron3782]

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

[@judyceljavier]

May i ask sir, why do when i did the =SEQUENCE(31 or any number it automatically become date 1-Jan on sequence?

[@Adriaan.J.L]

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?

[@5160173]

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.

[@MYKOLABOYKO-c6v]

Thank you so much! I needed some function, that counts correct even if I delete a row. So the last method is the best for me. Just one question: is it works with other velues, or only with numbers only? Because in my worksheet in excel I do have another values.

[@monirbahgat]

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?

[@chhidalgo7165]

WHY DONT APPEAR THAT FUNCTION IN MY EXCEL what version are you working?

[@buchimaximus3188]

I've always thought there has to be a better way to fill in numbers. Awesome. Thanks

[@ChrisMenardTraining]

You bet!

[@pipo441]

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.

[@coco99ca]

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?

[@hichamhadj9640]

This won't work in an excel table because of the #spill problem when the formula is automatically copied down

[@terrysmith3831]

Thanks for showing different ways to add numbers. Thanks

[@ChrisMenardTraining]

Happy to help

[@roallanwood1137]

I have specific lists. Can I add a row within a list and renumber automatically?

[@jeffmiller8183]

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?

[@chrispro79bloom46]

I try to use sequence(counta(b:b)), but the value is NAME?

[@PeterSorensen01]

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.

[@terrysmith3831]

I didn't know how to add a column to get started. Lol

[@mytrueserenity5666]

Perfect! I was looking for just this type of functionality. I'll be using this from now on. Thanks, Chris!

[@sergiorivera2982]

Hey Chris, there might be an easier option try using "=row()"

[@Hercasl]

The last one worked but when I filter out cells, the sequence does not correct itself?

[@naderfawzy5958]

thanks for this one

[@ChrisMenardTraining]

You're welcome

[@bonganemahlalela3327]

I have learnt a lot from you today

[@ChrisMenardTraining]

Thank you!

[@MichaelBrown-lw9kz]

Your instructions are great because you get right to the point and you keep it simple.

[@markpodesta4605]

Thank you Chris. However it wouldn't work in an official table. What would you use?

[@ChrisMenardTraining]

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.

[@concepcionlaranjo6441]

so much to learn sir. thank you!

[@naajihah3036]

i use the method, put 1, click fill, click series, column and put in the number. thank u so much.

[@ChrisMenardTraining]

I like that method but if you delete a row, the numbers are off.

[@1Enjaz]

Chris. I like your tutorials, they are clear and fun to watch 👍

[@ChrisMenardTraining]

I appreciate that!

[@malikasif9562]

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❤❤❤

[@ChrisMenardTraining]

Thank you!

[@davidkeown5351]

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.

[@ChrisMenardTraining]

Good to know. We use another app most of the time to make sure our captions are correct. Thanks for the positive comment.

[@andreanoguerahenao4844]

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?

[@ChrisMenardTraining]

The function I used will do it.

[@ralphcabeltis628]

I salute you bro. Thanks bro. You are the answered to my prayer! :)

[@ChrisMenardTraining]

My pleasure

[@SantieBouwer]

Fantastic !!! Thank you

[@2000konnie]

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.

[@ChrisMenardTraining]

You should be able to use Excel on the web also for Sequence.

[@nathaliehernandez6842]

Was struggling to find out how to do this. Came across your video and figured it out immediately! Thank you

[@ChrisMenardTraining]

You are welcome!

[@Limrenmuh]

using in Singapore at therapy department

[@kimptonndimande1760]

I want my series of numbers to be like 1,1,2,2,3,3 and so on. How would I do that.?

[@ChrisMenardTraining]

Sequence function will not handle that, but this will work. Click A2 and type 1. Click A3 and type 1. Click A4 and type =A2+1. Autofill down. Screenshot drive.google.com/file/d/156HrGYwQGiFW6AjbPgAJZXN2IGemhwaa/view?usp=drivesdk

[@abflenn]

=SEQUENCE(COUNTA(B2:B40))

[@shanesalmon3379]

Thank you so much sir! This was very helpful

[@ChrisMenardTraining]

You are welcome!

[@mj1reyes]

Thank you very much this saved me so much time on a report!

[@ChrisMenardTraining]

Glad it helped!

[@xdhruvtara]

Cool brother Thanks from India❤

[@ChrisMenardTraining]

Welcome!

[@BillJohnston-td7li]

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()

[@mce8183]

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

[@irvingdetres6977]

I will use this feature. Thank you again Chris!

[@ChrisMenardTraining]

Thanks, Irving.

[@johnnycunningham7368]

I am definitely going to use that. Thanks.

[@ChrisMenardTraining]

Thanks, Johnny.

[@lorenzoarodriguez6048]

Yes, this is new to me. I used the fill method most often, I will try this one now.

[@ChrisMenardTraining]

Thanks, Lorenzo.

[@kathyhardyjones6757]

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. ❤

[@begonial6151]

I also didn’t know about this. Great way to do it.

[@ChrisMenardTraining]

Thank you.

[@JethrofarmsAgroenterprise]

GREAT

[@ChrisMenardTraining]

happy to help

[@janjua1024]

Well., ur method of explaining is very good, and experience speaks❤

[@RamonaTapia-o7p]

i don't understand where you're going when you say "method one"... and then you start typing =seq(counta

[@papapws]

Nice presentation and good examples. I am going to use the sequence (count function). Thank you.

[@HueNguyen-cf5ok]

Thank you. This is very helpful.

[@ChrisMenardTraining]

You're welcome!

[@dorothya1681]

Thank you, Chris! Very helpful. I knew there was a way to have the sequence automatically update. Enjoyed the video!

[@simontheunissen575]

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.

[@mikedonnelly4988]

This is great,,, I just used it and it works wonderfully! This channel is great!

[@ddp2049]

Great. I have a question when you have the sequence function in excel table is there a specific to write the formula

[@anv.4614]

Thank you very much Chris.

[@ChrisMenardTraining]

You are very welcome

[@jobsafety6992]

I will be using option #5.Thank you Much.

[@tutsecret499]

That's our guy. Thank you.

[@ChrisMenardTraining]

Thank you.

[@oona-rz8cy]

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?

[@BillJohnston-td7li]

Thank you, I love new ideas

[@ChrisMenardTraining]

You are so welcome!

[@rayt61]

thanks, good way to sequence columns!

[@loujeancabillo5870]

great info! very useful! thank you❤

[@osayanmon]

Thank you, you made it so easy

[@ChrisMenardTraining]

Glad to hear that! Thank you.

[@kewhite50]

Thank you made this task so easy for me a novice user

[@mabbarys]

row-1

[@ronnydiaz1114]

I definitely use this function. Thanks.

[@tusharmukherjee3598]

Super Cool!! TY.

[@ChrisMenardTraining]

Welcome.

[@Frankbrace]

Just used awesome thank you

[@ChrisMenardTraining]

No problem 👍

[@darrenalexander7788]

Thank you excellent tutorial

[@robertcoleman7071]

good stuff. thank you

[@ChrisMenardTraining]

You bet. Thanks, Robert.

[@masnahbintiabdulrahmankpm-9049]

its really helpful

[@ChrisMenardTraining]

Thank you!

[@IsItTrueThat]

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.

[@roywilson9580]

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.

[@Idarousss]

awesome thanks

[@ChrisMenardTraining]

You bet!

[@HaiderAli-m1z7m]

Great info

[@ChrisMenardTraining]

TY!

[@jwickert3]

seq and seq with counta don't work if you have hidden rows.

[@ChrisMenardTraining]

True

[@mariamastoor3195]

thank you

[@ChrisMenardTraining]

You're welcome

[@Mhztish]

Thank you 🙏