4 Ways To Create Numbered Lists In Excel - Dynamic And Professional

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, you can learn 4 different techniques for creating a list of numbers in Excel. These include both static and dynamic lists that change when items are added or deleted from the list.
If you’d like to read the accompanying blog post on my website, you can access it here: www.excelcampus.com/tips/numb...
0:00 1. Create a Static List Using Auto-Fill
1:20 2. Create a Dynamic List Using a Formula
4:05 3. Create a Dynamic List Using a Formula in an Excel Table
6:05 4. Create a Dynamic List Using Dynamic Array Formulas
Related Videos:
Dynamic Array Formulas and Spill Ranges in Excel Tables: • Dynamic Array Formulas...
Excel Tables Tutorial: Beginners Guide for Windows & Mac: • Excel Tables Tutorial:...
Prevent Excel from Slowing or Freezing when Deleting Rows: • Prevent Excel from Slo...
#MsExcel #ExcelCampus

[@rosimarhernandez8572]

This was the most amazing Excel tutorial I've seen thus far. Very simple to understant and it provides you with multiple options to create both a static number list and a dynamic one. I was also very grateful when the facilitator went the extra mile and explained how we could add a symbol next to our numbers!

[@wayneedmondson1065]

Great one Jon! Another option is: =ROWS(A$4:A4) and copy down. I use that one often. Thanks for the tips. Thumbs up!!

[@ExcelCampus]

Ah yes, great suggestion Wayne! Thanks! 🙏

[@brucecheatham7667]

I’ve found that this method stumbles when you add a new row at the bottom of a table (as in when you’re in the bottom cell and you tab to add the next row in the table automatically). It works perfectly if you add or delete rows within the table.

[@wayneedmondson1065]

@@brucecheatham7667 True.. for CTRL+T tables, a different method is required. Thanks for the feedback.

[@smartshan4u]

Thanks for posting this amazing videos on "Number List" in Excel.

[@PaulEBrownbill]

Not sure how I missed this video when it came out, but great tips again, thanks Jon.

[@simona7517]

That SEQUENCE function is tremendous. Thanks!

[@ExcelCampus]

Yes, there are a ton of uses for it and it's one of my favorite new functions.

[@radenmasandalusia]

Thank you for the tutorial. Very simple and easy to understand.

[@ExcelCampus]

Thank, for your feedback, @radenmasandalusia! 😀

[@bashirjibrilibrahim6708]

Thanks. You instantly increase my knowledge.

[@ExcelCampus]

That's great to hear. Thanks Bashir! 👍

[@BenDieselBasicAutomotive]

Thanks so much for the learning.

[@Quijanos1]

Great examples and explanation. I greatly appreciate it.

[@abdulrahmanmuslih141]

Thanks

[@smilgu]

Thank you so much for sharing!

[@esthersuh3388]

Amazing tricks for excel!

[@shiffamohammed5818]

Thank you so much for sharing wonderful tips, Jon.

[@ExcelCampus]

Thanks Shiffa! 😊

[@markpodesta4605]

Thank you Jon.

[@edmondosestini3227]

Very nice Jon. Thank you very much. Eddy from Turin Italy

[@ExcelCampus]

Thanks Eddy! 😊

[@shoaibrehman9988]

I hv been this situation many time, its really cool stuff. Like as you said during sorting/ ranking not to use.

[@xaquison]

Great tips! Thanks.

[@juliaborovko5865]

You saved my day! Thank you so much!

[@ExcelCampus]

You're welcome, Julia! :)

[@sohailaftab4209]

Thank you, man.

[@darshanadagha5603]

Thanks for these wonderful tips always👍

[@icalkp]

useful tips thankyou Jon

[@orelshema911]

Always good to know new methods. Thanks!

[@ExcelCampus]

Happy to hear it. Thanks Orel!

[@joydeepchoudhury6974]

Thanks Jon. Very useful tip 👍

[@ExcelCampus]

Thanks Joydeep!

[@ismaelkourouma5558]

Ohh yeah this is great, I am starting to build a timetable and was wondering how to tell to Excel some ideas that I have. I think our brains are connected, this video is build for me 😁😁. Many thanks 🙏

[@ExcelCampus]

Haha, I'm happy to hear it. Thanks Ismael! 👍😁

[@mahmoudtaher8893]

