Automatically number rows using Sequence and Counta in Excel!

[@hristobarbolov5953]

Never expected Eric Cartman to be on first place

[@Kafj302]

I mean what if he is just wearing a fat suit, and this was a long term study. So he sheds the fat suit and takes off at speed

[@PianoKwanMan]

​@@Kafj302he'd be ripped from carrying all that extra weight, and the sweat

[@buithach4084]

how do you think he stays the same age after 30 years? That's right, guy's been vibrating the particles in his body since the 90s

[@coastmalone]

Let me extend my gratitude for giving so so many great tutorials!

[@tazguy371]

You can also use the COUNTA() function on the entire column. Just subtract one or two for the headings.

[@QwDragon]

Isn't excel supporting ranges like C3:C ?

[@lubekdotnet]

​​@@QwDragon dont think so edit: it is dumb tho

[@blaisemomin1106]

Can just Ctrl + Shift + ⬇️ on C3?

[@christophersonawane]

this man Excels at this.

[@TriangularChica]

*budum tss* 🥁

[@V10Hotsauce]

*budum tss* 🥁

[@mic2968]

Top ten, maybe top 5 useful channels. By far. And I'm not even making spreadsheets yet. Thank YOU For sharing!!! You're a saint and a scholar!!!!

[@neatrizareco4807]

You have no IDEA HOW MUCH I LOOOOOOOOOOOVE YOU🎉🎉🎉🎉🎉🎉. Thank you so much for your time

[@michaeldeangelo4374]

These clips have been incredibly helpful to me.... Thanks!

[@abbesatty9498]

I used to combine arrayformula with row() and counta() formulas to achieve this lol. This is much elegant and far more readable. Thanks

[@purpsonus]

You can always make it so it makes a number when you populate the b column. With the counta and then conctatonate # and a number

[@EasymasterGuru]

Also Insert your columns as a table and in the cell of the first row type =ROW()-X (X being the row number you will be starting from). Tab each row from then on.

[@rachimvdr]

tysm!!

[@serenade8905]

Thanks man I just needed this

[@jvm0wlp35]

Really appreciate this❤

[@ghettoghost2657]

Fantastic information

[@Kal_sengupta]

Thank you so darn much

[@kennyljs]

Eric Cartman beat the Flash???? WTF???

[@sameermpt1]

Man, I should have met you before. ❤ I was using =max(range)+1 so far.

[@lizardmilk]

Great runners.

[@ANunes06]

Or you could use =Row()-2 and autofill by double clicking on the little box at the bottom right of that cell. Or you could use VBA to pull them into an array and really go crazy with Index/Match. Or you could ask why entering every runner in the order they finished was *fine* but entering their indices is a bridge too far. Sequence() is great. But Sequence(CountA()) reads like the scribblings of a madman to me XD

[@Matt-lc8qt]

Not to mention that sequence won't work in a table due to spill and you still have to manually expand the range for counta if you do it the way he did it

[@reinhardcontessa644]

thank you

[@xnetpc]

Your solution seems like more trouble than how I was shown how to do it. Go to whatever cell you want the first number to appear in, for example A1. Now go to the cell below it and type =A1+1 then copy the formula to the clipboard. Select however many cells you want to have a number in and paste the formula into every cell that you’ve selected. You will end up with row after row of ascending numbers.

[@rehawi2001]

You ended by dragging down the arrow 😂

[@adamlako]

Exactly 😂

[@attila_the_hunk9685]

Great channel. Thanks mate. . 1 question - how can I/you adapt the formula to create a row space between each item, and have the list still update automatically..?

[@tazguy371]

I would use two columns - the source list, and a column for the formula to show every item twice. Then hide the duplicates using conditional formatting.

[@whyareureadingthis6719]

But then you would need to update the range of your selection if you go over, instead you can just select all of column C and subtract 2

[@mulisaurus]

Nice. I always just referenced Rows() minus whatever

[@pixelstriko1642]

The more I see these Excel shorts the more I realize they're just databases but user friendly.

[@shrutisingla6551]

Very nice

[@lavelclaytorsProjectswPurpose]

Wow, this is awesome! Can you couple this with concatenate? For example, if I wanted to allow it to Number, but also pull over the person’s first initial and last name.

[@F_A_R_man]

If you have earlier version of Excel and can't use the SEQUENCE function, then you can use ROW func. instead. It's dynamic too. ="# "&ROW() ---- if you are on a top row ="# "&ROW() - 1 or 2 or more ---- depends how many row you are lower than the top one.

