Do NOT Drag Down to Create Numbered Lists in Excel! Here's Why.

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/numbered-lists-file If you're using a table, you can use =row()-1

[@karenroem3757]

Yet another reason to use Tables 😉

[@kenhaley4]

I just tried that. Unfortunately, the numbers change if you move the table vertically.

[@kenhaley4]

OK, I just fixed that. Use =ROW()-ROW(Table1[#Headers]) (Replace "Table1" with the name of your table.) That anchors the number to the row that the table headers are in, and so the numbers never change if the table is moved.

[@HarpreetSingh1991]

Damn my 2016 version doesn't have it. "The SEQUENCE function is only available with Microsoft 365 subscriptions and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it does not work since those versions do not support dynamic arrays"

[@andidegn]

Only issue is that it doesn't respect sorting. So if you're using it as an index and sort by a different column then the index doesn't follow the row data. Or am I missing something?

[@triplex86]

Was a dragger in the past but I never knew anything better. Now I'm a dynamic. Great video Leila - nice to learn little tips and tricks like this . Thank you.

[@jimmychandra1657]

7

[@jimmychandra1657]

7

[@adrianpalmer3077]

I am a dragger and I will continue to drag in certain circumstances. The most important one is when I want to sort rows by different other columns, but I want to keep the order when I want to come back to the original table. But I will adopt the sequence function for other situations. Thank you, Leila! 🙂

[@RobertFCM]

Very smart

[@Oldnose63]

Same here

[@fuzzylon]

Good point. This is the same reason *why* I usually have a numbered sequence in column A.

[@thinkingoutloud6741]

Exactly. I’m a big fan of tables, and often use numbering to get back to the original sequence.

[@disengronkulifactice]

This is exactly why I drag, but it occurs to me that using SEQUENCE() and then pasting the result as “values only” when you want to lock in the numbering may sometimes be the best of both worlds.

[@junyc85]

I do both, but in most circumstances, I prefer a static rather than a dynamic index. It helps in going back to prior states of ordering/sorting, as well as serve as a permanent index number for look ups. The dynamic option is helpful only for end-state presentation.

[@CarlGillstrom]

agreed. I usually have numbering/sortering in an order for a reason. If 1 switches to 204 or w/e that would distort the result. I personally strongly dislike many of these automate functions because they limit how I can work.

[@ChaplainRMCSJ]

Definitely DYNAMIC! I was always adding the period after the number using the &"." but now I have learned that I can customize the number format to include this also. Thanks for the pointer on that.

[@EmlynFlint]

Dragger/autofiller. And for large matrices that don't have existing columns, I use the FIll --> Series option. The problem with the SEQUENCE function, or any *dynamic* formula-based numbering system, is that the COUNTA with entire row (B:B) as input is a lot of unnecessary computation overhead, especially when you have multiple output tables across sheets. It doesn't matter for small books or those without much calculation, but this is exactly the use-case where dragging (or Fill -> Series) is sufficient. Then, for those cases when you have large tables or a lot of dynamic formulas and UDFs linked to user interactions, streamlining unnecessary formulae becomes imperative.

[@MrSupernova111]

Yep. A lot of these new Excel functions appear to be for casual users. I posted a comment that allows me to do the exact thing that "=sequence()" does with an "=if" statement. I prefer my way if I needed a dynamic count. But in the end I copy over the formulas with values only to eliminate the unnecessary calculations.

[@GeoGuy388]

@@bussinessname7717 They were just answering the question at the end of the video, which is what Leila ASKED the viewer to do. You should stop being so combative and rude for no reason.

[@largpack]

just make sure the "used range" only of the worksheet is not bigger then necessary. Computation is okay then. if you don't know what i mean, then you should try to search it on the web and increase your knowledge

[@snjpverma]

@Bussiness Name stop finding fault in others and stop preaching.

[@mulisaurus]

@Bussiness Name Someone doesn't excel.

[@GpD79]

I'm a dragger for short lists and a double clicker for longer lists. I like to double click on the bottom right corner to get that drag effect, w/o having to drag. It's very helpful when you have a long list that extends beyond what's visible on the screen. The only issue is that it will stop once it gets to an empty cell, which is normally not an issue, but it is when not all cells in a data range are populated. I can see this sequence function being useful in a table as well.

[@hankgrimes1806]

Hi, Leila. I've never been a fan of dragging numbers down a column, so I wrote these formulas to address to inconvenience: First cell =IF([@Date]"","1","") / subsequent cells =IF(D6"",C5+1,""). Even these formulas don't work dynamically, but they were in improvement over dragging. Now that I've seen how you use the SEQUENCE function + custom formatting, I'm squarely in the dynamic column. As always, you've been a lifesaver. THANKS!

[@Xubono]

I was a “dragger” for 25 years. After this video I am going to become a “dynamic”. I use equations everywhere else and thanks to this video, I shall convert asap! Thank you. It looks like I am also subscribing to this channel!

[@ShazEire]

I was a dragger (hold down the ctrl key when dragging the fill handle to increase any number x1) BUT I'm Absolutely gonna go dynamic now! this is great!!

[@sergeralay2873]

I used to be a dragger during years but I moved to the dynamic team a few months ago ;-) Whenever I can, I try to make my formulas dynamic. I have wasted so much time in the past manually updating my lists. Now, I'm trying to take advantage of Excel functionalities. Thank you Leila !

[@richtiff]

Use both depending on circumstances. With a large existing list where I want to use these as an index to the original order of the list (ie sortable), typing 1 then 2 and then double clicking on the small black square instead of dragging automatically fills in all the numbers for each active row of data. Simple but effective. For a more dynamic or increasing list, the dynamic option is great 😀

[@markanthony7078]

Yup agree with you, same here... 🙂 like minds think the same lol

[@kalibrerad]

I agree. Or just type the 1, double click it down and then use "Fill series". Depending on your screen size and swiftness with the cursor i could be faster. Dragging, in my opinion, is only ment for when the data set is visible without scrolling.

[@chrislaf2011]

The double-click of the little black square is a new one for me, and a great tip. Thanks! (And I've been using Excel for 25 years or so!)

[@-theflea-4561]

You can also just use 1 in the cell and control click to add in sequence

[@nullcycle]

Used to drag, but seeing this I am all for dynamic. Great tips!!

[@ldmlhome]

DYNAMIC! You just saved me so many brain cells, you are my HERO!!!

[@sunshynea5151]

I am a dragger and I never had an issue with dragging but that's because I didn't know any other way...definitely becoming dynamic from today. Thanks Leila this tutorial was great!!!

[@sunshynea5151]

I tried it today on my To Do spreadsheet..its going to make my 1-2-1 meetings so much easier. My colleague saw my spreadsheet and she wanted the same for hers so, I shared the formula and your video. Many thanks!!!!

[@darylweaver9342]

I use the fill series button lots, also for calendar functions. However, the dynamic sequence concept is great!

[@AndreZA979]

What a great tip. I usually use a formula for changing numbers, adding 1 in the first cell followed by a =A1+1 in cell A2

[@fireman194]

I have done this too

[@joelajuluchukwu9798]

The problem with this method is when you have to delete

[@deltawing9]

Amazing content, as always. Working on older versions of Excel I'm afraid this function was not available back then. I used to add +1 to the previous row's number and drag down this simple formula.

[@amjan]

Ja tak samo ;)

[@collinsomoregbe2630]

The way I love this woman I can't explain... Please Leila Gharani can organize an excel bootcamp for us from amateur to expert level I don't mind paying. There are tones of that online but no one does it better than you.... You make our life easy.

[@Paul-om1zs]

Thanks for mentioning Bob Umlas. He has interesting approaches

[@LeilaGharani]

No doubt!

[@natejenkins786]

Hi Leila, I love your videos, I've learned many helpful tips, just for the record you viewers should know that if they're dragging to get a sequence of numbers incrementing by one, they only need hold down ctrl while dragging. But I prefer your method with sequence or maybe the row function. Anyway thanks for introducing that sequence function.

[@captainsawdust1]

Thank you so much. I didn't know about the Ctrl option. I used to type 1 down 2 highlight both then drag or double click

[@himodyuti]

I use reference to the previous number + 1. If we remove something from the middle, we have to fix 1 ref error and we are back on track. Please note that in both of these methods, we aren't relying on the number/serial as unique key for lookups/index/match as the numbers will update with the changes in list structure. They have to be hard coded for correct results in lookups.

[@carlwummel9050]

It depends on the purpose. I often introduce lists that are meant to grow. So they should have an id that is not the row number and they should NOT dynamicslly change because the whole purpose of the row id is to have single simple and stable identifier. Adding an indicator like auto format for duplicate ids plus a hinting field with the max value often helps avoiding messing up the ids. In other cases we use timestamps or arbitrary strings (cat walks over keyboard) just to get unique but stable ids. No one of us has ever figured out a nice dynamic and trustworthy way but I bet vba could serve that. Really, if you just need an ordered list sequence is probably the best. If you want to keep track of your rows you shouldnt go too dynamic as you'll lose track. Like that email telling you to do step 302. Well, that was when step 302 was "eat lunch" in the current version of the file 302 is "go for a walk". Hard fail in any work done based on excel lists! So, it depends on the application. No one does sequence or drag for the sequence or the drag but for the purpose it should serve.

[@NWRIBronco6]

I recall trying something like this to de-identify documents but maintain a key of their source. It was a pain! I think the only real solution I had was to use a random function on a large range, then paste values only. Not elegant or dynamic, but quick and functional.

[@alfredtomiamubiaya3330]

I used to be a dragger but after watching this video am going dynamic all the way. Many Thanks Leila

[@LeilaGharani]

Great to hear!

[@platypi_otbs]

I'm a recovering dragger. More often than not I would use ROW() and then fill down the number of cells I wanted. That way I could delete rows without problems. I've only known about these new methods for about a year. But I now look for a way to use sequence, let, etc. as much as possible. I have saved so much effort by minimizing the number of times I need VBA.

[@JasonMilner]

It depends on the list. Drag is quicker and simpler for lists that are (A) no taller than the height of a screen and (B) unlikely to have items removed/inserted, and also simpler to remember. That said, I do have a large task list spreadsheet that is regularly updated - this will be perfect for that, so many thanks for the tip!

[@robertwatford7425]

I'm a dragger but I use it for more than numbering lists: Sequential dates, for example. Patterns are detected so that if you have 110.75 and under it 120.75 and select both to copy it will add 130.75, 140.75 etc.This works with dates as well so you can increment just by month or year if you like. Also if you select a single cell with a single number such as '5' and then copy down with the CTRL key pressed it will increment from that number, you don't have to use a pattern.

[@rodrigoribeirodeazevedo9238]

You could also use the row() function to be dynamic and still saving resources, since you don't have to select a full column of data

[@benjaminnzioki8815]

Row() or Rows() works if in a ListObject; otherwise, you still have to drag. The 2 functions are also problematic in ListObjects.

[@rodrigoribeirodeazevedo9238]

@@benjaminnzioki8815 An option is to transform the data into a table. This way both row() or sequence() can be used without spending too much resource and it will not require you to drag anything down as the list expands

[@rachk2839]

@@rodrigoribeirodeazevedo9238 Agreed, data in a table with Row() function.

[@johncooper4187]

Depends

[@OrdinarilyBob]

This video just saved me a ton of time by learning at 2:50 that CTRL-1 brings up the cell format menu! OMG, that's amazing to know! Edit to add: I'm a dragger, but I right-click-drag on that little square in the corner of a cell.

[@awishalapierre9950]

Dynamic… I absolute appreciate your channel, it helps me tremendously in my everyday job, which is a Category Manager in the CPG industry. Please continue what you do… your helping folks like me in more ways than you can imagine.

[@joagj1978]

I've always used row() within tables for this. It looks like this is a better way to do it. Simply count the databodyrange of the table.

[@gsternklar]

I use both. Usually, I use =row() and maybe subtract the headings (row() -1). This is dynamic and if you delete, it renumbers the other rows.

[@robjchristopher]

I’ve been a dragger until now - can see myself becoming a sequencer. This was helpful - especially the custom format - will try to remember that! Thanks.

[@illusion-xiii]

I've been a dragger, but only because I wasn't aware of the "=Sequence()" function. As soon as you started giving the three reasons, I was like, "Yeah, that does make it annoying." Especially the need to select 2 cells to get it to increment, or you can hold [Ctrl] while you drag, either way I always do it wrong first and have to undo and correct myself. I think I'll try getting in the habit of using this function. Thanks.

[@morenofranco9235]

BEAUTIFUL! Leila! I wish I had known THIS function, back in the stone-age of Excel. The Time I could have saved! Thank you for this gem.

[@kate_z317]

I was a dragger, but I became a dynamic after your tip. Thank you! 😀

[@webwizard65]

I think it depends on the use. If you plat to create a "fixed" table, that you do not plan to expand in the immediate future, you may find dragging more immediate. Planning a more "liquid" table instead, may be worth an additional work in order to eleminate a concern later on. As old Obi Wan kenoby used to say, "many of the truths we cling to depend greatly on our own point of view": it works for Star Wars, for life and, it seems, for Excel too. PS: another little trick (nothing exciting), derived from the suggestion from Umlas: try ="No. " & SEQUENCE(COUNTA(B:B)-1) & " of " & COUNTA(B:B)-1" ...

[@l00d3r]

You don't really have to drag down. Instead of dragging, after you select the first two cells for 1 and 2, just double-click the bottom right corner of the 2-cell. Excel will then fill all the rows below as if you had dragged it down yourself.

[@popenieafantome9527]

This is what i do for the most. Although it does not always work for me.

[@l00d3r]

@@popenieafantome9527 Adjacent cells (left or right) need to have non-blank values for this to work. If you are on column B, either column A or C need to have values, and then it will fill in B up to the last non-blank value.

[@mikatu]

Exactly. But she knew that. Just pretended to "forget" to make her little video work.

[@MrZuniboy]

this is what i do almost all the time. i work with lists in the 10K-1M range and often need to number them serially. the double click really saves so much time.

[@popenieafantome9527]

@@l00d3r this i think is only part of what it looks for. Sometimes i do have data on either side but only goes down part way. I think it on occasion looks for similar data and only ends up going down to that similar data. Might also just be old excel quirk. I use excel 2007 which i still have on an old computer.

[@SandoshParavoor]

Your presentation and language are amazing Ms. Leila. My way of numbering is 1 in the first cell, and then, =sum(cell value+1) in the following cells. Again, it need to be dragged if I have a long list. Thanks a lot, Leila. Sandosh

[@Rjtucker60]

I was a dragger in the past…this is a game changer! Outstanding!! 👏👍👊

[@fbanjok]

I became a converted dynamic acwhuke ago. Tnx for remembering us how useful dynamic formulas are. You rock Leila

[@casasdepasymas1]

I really appreciate all your clases. Thanks. Greetings from Perú.

[@Sharingthebiblefaith]

Used to be a dragger, but now I'm going for the dynamic wave Thank you Leila Gharani

[@mattleon734]

Ex-dragger, thanks Leila. I’ve also used a row() formula in tables to achieve similar results. Custom format is my good old favourite. 😊

[@michaelnielsen2870]

Wow I haven’t know anything else besides dragging down. This is so much better. Thank you

[@vramarathnam]

Thank You. Learnt this one today. I am a dragger! and from now on should be doing what you taught.

[@Oxydoz1]

This is absolutely awesome! I was dragging down lists forever, until now. Plus, dragging down selection is destroying the formatting and borders.

[@shibanisa]

Dynamic!!!!! After watching this video i changed from dragger to dynamic!

[@LeilaGharani]

👍

[@paulnatta8519]

Great tip! Was a dragger, but dynamic now! Thank you!

[@vidaasamoah2085]

Am new to your channel but I like to drag which makes it easy for. Thanks for this video. I found it at the right time.

[@eneadastavri839]

I am not a dragger, I was a double clicker but now I am definitely in love with dynamic 😂 Thank you very much!

[@mwkloh]

Dragging is annoying... learned something new from you, dynamic sequencing is my go to method now! Thanks!

[@_me_sh]

Love the dynamic version. Thanks a lot, it'll be handy in future.

[@wayne8797]

Ok, never knew this. Thanks for the tip. We learn every day.

[@alandahlstrom7213]

I was a dragger, but I think I will try to remember to be dynamic. I am also now a subscriber.

[@ShooberTimber]

Will continue to drag, but dynamic certainly looks more convenient depending on what needs to be done. Great video!

[@marvinsamuels1237]

I’ve spent so many years dragging, but no more!! Thank you Leila! 👍🏾

[@NeelModi_dotcom]

I've been dragging for literally 30+ years. Great tip, I never knew about SEQUENCE

[@theaveragedad5024]

Dragger, until today. You’ve made me Dynamic! Thanks!

[@rodwestonable]

Former dragger, now dynamic - thank to you! Thanks!!

[@syedaltaf2213]

I am a dragger always now I understand the important of dynamic. Thank you

[@Coopersmith7718]

I’m happy to say I was a dragger. Thank you for marking life easier.

[@mikehoyt7592]

I started off when cell A3 got a formula of A2+1, then copying and pasting the formula over and over again. :) Dragging seemed like a breakthrough! Now I'll try sequence. Old dog learning new tricks