Thanks John for your great videos and effort, Moreover I think subtotal is more dynamic especially in filtering : =subtotal(3,$B2$:B2)

[@rere-mx8vc]

BIG THANKS

[@ExcelCampus]

You're welcome, Rere! :)

[@zzzzzzzzzzzzzzzz9]

Thank you!

[@jiansecr7325]

Thank you so much Dear, I got a very usefull tips. good luck

[@mutlunun]

Cool trick 👍

[@ExcelCampus]

Thanks mutlunun!

[@ravichandrankannappan6552]

Hello Jon, Thanks for explaining those nice tips, Keep it up! Cheers Ravi

[@ExcelCampus]

Thanks Ravi! 👍

[@md.saifulislam2205]

Just awesome 👍.

[@darrylmorgan]

Super Cool And Useful Tips...Thank You Jon :)

[@ExcelCampus]

Thanks Darryl! 🙌

[@cindybarbosagonzalez9691]

Mind blowing 😊

[@NkGred]

Great sir 😎

[@CCAFO]

Great!

[@Artistram3d1]

Succinct and Success. Great video, new sub! Thank you!!

[@rayanjabar1596]

Thank so much it’s useful

[@ExcelCampus]

Glad it helped, Rayan! 😀

[@elisehill992]

This helped me... =ROW worked a treat. Thank the heavens! Thank you very much! Then all i needed to do was copy the row of 7,000 and paste as value only. PROBLEM solved. Thank you!

[@ExcelCampus]

That's good to know, Elise! Glad it helped. 😀

[@Excelambda]

Great video, great tricks!!✌ For fun, reference to "Dynamic Array" spreadsheet: more dynamic (using latest new functions VSTACK/HSTACK) =LET(a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(SEQUENCE(ROWS(a)),a)) -full dynamic, entire array: =LET(t,{"No.","Task","Assigned To"},a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),VSTACK(t,HSTACK(SEQUENCE(ROWS(a)),a)))

[@Excelambda]

If we want to add number format: (f variable) =LET(f,"0.",a,FILTER(tblTaskMgmt,tblTaskMgmt[Assigned To]=C3),HSTACK(TEXT(SEQUENCE(ROWS(a)),f),a)) we can choose "0." or "(0)" or "0-"

[@simona7517]

Whoa, that's some super slick stuff! I hadn't even heard of VTACK/HSTACK and don't have them yet in my Excel but cool.

[@Excelambda]

@@simona7517 You can get them only if you are signed up for Beta channel. (for MS 365 users: Open Excel, go to: File/Account/Office Insider /Change Channel click Beta; Office Updates/Update now)😉✌

[@Excelambda]

@@simona7517 this is the build nr: Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15209.20000) 64-bit

[@simona7517]

@@Excelambda Thanks and understood - I've always held off on Beta items as Excel is mission-critical for me for work, plus I don't want to get ahead of my colleagues from a compatibility perspective. So I just pine for the newest stuff and wait for GA. Cheers.

[@SirChiefUK]

Very nice tutorial. Is there a way to add a sequence number automatically when you insert a new row above? The idea is from the table, the latest entry goes above the last data entered in a row.

[@ksumar]

Cool tips Bro 👍... Let's Row () Thank you 😊.

[@ExcelCampus]

Haha! Thanks Karim! 🙌

[@smartwork4768]

Hello Genius, I never miss your any of the videos and you are absolutely amazing in this. Well, i have one query if you could guide me here, - Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query. - Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder? - as we know after refracing of query it would load fresh data only in model. basically, i want to preserve old data also in my excel where i have load the query. Thanking you in advance.

[@peterjames8046]

