In this article I will be talking about the SEQUENCE Function that is one of the dynamic array functions. It allows us to create a list of sequential numbers in an array when we want to give a sequence number in a column or we give a starting number and indicate the increase value. We can easily use it when we need to quickly create a data set. You can find articles about other functions on our blog.

 

WHAT DOES IT DO

The SEQUENCE Function -like I mentioned above- is a function similar to the Flash Fill option in the Fill field in Excel. For example, when we want to give a sequence number from 1 to 20, it is going to be enough for us to write =SEQUENCE(20). Or let’s say that you are going to create a 10-row and 5-column numeral data table. When we want these numbers to start from a certain number and increase by the value we indicate and fill the sequence like that, we can use this function. We can do extra special actions with the numeric values this functions returns us.

 

SYNTAX

=SEQUENCE(rows,[columns],[start],[step])

This function has 4 arguments.
The first one of these arguments is required and the other 3 are optional.

Now let’s take a look at what these arguments mean, i.e. what this function wants from us and what we will give it.

 

WORKING CONDITIONS

• Any missing optional arguments will default to 1.

• An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our SEQUENCE formula is range C1:G4.

• The SEQUENCE function will return an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you’re using structured references. For more details, see this article on spilled array behavior.

• Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

 

USE OF THE FUNCTION

If you want, you can try to give a sequence number in a column. We can obtain a sequence of numbers starting from 1 for the specified number of rows and columns. If you wish, you can increase step by step by using the increment value. Let’s take a look at how to use the function.

 

SPLIT THE DATA INTO COLUMNS EASILY

Now let’s have an example of the use in extra special actions…
Let’s assume that there are dozens, hundreds, thousands of row of data in the A column, and you want to split that data into a few columns. Now, we can create the list easily with the numbers it returns with the logic of this function. In the example below, there are 16 rows of data in the A column. Let’s get these written equally in the A and B columns with a row gap.

 

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this article with your friends and help them get informed. 👍🏻