[@ninamodi15]

I was a dragger but am going to try this tip out. Thanks for the video.

[@bignerdoncampus4527]

Best new information I have ever learned. I’m going to use this from now on.

[@LeilaGharani]

Glad it was helpful!

[@JamesWilliamsWill]

Was a dragger, now I'm becoming more dynamic. Thanks !

[@thecakeladytm]

Dynamic, but I never knew this before now. I'm love with this easier way! Efficiency is one of my favorite words, lol!

[@Davidm1956]

Exactly what I've been looking for!! Only talking to a colleague about this last week!!

[@LeilaGharani]

Perfect!

[@ExaltedDuck]

I've been learning more array function lately and enjoying it. I used FILTER a few days to pare down a daily metrics column to just this month's work days, then OFFSET to create dynamic named ranges, and finally created a combo chart using those dynamic named ranges as sources and was able to get my monthly progress chart to update itself each month elegantly (was in response to a manager reminding everyone to update charts due to the month turn... made me want to challenge myself to automate it)

[@Flying.Dutchm4n]

And then you took the rest of the day off? And keeping your boss thinking that you worked hard 🤣

[@alexedwards8994]

Hmmm, I was using excel on my work computer which isn’t linked to my phone yet I still got suggested this video on my phone. Never looked up anything excel related on my phone. Your video is great and informative, it just boggles my mind how intensely our phones are monitoring our daily routines.

