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?

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

[@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

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

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

[@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!

[@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!

[@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!!!!

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

[@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

[@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 ;)

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

[@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!

[@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

[@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

[@darylweaver9342]

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

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

[@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!!

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

[@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

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

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

[@Paul-om1zs]

Thanks for mentioning Bob Umlas. He has interesting approaches

[@LeilaGharani]

No doubt!

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

[@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" ...

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

[@ldmlhome]

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

[@kate_z317]

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

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

[@Rjtucker60]

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

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

[@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. 😊

[@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 🤣

[@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

[@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!

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

[@marvinsamuels1237]

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

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

[@Oxydoz1]

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

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

[@eneadastavri839]

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

[@ghanimaabderrazzak1022]

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

[@ebenezerlimitless1907]

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

[@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!

[@ShooberTimber]

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

[@mevans3291]

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

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

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

[@fbanjok]

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

[@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!

[@rodwestonable]

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

[@louiselane806]

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

[@casasdepasymas1]

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

[@SmileyFaceGeorge]

The problem I see with the dynamic method is that it can ruin a scope. I often share workbooks with clients and have specific line items related to a number. Here's an example: I may have already billed for, or completed items 1,2 & 4. Now say that we change the scope to remove item #3 from the list and "delete" that line. The resulting issue is that now the previously completed item which was listed as #4 has suddenly shifted up to #3, along with everything else after it. In this case, a strikethrough would be a better option while keeping the original numerations the same.

[@_me_sh]

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

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

[@mwkloh]

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

[@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

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

[@NeelModi_dotcom]

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

[@Coopersmith7718]

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

[@JamesWilliamsWill]

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

[@syedaltaf2213]

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

[@alandahlstrom7213]

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

[@paulnatta8519]

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

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

[@bignerdoncampus4527]

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

[@LeilaGharani]

Glad it was helpful!

[@TonyDiVincenzo]

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

[@shibanisa]

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

[@LeilaGharani]

👍

[@theaveragedad5024]

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

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

[@ttgy_mlbb3328]

Was a dragger but, thanks to you, I will now be doing the sequence feom now on. 💕💕💕

[@wayne8797]

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

[@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!!!

[@ninamodi15]

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

[@firmaevents9594]

Hello Leila, is there a way to start a number at another than 1. like 1001 and can it automatically increase by increment 1

[@mth32871]

I've been a dragger until yesterday when I somehow discovered SEQUENCE, so I decided to play around with it at home a spreadsheet that I was experimenting different with some of the newer functions. That combined with WRAPROWS and WRAPCOLS suddenly made what I was playing around with SO much nicer. So now I have to see if I will incorporate that into my daily Excel work. BTW, when I would drag to create a numbered list, I actually used a function (I would just add 1 to the previous row, for example), so if I delete a row, the list would automatically update. I'm excited to see if I will be able to make more robust spreadsheets using SEQUENCE.

[@LeilaGharani]

Great to hear you got inspired to take advantage of the new functions! Keep learning :)

[@thecakeladytm]

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

[@johncart59]

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

[@gerbherb8215]

Of course I would be a dynamic if possible, but it has a few disadvantages over dragging: 1-Can't make it a table 2-Can't sort on column B 3-Cant use it to sort back to original order (a common reason to number a list) (Workaround for problem 1 and 2: insert an empty column between the numbering column and the rest of the table) And a tip for draggers: type only the number 1 and CTRL + drag to get your series without having to type number 2.

[@71ITor]

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

[@patrickcardon1643]

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

[@Davidm1956]

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

[@LeilaGharani]

Perfect!

[@eessppeenn001]

If I just make a quick list for a short one tome use just to do something experimental and don't really expect to changing much later, I'll just start at a number 0, 1, or 57, then write the number one higher than the previous, in the neighbouring cell before dragging. Like in the example at the start of the video, just not always starting at 1. If I'm making a longer list that I expect to work on a bit, Itend to write down my starting number in a cell. Then in the cell next to it write =[previous cell]+1, and then drag that cell. (like if I Start at cell A3, with value 1, then I type =A3+1 in cell A4, then drag it down. Also when I use this method for the longer list, I probably have some formula in row B, C, D... or something that refers to the values in A. And drag both of the colums down at the same time.

[@84Actionjack]

Dragger for years, now reformed. Thanks!

[@apedanticpeasant1447]

You absolutely wonderful human being. I didn’t even know that I didn’t know this. Now I know. ❤

[@renees766]

This is EXACTLY what I needed!!!

[@s1283]

Spent a year in africa designing a program to better build position schedules for security and this would have saved me so much time!

[@VickiBowers]

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

[@ronaldjager729]

I was a drager now I am a dynamic. Thanks.

[@patrickbuick5459]

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

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

[@martypfeiffer1529]

I'm trying to get myself to use tables for these types of things. Using Excel Tables for database-type stuff works great!

[@abuena73]

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

[@irwbcg]

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

[@ugohsteven3342]

Used to be a dragger but with your tutorial I am fully dynamic.