What is the SEQUENCE function and 5 optimal ways to use the SEQUENCE function in Google Sheets

The SEQUENCE function is one of the common functions when working on Google Sheets or Office 365, creating a list of numbers in an array, arranging flexible number sequences, making complex formulas or manual operations easier, and saving time working on Google Sheets.

What is the SEQUENCE function?

In addition to the basic functions in Excel that we often use, Microshop 365, Excel 2021, and Google Sheets have added the SEQUENCE function, which allows you to create and arrange pre-existing number sequences or arrays of numbers according to user-specified parameters, in a specific order.

Note: For Excel 2016, 2019 versions, the feature of this function has not been updated yet.

The formula of the SEQUENCE function

Formula

To use the Sequence function, you need to remember the basic formula below:

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

Explanation of parameters:

• Sequence: The name of the function.
• Rows: The number of rows for the number series. This parameter is required.
• Columns: The number of columns in the number series. If not given a value, the formula will default to 1.
• Start: The starting value of the number series. If not given a value, the formula will default to 1.
• Step: The interval between values. If not given a value, the formula will default to 1.

How to use the SEQUENCE function in Google Sheets

Numbering quickly with the SEQUENCE function

Vertical numbering

In the case where you want to number a list with fairly long content, sometimes reaching up to 1000 or 2000 numbers. Manually dragging in Google Sheets is time-consuming and can lead to mistakes. In that case, you can use the SEQUENCE function to quickly number the list.

The use of the SEQUENCE function in this case is quite simple, you just need to replace the sequence number you want to assign to the first Rows parameter in the corresponding formula, do not enter other parameters, the function will default to 1.

Example: The sequence number to be assigned for a list is 1000.

Formula =SEQUENCE(1000)

Horizontal numbering

Similarly, the SEQUENCE function is also used to create numbers in horizontal rows, depending on the change of Columns.

Example: You want to create a table with 15 columns and sequentially number them horizontally.

Formula =SEQUENCE(1;15)

In which:

• The parameter 1 corresponds to the number of rows (Rows).
• The parameter 15 corresponds to the number of columns (Columns).
• The other parameters are defaults to 1, meaning starting from number 1 and incrementing by 1 unit.

Using the SEQUENCE function to create a series of increasing or decreasing numbers

Creating an increasing or decreasing table

Change the Start and Step parameters in the formula to get consecutive increasing or decreasing number sequences.

Example: Use the SEQUENCE function to create a series of even numbers increasing vertically, with the formula =SEQUENCE(10;1;2;2)

In the displayed formula on the screen, the Start and Step parameters have been transformed appropriately to get even number data when starting with the number 2 and increasing by 2 units. Similarly, you can also create a table with odd numbers by starting with 1 and increasing by 2 units.

Descending numbering in Google Sheets

Similar to the above steps, FPT Shop guides you to use the SEQUENCE function to create a sequence of decreasing numbers by changing the Step to -1.

Example: Create a sequence of decreasing numbers in Google Sheets using the SEQUENCE function with the formula =SEQUENCE(10;1;10;-1)

By shifting the Step to -1, you will get a sequence of numbers moving back from 10 and ending at 1. You can apply this formula to ranking tables in ascending order.

The SEQUENCE function combined with other common functions

1. SEQUENCE combined with the ROMAN function to create a Roman numeral table

Innovating in taking natural numbers as sequential numbers, you can combine the ROMAN function into the spreadsheet to create a sequence of Roman numerals.

Example: Numbering Roman numerals from 1 to 10 in Google Sheets.

The formula is as follows: =ARRAYFORMULA(ROMAN(SEQUENCE(10;1;1;1);0))

Explanation of the formula:

• ARRAYFORMULA: often used in Google Sheets, allows applying a row formula to a selected range of data on the sheet.
• ROMAN function: converts a natural number value to a Roman numeral in the form =ROMAN(number, [form]).
• SEQUENCE function: creates a sequence of values including 10 rows and starting from number 1.

2. Using the COUNTA function to automatically number the content

In the case where you enter content in the next column and want the sequence number to be displayed along with the content, you can use the COUNTA function with SEQUENCE.

Example: Enter the command =SEQUENCE(COUNTA(C3:D12)) to both enter text and display sequential numbers.

3. Using the TRANSPOSE function in combination with SEQUENCE

In case you enter the formula in the wrong column or row, you can change the column and row parameters or switch them using the TRANSPOSE function. Then, the number of rows and columns in the SEQUENCE formula will be interchanged.

Example: Switch the Rows and Columns numbers with SEQUENCE and TRANSPOSE.

The formula =TRANSPOSE(SEQUENCE(3;6;1;1))

In this case, the value 6 representing Columns will replace the value 3 representing Rows, as shown in the accompanying illustration.

4. Creating a date series with the DATE function

If you want to create a list of dates in a month for personal work plans or company schedules. You can apply the SEQUENCE function along with the DATE function to get the most accurate and convenient time arrangement on Google Sheets.

The applied formula using the SEQUENCE and DATE functions on Google Sheets is as follows: =SEQUENCE(365,1,(DATE(2023,01,01))

In which:

• The parameter 365 represents the number of rows, corresponding to 365 days in a year.
• The DATE(day;month;year) function displays the value corresponding to the start date of January 1, 2023.

5. Combining SEQUENCE with the TIME function to determine a time series

If you have a schedule that requires describing time, the SEQUENCE function combined with TIME will help you arrange time more conveniently on Google Sheets.

Example: You have a plan to visit 10 locations, each place for about 1 hour.

The formula will be =ARRAYFORMULA(TIME(SEQUENCE(10);0;0))

Q&A and Important Points to Note when using the SEQUENCE function

Can I use the SEQUENCE function for decimal numbers and how?

Answer: In some cases, you need to number decimal places, from 0.1 to 1, for example.

You can apply the following formula: =ARRAYFORMULA(SEQUENCE(10)/10). Dividing by 10 will ensure that the displayed result on Google Sheets will be a decimal number.

How to create a multiplication table with the SEQUENCE function?

Answer: First, you create a sequence of numbers from 1 to 10 using the formula =SEQUENCE(1;10), then lock the row and column from start to finish by entering the command =SEQUENCE($K$1,$B$1,B$1,B$1)

Some necessary notes when using the SEQUENCE function

Note 1: You need to determine the number of elements to be created, how many rows, how many columns to assign the sequence number appropriately.

Note 2: When entering the formula, check if the equal sign, brackets, function name, and commas are formatted correctly, which reduces the chance of an error message appearing.

Note 3: If you want to format the sequence based on decimal numbers, you need to add a division by decimals after the SEQUENCE formula.

Note 4: Currently, older versions of Excel have not updated this feature yet. If you want to use it, you need to download the Microshop 365 software or update to the Excel 2021 version. It’s more convenient to use the SEQUENCE function on Google Sheets.

Summary

So, FPT Shop has introduced you to the concept of the SEQUENCE function and how to use the SEQUENCE function effectively in Google Sheets. Hopefully, the article has partly answered your questions about the SEQUENCE function.

You can also refer to other relevant articles on functions in Excel.

• Sharing 2 ways to sort names in ABC order in Excel in detail, easy to understand, and implement
• How to use Excel: The 5 most basic functions for beginners

• Good price laptops