[@bmo3778]

alternatives to dragging down: - double click (if you already have data in the right columns) - fill series (home>editing>fill) - row(this-offset) function works from office 2013 onwards bonus: you should never drag down if you want to copy same texts. dragging down would sometimes* cause excel to think you want an incremental pattern. just copy+paste, or use ctrl+d. *sometimes= the inconsistency is the real problem.

[@johncart59]

Dragger, because I knew no better. Now I've added this to my skillset. Thanks.

[@TonyDiVincenzo]

I’m a dragger. Till now. I’m definitely going to use your great advice. Thanks 😊

[@kennethdevos8768]

I am/was a dragger. By seeing thuis video I will try to stop dragging and start using thuis function. Thanks for the video!!!

[@irwbcg]

wow, happy i found your videos, so many useful tricks to make our lives more easy thanks you so much!!!

[@louiselane806]

Was a dragged but am always looking for the dynamic answer, thank you for this❤

[@71ITor]

Hi, I am a dragger, BUT with practice I know I can become dynamic. Thank you for the simple explanation.

[@EdKolis]

That looks useful. I always just put 1 in A2 or wherever, then put =A2+1 in A3 and dragged that down. But the ability to dynamically scale the numbers based on how many rows are filled in does look cool!

[@mariosebastiani3214]

