How to Use the SEQUENCE Formula in Excel (Easy)

The SEQUENCE function in Excel allows you to automatically generate a sequence of numbers or dates. You can create a simple sequence or a custom sequence with increments of your choice.

SEQUENCE function offers an easy way to streamline your data and boost your efficiency when working in an Excel spreadsheet 📗

Let’s see a detailed explanation of the SEQUENCE function in the tutorial below where we break down its syntax and see different use cases and practical examples.

Download our sample workbook here to practice along with us 🧐

Table of Contents

Excel SEQUENCE formula

Excel SEQUENCE function allows you to generate arrays of sequential numbers in no time. This is very useful if you want to create a numbered list for some data or want to create grids of different shapes and sizes ⬛

SEQUENCE is a dynamic array function which means that depending on the arguments, it fills in the columns and rows automatically.

Its syntax is explained below:

where,

rows refers to the number of rows you want to fill

column refers to the number of columns to fill – where its default value is 1

start refers to the number where the sequence starts from – with the default value as 1

step refers to an increment in each value to appear in the sequence -its default value is 1 and it can be positive or negative where the subsequent values increase or decrease.

Creating a number sequence in Excel

The simplest use case of the SEQUENCE function is to populate your rows and columns with sequential values. Let’s see how to do that below 🔽

Say, in a sheet I want 7 sequential values in one column.

To do that,

Step 1) Select a cell.

Step 2) Type in the following formula.

Step 3) Press Enter.

The SEQUENCE formula would return a row of values from 1 – 7. This creates a one-dimensional array that holds the values of a column.

To place numbers in a single row, change the arguments (1, n) as:

Similarly, if you want to create a grid, supply both arguments as:

This would return 10 values as 5 x 2 —> (rows x columns) in a two-dimensional array – you only need to add two parameters for that 2️⃣

You can also choose a different start value for your sequence list by providing a value to the start parameter as (5, 3, 20). Your list of sequential numbers then begins at 20.

If you want to customize the increment value of your sequence, you can set that in the step parameter of the function as (5, 3, 1, 2). This will show you a grid of odd numbers as:

Or you could do the same in decreasing order using parameters like (5, 3, 20, -2). This will return odd values starting from 20 in decreasing order.

Excel SEQUENCE formula examples

Let’s look at a couple of examples of the SEQUENCE function in combination with other functions below.

TRANSPOSE Function

SEQUENCE function is often used in combination with the TRANSPOSE function in Excel. It lets you change the order and even the shape of the grid depending on the order of the arguments.

We use SEQUENCE function with TRANSPOSE as:

Note that the rows and columns argument has been switched here – this is to swap the order of the values 🔁

Let’s see its use case below.

Step 1) Select a cell.

Step 2) Type in the following formula.

Step 3) Press Enter.

The function returns a matrix as:

To see the difference between a transposed grid and a normal SEQUENCE-generated grid, see the screenshot below 📷

In the SEQUENCE function, the values appeared in a left-to-right order, i.e., horizontally. After applying TRANSPOSE, the values now appear in a top-to-bottom order, i.e., vertically.

But note that the shape of the grid remains the same. If you want to change the shape of the grid, swap the row and column arguments as:

The grid shows values in the top to bottom order but its shape and size have now changed. The difference won’t be visible if you are dealing with a square matrix 🟨

Date Function

Another popular use case of the SEQUENCE function is with the DATE function. Sometimes you need to generate the dates of months quickly and typing them manually is not an option.

Let’s use it with the SEQUENCE function.

Step 1) Select any cell.

Step 2) Type in the following formula.

Step 3) Press Enter.

It will return the result as:

These are dates but in number format which gives the impression the data makes no sense. To fix it, select all the values, go to the General bar under the Number section and select Short Date 📆

You can customize your date format too by going to custom and typing in your custom format. We changed it to d-m-yy by setting a custom preference.

You can also use the TODAY function in combination as:

Apply the same format as above from the Number section on the Home tab to get the sequence of dates:

Using SORTBY Function to generate random values

We use the RANDARRAY function in combination with the SORTBY and SEQUENCE function to create a random list of numbers. You can modify the formula to give you increasing or decreasing values.

Let’s see how this works below.

Step 1) Select a cell.

Step 2) Type in the following formula.

Step 3) Press Enter.

The formula generates a matrix of 9 x 2, returns an array of 18 values and sorts it according to the 9 elements returned by the RANDARRAY function.

If you were to decrement the values by the same margin, you would simply add a minus sign before the RANDARRAY function as in the formula below 🔽

Pro tip

Note that since RANDBETWEEN is a volatile function, its values will change every time to do something with your worksheet. It’s better to copy and paste the values of the formula using the Paste Values feature of Excel to avoid rewriting a list of sequential numbers.

Using COUNTA Function

If you want to create a list of numbers equal to the number of entries in the adjacent list, the COUNTA function is exactly what you need 😃

It counts the text values in the neighbouring column and returns an array of sequential numbers where the count is the number of rows in the SEQUENCE function. Let’s see how to use it below.

Step 1) Select a cell.

Step 2) Type in the following formula ⌨

Step 3) Press Enter.

This counts all the values in column C and creates the sequence according to the count. You can also CONCATENATE different words with the list. To do that, use the formula below.

Conclusion

In this article, we saw how the SEQUENCE function works, its different use cases and formula examples. SEQUENCE function is extensively used in data analysis and manipulation.

It can be a lifesaver in times when Fill Handle isn’t giving you the desired results for a list of numbers dates or even text strings as it keeps changing ☹

You can learn more about SEQUENCE and other functions in Microsoft Excel using the links below.

How to Fix the #Spill Error in Excel (Step-by-Step)

How to Randomize a List in Excel: Step-By-Step Guide (2024)

Today’s Date in Excel: Function and Formula Examples (2024)

We hope you enjoyed reading this article as much as we did creating it.