Repeat a Sequence of Numbers in Excel with the SEQUENCE function

Excel doesn’t always have to be about serious business. In fact, spreadsheets are a source of endless hours of FUN! This is exactly what this tutorial series is about, fun. With every article, I will attempt to put the ‘fun’ back into functions for you. 😊

I will demonstrate how to repeat numbers in ascending order, generate the Fibonacci sequence, generate a sequence of prime numbers, and in this article, repeat a sequence of numbers multiple times. All this will be done without VBA. We will use the SEQUENCE function mainly and with a little help from other useful built-in functions. If you require to understand Excel’s SEQUENCE function in more detail, please read the bonus section at the end of this tutorial.

This tutorial is about harnessing the power of the SEQUENCE function in Excel to demonstrate how to repeat a sequence of numbers multiple times. More specifically, we will be able to repeat a sequence of numbers multiple times such as repeating the sequence 1, 2, 3, multiple time i.e.: 1, 2, 3, 1, 2, 3, 1, 2, 3, …, 1, 2, 3, etc.

If you would rather prefer to skip all the explanations and jump straight to the formula, then please go directly to the summary! 🚀

Table of Contents

· Part I — Repeat a Sequence of Numbers
· Part II — Automating the Repetition of the Sequence
· Part III— Further Improvements
· Part IV — Apply Repeating Sequence to a Business Case
· Summary of the main idea
· Summary of further improvements
· Bonus I — Getting familiar with the SEQUENCE function
· Bonus II — Replicate the Sequence Repeating functionality in VBA
· Downloads

Part I — Repeat a Sequence of Numbers

Let’s say that we would like to generate the sequence of numbers 1 to 4 and repeat it 6 times i.e., 1, 2, 3, 4, 1, 2, 3, 4, …, 1, 2, 3, 4. We need to think about what inputs we require:

• “Number up to” is the maximum number to generate repeatable sequence, assuming starting at 1.
• “Number of Repetitions of the Sequence” is the number of times that we would like the sequence to be replicated.

Firstly, we need to work out the total sequence length with =B1 * B2 in cell B3, to generate our starting sequence. Using this total length, we generate the basic sequence of numbers to work off.

Next, if we take the remainder of each element with respect to the repeating sequence’s length i.e., MOD 4, then we get the following:

Notice that this is not quite the repeating sequence of 1, 2, 3, 4, and we rather get 1, 2, 3, 0 repeating. Thus, we need to correct the starting number for repeating sequence first. We set the starting point at 0 in the third argument of the SEQUENCE function, which should give us the repeating sequence 0, 1, 2, 3:

We are almost there. All that we need to do now is to add the starting number of 1 and voila!

Note that adding the 1 to our sequence actually gets “broadcast” to the entire dynamic array. In plain English, the 1 gets added to each element of the sequence.

In summary, running the following formula =MOD(SEQUENCE(4 * 6, , 0), 4) + 1 will return the sequence 1, 2, 3, 4 repeated 6 times. Therefore, the general idea is as follows:

=MOD(SEQUENCE(max_num * num_reps, , 0), max_num) + 1

Where the inputs are:

• The number max_num to generate the repeating sequence up to, and
• The number of repeats num_reps.

These inputs can be integers or cell references.

Part II — Automating the Repetition of the Sequence

We can automate the above more elegantly using the LAMBDA formula which simply follows the pattern =LAMBDA(x, formula(x))(input).

To automate the repeat function requires the names of the input variables and the formula from the previous section. The lambda function looks as follows typed in cell C5:

=LAMBDA(max_num,num_reps, MOD(SEQUENCE(max_num * num_reps, , 0), 
max_num) + 1)(B1, B2)

Note that the actual inputs B1 and B2 to the Lamba function must follow the function in round brackets. These inputs can also be simply integers.

Instead of writing this long formula out every time we should store it in the Name Manager in the Formulas ribbon. NB: Copy and past the formula =LAMBDA(…) only into the “Refers to” box, not the input arguments (B1, B2)!