I WAS a dragger... using the "=(cell)+1 to avoid problems you showed when deleting rows. But I'll give this funcion a try and see if it suits me. Thanks.

[@ErikaNeyer]

dynamic! Always sooo helpful!

[@jean-francoisgagnon8209]

Other: I usually use a previous row +1 formula and copy/paste. It works even if some values are empty on other columns next to the index. I usually work with 100k+ rows and it's easier to just select the target range and paste than having to find and remember how many rows I have I almost never need or want a dynamic sequence. The main reasons I usually add a sequence of number are: 1:to be able to get the original order if i sort. 2:to create an index for each rows which allows me to copy a subset of the datas and easily link back to the original source.

[@vincentferrari]

For one off one shot sheets that won’t change dragging is probably fine but the sequence method is good if a lot of people are going to be working on a sheet or a lot of edits are expected. I love this tip!

[@giantrobottoys]

I prefer dynamic and this is really helpful. Thank you!

[@mevans3291]

Been a dragger for years but after this video I'm converted

[@renees766]

This is EXACTLY what I needed!!!

[@patrickcardon1643]

All Excel roads lead to the solution ... love this

[@jimfogz]

This is great shortcut, thanks for sharing!

[@nikeshbaranwal]

You are brilliant. I have learned good things from you.

[@patrickbuick5459]

