Take a look at a few examples of the new SEQUENCE function in Excel. Generate a long sequence of numbers quickly. Generate 52 trailing weeks. Generate a forward-looking calendar in Excel with two formulas.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2308.
52 trailing weeks with SEQUENCE.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Today we're going to look at one of the new dynamic array functions called SEQUENCE.
When this one initially came out I said oh this is this is really silly but SEQUENCE can do a whole bunch of things for arguments or in the first one is required which is the number of rows and optionally the number of columns, the starting number and the step number.
If you don't provide these three it is going to be 1 column wide starting at 1, stepping by 1.
So just very easily here SEQUENCE(, let's say 1000) will generate 1000 numbers like that right?
Super easy to do but you can customize this a little bit if we want to go from 5 to 10, jumping by one: =SEQUENCE And see I need 6 rows.
Don't need it to be more than one column so I can skip the number of columns. Start at 5.
I don't need to say it's stepping by one, just close and I get 5 to 10.
Here. Let's do odd and even numbers. =SEQUENCE(.
I'm going to do 10 rows two columns.
And I get the odd and even numbers from one to 20 broken out.
But hey, this one - this was cool I was doing a seminar - one of my last live seminars - in Springfield, Missouri.
Lisa was there and I was going over SEQUENCE and Lisa shouted out from the back and said, Hey, we have a report we have to create 52 trailing weeks.
So going from today backwards in the last 52 weeks and SEQUENCE would be great if you were to have 52 rows and one column. And we do we start? TODAY().
Today is a great function that gives us the current day and then the step is going to be negative 7.
That way we'll go from today's date and then the previous week.
The previous week, the previous week, and so on. Like that right now.
One hassle with this is that sequence is expecting number, so it's not smart enough to format things as dates.
I'll choose all that data, control shift down arrow, and then choose short date or long date or whichever you need.
This reminds me of another one from Excel MVP Roger Govier: Creating a forward-looking calendar so =SEQUENCE(.
We we want 5 rows, 7 columns because there Seven days in a week starting from today and then the step will just automatically be one and again right numbers but wrong format.
Format that as a short date.
Actually, you know, since it's a calendar, we can do this control one and come in here.
Just something like that perfect and then to get the days of the week will say equal text.
This is pretty cool. I'm gonna choose Seven numbers here.
Text is expecting just one number there.
But now thanks to dynamic arrays, TEXT can accept all Seven and the format will do DDD to spell out the day abbreviation, something like that right. Great, great new function called SEQUENCE.
You know it's not as sexy as SORT and FILTER and UNIQUE, but there's a lot of very cool things that we can do with SEQUENCE. Now the Excel Dynamic Arrays book.
I know a lot of you have those first edition, I gave the first edition for free. I've updated it to a second edition. Just $3.
Click the "i" in the top right hand corner.
If you like what you see here on the podcast please SUBSCRIBE and ring that bell so you get notifications when there is a new video.
Feel free to post any questions or comments down in the comment section below.
I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
52 trailing weeks with SEQUENCE.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
Today we're going to look at one of the new dynamic array functions called SEQUENCE.
When this one initially came out I said oh this is this is really silly but SEQUENCE can do a whole bunch of things for arguments or in the first one is required which is the number of rows and optionally the number of columns, the starting number and the step number.
If you don't provide these three it is going to be 1 column wide starting at 1, stepping by 1.
So just very easily here SEQUENCE(, let's say 1000) will generate 1000 numbers like that right?
Super easy to do but you can customize this a little bit if we want to go from 5 to 10, jumping by one: =SEQUENCE And see I need 6 rows.
Don't need it to be more than one column so I can skip the number of columns. Start at 5.
I don't need to say it's stepping by one, just close and I get 5 to 10.
Here. Let's do odd and even numbers. =SEQUENCE(.
I'm going to do 10 rows two columns.
And I get the odd and even numbers from one to 20 broken out.
But hey, this one - this was cool I was doing a seminar - one of my last live seminars - in Springfield, Missouri.
Lisa was there and I was going over SEQUENCE and Lisa shouted out from the back and said, Hey, we have a report we have to create 52 trailing weeks.
So going from today backwards in the last 52 weeks and SEQUENCE would be great if you were to have 52 rows and one column. And we do we start? TODAY().
Today is a great function that gives us the current day and then the step is going to be negative 7.
That way we'll go from today's date and then the previous week.
The previous week, the previous week, and so on. Like that right now.
One hassle with this is that sequence is expecting number, so it's not smart enough to format things as dates.
I'll choose all that data, control shift down arrow, and then choose short date or long date or whichever you need.
This reminds me of another one from Excel MVP Roger Govier: Creating a forward-looking calendar so =SEQUENCE(.
We we want 5 rows, 7 columns because there Seven days in a week starting from today and then the step will just automatically be one and again right numbers but wrong format.
Format that as a short date.
Actually, you know, since it's a calendar, we can do this control one and come in here.
Just something like that perfect and then to get the days of the week will say equal text.
This is pretty cool. I'm gonna choose Seven numbers here.
Text is expecting just one number there.
But now thanks to dynamic arrays, TEXT can accept all Seven and the format will do DDD to spell out the day abbreviation, something like that right. Great, great new function called SEQUENCE.
You know it's not as sexy as SORT and FILTER and UNIQUE, but there's a lot of very cool things that we can do with SEQUENCE. Now the Excel Dynamic Arrays book.
I know a lot of you have those first edition, I gave the first edition for free. I've updated it to a second edition. Just $3.
Click the "i" in the top right hand corner.
If you like what you see here on the podcast please SUBSCRIBE and ring that bell so you get notifications when there is a new video.
Feel free to post any questions or comments down in the comment section below.
I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