Thus we can easily replicate the results above in cell D5:

Part III— Further Improvements

We can make the formula a bit more flexible by introducing an optional “Starting Number” which we will default to 1 if not provided. Then the “Numbers up to” can be more appropriately renamed to “Ending Number”. We rewrite the above basic formula as follows in cell B7 to allow for a starting number:

=MOD(SEQUENCE(B5, , 0), B3) + IF(OR(ISBLANK(B1), TRIM(B1) = ""), 1, B1)

We specify the starting number in cell B1. If it is omitted, the start will default to 1. This is taken care off by validation check in the IF statement.

We also require an additional calculation in cell B3 i.e., the “Repeating Sequence Length” which is simply calculated as =B2 - IF(OR(ISBLANK(B1), TRIM(B1) = ""), 1, B1) + 1. The calculated length is then multiplied by the number of repetitions to obtain the “Total Sequence Length” in cell B5.

Next we improve on the previous Lambda function, by letting variables mod_start and length to be stored temporarily with the LET function, and we obtain the following convoluted formula:

=LAMBDA(start_num,end_num,num_reps, LET(mod_start, IF(OR(ISBLANK(start_num), 
TRIM(start_num) = ""), 1, start_num), length, end_num - mod_start + 1, 
MOD(SEQUENCE(length * num_reps, , 0), length) + mod_start))(B1, B2, B4)

The modified start number mod_start and calculated sequence length length are then reused in the actual sequence repetition formula. For example, see the formula in cell C7:

However, it is easier to save the formula in the Name Manager in the Formulas Ribbon. NB: Remember to leave out the input arguments after the =LAMBDA() function in the “Refers to” box:

Next, we can use the second function REPEAT_SEQUENCE2 to generate the exact same sequence in cell D7:

Part IV — Apply Repeating Sequence to a Business Case

Of course, repeating number sequences has real world application as well. For example, if we need to build a report which requires a sequence of client names to be repeated, then we can use our custom function. We can use the CHOOSE function in combination with the saved custom REPEAT_SEQUENCE function to repeat the sequence of client names as follows:

=CHOOSE(REPEAT_SEQUENCE(5, 3), "Client A", "Client B", "Client C", "Client D", "Client E")

Alternatively, we can list the sequence in a range, then use the INDEX function with the saved REPEAT_SEQUENCE function to specify the row indices:

=INDEX($D$1:$D$5, REPEAT_SEQUENCE(COUNTA($D$1:$D$5), 3))

Summary of the main idea

The main idea to generate a sequence of integers and to repeat it for a number of times is as follows:

=MOD(SEQUENCE(max_num * num_reps, , 0), max_num) + 1

where the parameters are defined as:

max_num = maximum number to generate up to,
num_reps = number of repeats.

We use the LAMBDA function as our first step in automating this functionality:

=LAMBDA(
max_num,
num_reps, 
MOD(SEQUENCE(max_num * num_reps, , 0), max_num) + 1)(3, 4)

Therefore we get a sequence of integers repeated a number of times.

Then we tidy up the LAMBDA using the LET function by introducing intermediate variables that will hold values and calculations:

=LAMBDA(max_num, num_reps,
LET(
num_rows,
max_num * num_reps,
MOD(SEQUENCE(num_rows, , 0), max_num) + 1))(3, 4)

The LAMBDA and LET functions make the formula more readable and the main idea is coded clearly.

Summary of further improvements

To allow for different starting and ending numbers for the sequence to be repeated, we cater for these parameters as follows:

=LAMBDA(start_num,end_num,num_reps,
LET(
length,
IF(start_num < end_num, end_num - start_num + 1, NA()),
num_rows,
length * num_reps,
MOD(SEQUENCE(num_rows, , 0), length) + start_num))(5, 8, 3)

We can allow for a blank starting number to default to 1:

=LAMBDA(start_num,end_num,num_reps,
LET(
mod_start,
IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 1, start_num),
length,
IF(mod_start < end_num, end_num - mod_start + 1, NA()),
num_rows,
length * num_reps,
MOD(SEQUENCE(num_rows, , 0), length) + mod_start))(, 8, 3)

Last but not least, we can allow for various step sizes, including negative sizes. The negative step sizes will reverse the order of the sequence to be repeated:

=LAMBDA(start_num,end_num,num_reps,step_size,
LET(
mod_start,
IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 1, start_num),
length,
IF(mod_start < end_num, end_num - mod_start + 1, NA()),
valid_step,
IF(OR(ISBLANK(step_size), TRIM(step_size)="", step_size=0), 1, step_size),
mod_len,
CEILING.MATH(length/ABS(valid_step) ) * ABS(valid_step),
num_rows,
IF(AND(num_reps > 0, num_reps = INT(num_reps)), mod_len * num_reps, NA()),
mod_num_rows,
CEILING.MATH(ABS(num_rows/valid_step)),
new_start,
IF(valid_step < 0, end_num - mod_start, 0),
MOD(SEQUENCE(mod_num_rows, , new_start, valid_step), mod_len) + mod_start)
)(5, 10, 3, -2)

And voila, we can repeat a reversed sequence with different a step size:

I will draw the line here, as one can go crazy 🤪 with customizations. Thus, I shall leave it up to you to come up with your own modifications. 😎

Back to the Table of Contents.

Bonus I — Getting familiar with the SEQUENCE function

The syntax for the SEQUENCE function is =SEQUENCE(rows, [columns], [start], [step])where the inputs are:

• rows is the number of rows to generate. If only this input is passed as an argument then it will generate a column of integers 1 up to the number of rows input, for example =SEQUENCE(10) will return numbers 1 to 10.
• [columns] is the optional number of columns to generate. The default is value 1. If we input the number of rows and columns, then it will output a 2D array a.k.a. a matrix containing the numbers 1 up to rows * columns. To generate a matrix of numbers 1 to 36, try the following formula =SEQUENCE(6, 6).
• [start] changes the starting number from the default of 1. For example, if you would like to start your sequence at the number 10, then simply change the third input argument to 10.
• [step] specifies the step size other than the default 1. For example, if you would like to generate the first ten odd numbers, then run the formula =SEQUENCE(10, , , 2) or if you are not comfortable with passing blank arguments you can run =SEQUENCE(10, 1, 1, 2).

Generating an array of numbers using a function is nothing new. For example, we can generate a list of ten numbers in Python instantly with:

nums = list(range(10))

Mind you, this command produces numbers 0 to 9. It can be easily modified to give us the numbers 1 to 10 as follows:

nums = list(range(1, 11))

The SEQUENCE function lets us generate a dynamic array of numbers in an Excel sheet effortlessly. Dynamic array functions such as the SEQUENCE function, are simply formulas that “spill” automatically in the neighbouring blank cells. These make the legacy Ctrl+Shift+Enter(CSE) array formulas redundant.

For the purposes of this tutorial, we will make use of the dynamic array functionality which is available for Excel 2021, Excel for Microsoft 365 and Excel for the web subscribers.

Bonus II — Replicate the Sequence Repeating functionality in VBA

It hasn’t always been this easy to generate a sequence of numbers with the aid of a dynamic array function. Prior to the introduction of the SEQUENCE function, we had to write some kind of User Defined Function (UDF) to call in a spreadsheet.

The above is an example of how to use an UDF to generate the repeating numbers. If you are curious how to replicate number sequences with VBA, then please follow the link for the VBA tutorial.

Downloads

You are more than welcome to download a copy of the demo Excel workbook here. Enjoy!

Before you open the file downloaded from GitHub for the first time, make sure to right-click on the file and select Properties. In General > Security, select the “Unblock” check-box and then click the “Apply” button.

Back to the Table of Contents.