For what I do, dragging works fine. I will keep this in mind if I do something that needs it.

[@sheripruett6370]

What a GREAT tip!!!! I will never drag again in sheets that I need to keep track of the “number of”… thank you!

[@TommasoMauroTautonico]

Sempre spunti di studio, grazie

[@abuena73]

You’ve got me on dragger or dynamic. I burst into so much laugh! Haha, nice one Leila! Keep it up!

[@lazygardens]

Ex-dragger where lists will be dynamic ... I didn't know there was a better way. Thank you!

[@VickiBowers]

Oh my goodness, this is incredibly helpful! Thank you!

[@markparado1818]

Im a Dragger and a newbie and excel this teach me a lot. tnx Ms. Leila Greetings from hte philippines :) New Subscriber Here :)

[@LeilaGharani]

Welcome aboard!

[@84Actionjack]

Dragger for years, now reformed. Thanks!

[@LuisMogollón-s3j]

Exactly what I was looking for this is amazing.

[@urswolfensberger934]

Wow what a useful trick! Many thanks.

[@ghanimaabderrazzak1022]

I just love what you are presenting as courses. Thank you so much for such generosity 💯👍👍👍

[@gp917]

I like your explanation. To the point directly. Not like other You Tubers. In job I don't have to use Excel much (we use software applications, tools). I would like to learn it for reporting.