[@ANunes06]

That's better for this anyway. Sequence is nice for cross-sheet referencing of data sets, for sure. But there is no added value in this example.

[@Boom080]

Never knew popeyes was a runner 🤔

[@ilhaampv5033]

Could you do a tutorial how to clear up the spaces when some of the data is copied from another source and it is pasted on Excel?

[@sonpham5639]

❤

[@Jupiterninja95]

You can also double click the corner of the cell instead of dragging down 1000+ rows

[@zeepack]

Agreed! Better!

[@NestorCirhuza]

Hi @easy_sheets, What is the equivalent of this formula when data are not in range but table.

[@Gukumatz07]

Would it work if you insert a row after you have created the formula?

[@gardenia8213]

So will that stop until the last cell you highlighted?

[@AndriesduPlessis]

What about counting if column b is not blank?

[@maamardli]

Thank you very much! But it did not work in my table. Is there another way for numbering a column in a table ?

[@ANunes06]

Count the number of rows you have before your list of data starts. In this example it is 2 rows. In the desired column of the first row of your actual data, type ="# " & row() - X where X is that number of rows you counted. So here that would be ="# " & row() - 2 Then press enter, and the up arrow on your keyboard to return to that cell. Then double click on the little box that appears at the bottom right corner of the cell you typed that in. Have a snack.

[@Matt-lc8qt]

​@@ANunes06 Even better do something like= "#"&row()-(row()-1) and you can move the table anywhere you want and the range will change dynamically

[@mja1869]

Does this work when you paste this table on a PPT slide?

[@713theresa]

What if you had Eric Cartman for the first three rows, but wanted him each time to #1. Say, if you were counting to create a unique ID. Would SEQUENCE work?

[@KronyNo666]

nice

[@leftysheppey]

How would you edit this to do it with dates? Or is that not feasible. I'm currently graphing a weekly average statistic. I don't mind adding the date manually, but if there's a way to automate it, that'd be nice

[@ANunes06]

The command: =Today() will return today's date and =Now() will return the date and time In ye olden times, I would just do the math. Windows stores dates as integers, where 1/1/1900 is 0, and every second counts up from there. Type =DateValue(Today()) into a cell and hit enter. This will return the current date as the integer excel uses to "count" days. Type this number into the first row of the column you want to have these dates. Hit enter. In this next cell, type =[the cell directly above, ie "B1" or whatever] + [the number of days you want to iterate between rows. call it 1 here. Autofill to bottom. Highlight the column, right click and select Format Cells. Using this menu, convert to the date format of your choosing. It's old. But it works.

[@leftysheppey]

@@ANunes06 you're a very smart and good looking person, thank you for the help :)

[@arkk1]

Is there a way to do this with dates?

[@_Giorgio_]

Or just select first cell, hold Shift and double click bottom right

[@sdrawkcabdetfard]

How to do it if you want the same number

[@mariajaba5501]

This formula workout for Google sheet?

[@chrislawson1233]

Is there a reason to leave column A empty, or is it just for aesthetics?

[@rhynazoui7193]

Why nobody talking about the cartman is faster than flash💀💀💀

[@SpiritofAloha11]

Hold up I'm still entering my bar codes manually

[@fabianlim4902]

what about table

[@user-qc6xp3cj2m]

Anyone who wants to get rid of any text before the counting starts can use the following code =IF(COUNTA(//Your column) > 0, "# " & SEQUENCE(COUNTA(//Same column)), "")

[@lykourgosls2910]

What version of office you have?

[@easy_sheets]

365

[@BilalAhmed-on4kd]

Mojo jojo😭

[@user-qj6ty1mt2f]

Howwwww do I do this same thing in Google sheets?!?!?! 😭😭

[@user-fd1tv3gc5g]

why my cell show's" #NAME?"?

[@scrivster]

i could definitely drag faster than this

[@artyommanukyan4296]

This doesn’t work in excel 2016

[@redzebra6688]

Birdperson 😂😂😂

[@Getoutmyway994]

Ph bound

[@Natasha26]

Isn’t Excel a great tool? Of course, mistakes happen and in the financial banking world, this can be catastrophic.

[@voisvmujdzjnkwsulqnmqpthrudxgt]

Skip a row

[@MrAkurvaeletbe]

Excel is so damn unintuitive… how on earth are you supposed to know that…