The SEQUENCE function in Google Sheets is useful to return an array of sequential numbers vertically or horizontally. You can create single or multiple columns sequences as well as define a step value.
Table of Contents
SEQUENCE function does this simply by taking the number of rows, column, starting number, and step value and create an arithmetic sequence.
Arithmetic sequence means that the difference between one term and the next is a constant. In other words, we just add the same value each time. For example, 1, 3, 5, 7, and so on.
So how do we do that?
SEQUENCE function only needs the starting criteria of the sequence, and it fills the whole list (or a multiple columns table) of numbers automatically. In conclusion, you only need to write the function in one cell, and it will provide the whole sequence.
Let’s dive right into real examples to see how to use SEQUENCE function in Google Sheets.
The Anatomy of the SEQUENCE Function
So the syntax (the way we write) the
SEQUENCE function is as follows:
=SEQUENCE (rows, columns, start, step)
Let’s dissect this and understand what each of these terms means:
=the equal sign is just how we start any function in Google Sheets.
SEQUENCEis our function. We will have to add the
rowsvariable(s) into it for it to work.
rowsis the required field that represents the number of rows to return.
columnsis an optional field that represents the number of columns to return. If you omit using it, the returned array will have 1 column.
startis an optional field that represents the number to start the sequence at. If you omit using it, the sequence will start at 1.
stepis an optional field that represents the amount to increase (or decrease) each number in the sequence. By default, it will increase the sequence by 1.
You can create sequences with different structures with the
SEQUENCE function. Above all, it depends on how you use the variables (the required and optional fields of the function).
- If you only give the number of
rowsin your function and omit using the rest of the variables, you get a single vertical list of numbers starting from 1. The formula below creates a sequence of one column with three rows.
- Now let’s see how to create sequences with multiple columns. The first two variables mean that you define the number of
columns. This solution creates a table with four rows and two columns.
- You can give a third variable
startto define which number to start the sequence with. This way, it doesn’t start with the default 1, but with 3.
=SEQUENCE(4, 2, 3)
- The last variable is the
stepvalue. Instead of increasing the numbers by 1 in the sequence, you can set any other number to increase (or decrease) it. With the formula below, the sequence has a difference of 2 between each number.
=SEQUENCE(4, 2, 3, 2)
There are a lot of ways to create sequences with this function. Additionally, there are some special use cases of it, which you can see under the notes below.
⚠️ A Few Notes to Use SEQUENCE Function Even Better
stepvalue can be negative, and you can create sequences with decreasing order.
- You can create single row horizontal sequences when setting the number of
- You can easily create a single column list of numbers without the use of
SEQUENCEfunction. This can be done by entering at least two values next to each other, highlighting them, and then dragging the blue highlighting box down or across. However, there is a difference when you want to insert or delete a row from the sequences. The
SEQUENCEfunction always results in a continuous list, meanwhile, the drag-down solution can be interrupted.
- There is an identical solution to the
SEQUENCEfunction with the combination of
ARRAYFORMULA. The formula
=SEQUENCE(5)gives the same result as
- Dates and times are considered numbers in the background. This means that it’s possible to create sequences of dates and times with the
SEQUENCEfunction. You can give a date as the
startvariable with a simple
=SEQUENCE(4, 2, DATE(2020, 4, 1), 1)
You just need to format the result from numbers to dates. Select the whole area with the sequence and click on Format > Number > Date.
A Real Example of Using SEQUENCE Function
Take a look at the example below to see how
SEQUENCE functions are used in Google Sheets.
Say we are creating a sheet to count how many products are created in a shoe factory in one week. 👟
The working hours are from 8 AM to 8 PM from Monday to Friday. One thousand pairs of shoes are produced every 4 hours.
We want to see how many pairs of shoes should be in the inventory at 8 AM, 12 PM, 4 PM, and 8 PM each day. Thus we want to create a sequence that calculates the exact number of shoes at these moments.
We fill the
SEQUENCE function with the right variables:
- The table goes from Monday to Friday, so the number of
- Then, there are 4
columnswith the indicated times in the top row.
- The next variable is the
start, which is 0 because there are no shoes produced at the beginning of the first day.
- Finally, we need to define the velocity of the production. In other words, how many pairs of shoes are produced every 4 hours? The
stepis 1000 in this example.
To put this all together, the
SEQUENCE function we write in cell B2 is this one:
=SEQUENCE(5, 4, 0, 1000)
After hitting the Enter key, we can see the number of shoes in the inventory throughout the week:
Super easy, right?
You can change the variables in the first cell of the sequence, and it will change the whole sequence. For example, you can change the velocity of production (the
step variable) from 1000 to 1500, and the entire table is recalculated accordingly.
Try it out by yourself. You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:
How to Use SEQUENCE Function in Google Sheets
Let’s see how to use SEQUENCE function in Google Sheets step-by-step.
- To start, simply click on any cell to make it the active cell. For this guide, I will be selecting B2, where I want to show my result.
- Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘
SEQUENCE‘, whichever works).
- Great! Now you should find that the auto-suggest box will pop-up with the name of the function
- After the opening bracket ‘(‘, you have to add the variables. Remember that you can add up to 4 variables, but only the first one (
rows) is required, the rest are optional. In this example, we have five rows (from Monday to Friday) and four columns with the times. Therefore, we create a sequence of 5
columns. So the first two variables are 5 and 4.
- After that, we need to define the
startof our sequence, which is 0. This is the next variable in the function.
- Finally, we have to add the
stepvalue. We want to increase the numbers by 1000 each cell, so we write 1000 as the last variable.
- Hit the Enter key, and you can see the whole table with the sequential numbers!
That’s it, good job! You can now use the
SEQUENCE function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