Thanks Jon, the sequence spill is great and i will use it. I am using if(A4="","",counta($B$4:B4). this will re sequence your numbers as well.

[@mehranhassani6425]

ould you please link the video, that how did you create the Filter that you used sequence for that? 6:05

[@mattmatt245]

Hi Jon. Did you do a video on how to remove duplicates when column values can be swapped like (1,2) and (2,1) would be a duplicate. Thanks

[@ExcelCampus]

I don't believe we have a video on that topic but we'll add it to our list for future videos. Thanks Matt!

[@mattmatt245]

@@ExcelCampus Thanks, just keep in mind that column values can be numbers, letters or any combination of both.

[@nuchamber]

Hi John. I've spilled a single column via a filter of a list--however I want the spill to populate 3 columns of similar rows... the single column contains a simple word in each row. I'd like the spill to flow into maybe 3 columns of single words. Can you figure it out?

[@besslau3570]

Hi thank you for video. But it appears to be #ref when I use =sequence(rows(D2#)), why is that ?

[@ronaldlamb9766]

Very helpful. I have been chasing a re-numbering challenge for doing hierarchy or Outline numbering like MS Project can do. That is, 1.1, 1.1.1, 1.1.2, 1.1.3 If you want to insert a new row as a new 1.1.2, you need the existing 1.1.2 to become 1.1.3 and the current 1.1.3 to become 1.1.4 etc. This is needed when crafting a hierarchy that will then be imported into something that required hiearchial numbering or a way to understand child / parent relationships. e.g. 1.1.1 row is a child of row 1.1 Any help on how to do that is appreciated. Think of it as an ontology. That is, each row starts with its outline number in column A, then there are multiple columns that have properties for that row's main subject (the main subject likely is in column B) All the other columns have headers, and the content at each row is either from a fixed list or open list

[@jerekson]

Would be interested if you found a solution for this! I am trying to do exactly this for a research and evaluation framework, with outline structure in three levels: 1, 1.1, 1.1.1

[@saichandu5965]

Am trying to get the unique series of number including numeric and alpha ets say I have dataset of duplicate I'ds and IDs with same and in diff projects..want to give an unique series of number and it should not change it should be unique to map other datasets can we do it

[@RizwanAli-wt9dr]

hello Sir, i really appreciate your detailed work. Thank you so much. but i wan to request you please attach that file in the comments. also please share how did you apply data validation in Cell C3 in method 4 - Dynamic List with Dynamic Array Formula.

[@RizwanAli-wt9dr]

ok i got it. i watched it again. how did you create list in Cell C3. Thanks

[@phoneAmobileNotary]

Hi Jon, First, I'd like to say that your tutorial is so good because you are articulate on the subject matter. People inject words like: ah, um etc., which makes for disjointed speech or, what they are trying to show does not work so they lose me with my ADHD attention span! ... I wish I could upload a screenshot so that you could see I typed your formula correctly. I tried the 4th numbered list [=SEQUENCE(rows(b5#)], and it did not work. I tried it at home and work, and I get #REF! All of your other formulas work. What could it be? Thanks, and keep tutoring!

[@ExcelCampus]

Hi Marci, Thank you for the nice feedback. There must be a spill range starting in cell B5. If not, then the formula will return the #REF error. You must also be on a version of Excel that supports dynamic array formulas and has the SEQUENCE function. This will be a modern version of Excel. Excel 2021 or later. I hope that helps get you started in solving this issue. Thanks again and have a nice day! 🙂

[@phoneAmobileNotary]

@@ExcelCampus Thank you so much for responding! I kept at that formula and what I found was that I was not closing the formula at the end with a paren! I think I kept missing it because of the header information in the formula. So simple yet so easily missed!

[@IvanCortinas_ES]

A very interesting tutorial. I am verifying that it does not allow me to customize the format using "." in the spilled-array with the SEQUENCE function.

[@IvanCortinas_ES]

Yeah!!! All's is ok now!!! Thank you Jon!

[@ExcelCampus]

Thanks Ivan! 🙂

[@excellNexcel]

Here is another way: =SEQUENCE(ROWS(A1)+COUNTA(table1)-1 ,1) table1 is the name of the named table. CountA will contain the number of rows in the table. The only problem with this formula is that if you insert a blank row, the table numbers will not update UNLESS you put some values in the empty rows. To overcome this issue, I used the count blank =SEQUENCE(ROWS(A1)+COUNTA(table12)-1 +COUNTBLANK(table12),1)

[@myomyoaung8831]

I can not use #sign in Sequence function with Rows function.Show up Ref error. Help me.

[@atherbeg2177]

# is giving REF error, in sequence formula, Please help

[@sheikabdullah6845]

How to remove the space before and after the contents in excel in large number of line items

[@kevingodsave8893]

try using the trim function

[@ExcelCampus]

Hi Sheik, We have a video on 5 ways to remove blank spaces. Here is the link. kzbin.info/www/bejne/sH-vi5anhMqAmdk I hope that helps. Thanks again and have a nice weekend! 🙂

[@gvinodnair]

Great useful tips Thanks Jon ✌🏻

[@ExcelCampus]

Thanks Vinod!