Repeat Numbers in Ascending Order 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 a sequence of numbers multiple times, generate the Fibonacci sequence, generate a sequence of prime numbers, and in this article, repeat numbers in ascending order. 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 generate a sequence consisting of repeating numbers. More specifically, we will be able to repeat each number multiple times in ascending order such as: 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 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 the Numbers in Ascending Order
· Part II— Automating the Repetition of Numbers in Order
· Part III— Further Improvements
· Part IV — Apply Repeating Numbers to Business Cases
· Summary of the main idea
· Summary of further improvements
· Bonus I — Getting familiar with the SEQUENCE function
· Bonus II — Replicate the Repeat Numbers in Order functionality in VBA
· Bonus III — The Unit Vector
· Downloads

Part I — Repeat the Numbers in Ascending Order

Let’s say that we want to generate numbers 1 to 10, an repeat each number three times i.e., 1, 1, 1, 2, 2, 2, …, 10, 10, 10. First we set up the inputs that we require. Those will be:

• “Numbers up to” i.e., the maximum number that we want to generate the sequence up to. In our case that will be the number 10.
• “Number of Repetitions for each Number” i.e., 3 in our example.

Thus, in total we will be generating as sequence of 30 numbers i.e., “Numbers up to” * “Number of Repetitions for each Number” = 10 * 3.

If we simply try to run =SEQUENCE(30), then that is clearly not what we want to achieve. However, this is a start.

Next, if we divide each of the 30 numbers in the sequence above by the number of repetitions for each number i.e., 3 repetitions in our case, then we get the following:

Notice that the numbers 1 to 10 that we want repeating 3 times do actually appear every third occurrence. All that there is left to do now is to take the CEILING of each number in our sequence in cell B5. This function will round each number up to the nearest integer and voila!

In summary, running the following formula =CEILING.MATH(SEQUENCE(10 * 3) / 3) will return the numbers 1 to 10 repeated 3 times each. Therefore, the general idea is this:

=CEILING.MATH(SEQUENCE(max_num * rept_each) / rept_each)

Were the inputs are:

• The maximum number max_num, and
• The number of repeats rept_each.

These inputs can be integers or cell references as shown above.

Part II— Automating the Repetition of Numbers in Order

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,rept_each, 
CEILING.MATH(SEQUENCE(max_num * rept_each) / rept_each))(10, 3)

Note that the actual inputs 10 and 3 to the Lamba function must follow the function in round brackets i.e., (10, 3).

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 (10, 3)!

Make sure to enter the LAMBDA(…) function only without the inputs (10,3).

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:

=CEILING.MATH(SEQUENCE(B5) / B4) 
+ IF(OR(ISBLANK(B1), TRIM(B1) = ""), 0, B1 - 1)

The addition of the “Starting Number” ensures the sequence starts at the desired number. If omitted, the sequence will start at 1 by default. Thus, I have included a validation to check for an empty starting number in cell B1 in this example.

There is one extra calculation in the above example that is, the “Count of Numbers to be generated” calculated in cell B3 =B2 - IF(OR(ISBLANK(B1), TRIM(B1) = ""), 0, B1 - 1). This is used in turn in the calculation of the “Total Sequence Length” in cell B5.

Adding the count calculation to the improved formula, then the resulting formula will look rather complicated:

=LAMBDA(start_num, end_num, reps, 
LET(count, end_num - IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 0, 
start_num - 1), CEILING.MATH(SEQUENCE(count * reps) / reps) + 
IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 0, start_num - 1)))

Note that I am using the LET function to temporarily store the total count =end_num — IF(OR(ISBLANK(start_num), TRIM(start_num) = “”), 0, start_num — 1), so that the intermediate variable count will be calculated only once and will be readily available for other calculations. See cell C7:

The complexity of this formula gives us a good reason to save it in the Name Manager in the Formulas Ribbon. NB: Remember to leave out the inputs after the =LAMBDA() function in the “Refers to” box.

Now we can use the second stored function REPEAT_NUMS_IN_ORDER2 (apologies that I couldn’t think of a more creative name) to generate the exact same sequence in cell D7:

Part IV — Apply Repeating Numbers to Business Cases

If you are wondering how we can use repeating numbers in a real work example, then here is a suggestion. Say we need to build a report which requires repeated entries per client. Here we can use the CHOOSE function in combination with our custom REPEAT_NUMS_IN_ORDER to list each client name a number of times as follows:

=CHOOSE(REPEAT_NUMS_IN_ORDER(4, 5), 
"Client A", "Client B", "Client C", "Client D")

Alternatively, we can list the values we wish to iterate through in a range. Then using the INDEX function, we simply use the saved function for the row indices input:

=INDEX($C$1:$C$4, REPEAT_NUMS_IN_ORDER(4, 5))

Summary of the main idea

The main idea to repeat each number in a sequence multiple times in order is achieved as follows:

=CEILING.MATH(SEQUENCE(max_num * rept_each) / rept_each)

where the parameters are defined as:

max_num = the maximum number to generate sequence up to,
rept_each = the number of repeats for each number in the sequence.

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

=LAMBDA(max_num,rept_each,
CEILING.MATH(SEQUENCE(max_num * rept_each) / rept_each))(4, 3)

Thus we get each number in a sequence from 1 to the maximum number in order 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,rept_each,
LET(
num_rows,
max_num * rept_each,
CEILING.MATH(SEQUENCE(num_rows) / rept_each)))(4, 3)

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 of numbers to be repeated in order, we cater for these parameters as follows:

=LAMBDA(start_num,end_num,reps,
LET(
count,
IF(start_num < end_num, end_num - (start_num - 1), NA()),
num_rows,
count * reps,
CEILING.MATH(SEQUENCE(num_rows) / reps) + start_num - 1))(5, 8, 3)

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

=LAMBDA(start_num,end_num,reps,
LET(
mod_start,
IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 0, start_num - 1),
count,
IF(mod_start + 1 < end_num, end_num - mod_start, NA()),
num_rows,
count * reps,
CEILING.MATH(SEQUENCE(num_rows) / reps) + mod_start))(, 4, 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,reps,step_size,
LET(
valid_step,
IF(OR(ISBLANK(step_size), TRIM(step_size)="", step_size=0), 1, step_size),
mod_start,
IF(OR(ISBLANK(start_num), TRIM(start_num) = ""), 0, start_num - 1),
count,
IF(mod_start < end_num, end_num - mod_start, NA()),
mod_count,
CEILING.MATH(ABS(count/valid_step)) * ABS(valid_step),
length,
mod_count * reps,
num_rows,
CEILING.MATH(ABS(length/valid_step)),
seq_count,
CEILING.MATH(ABS(count/valid_step)),
INDEX(SEQUENCE(seq_count,,IF(valid_step>0, start_num, end_num),valid_step), 
CEILING.MATH(SEQUENCE(num_rows,,,ABS(valid_step)) / ABS(reps*valid_step)))
))(1, 10, 3, -2)

Allowing for the custom step size was a real mental challenge, to say the least… I have managed in the end, by make clever use of the INDEX function. I have saved the final function as REPTNUMS, and voila!

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 Repeat Numbers in Order 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.

Bonus III — The Unit Vector

Excel has a limited offering of Linear Algebra functions. One of these is the MUNIT function for generating the Identity Matrix. However, Excel does not have a function to generate an array of 1’s a.k.a. “unit vector” or an array of 0’s a.k.a. “zero vector”, or does it?

Surprisingly, the unit vector can be generated effortlessly with the SEQUENCE function by simply setting the step to 0:

=SEQUENCE(10,,,0)

This is because the start value is set to 1 by default.

If we wanted to generate the zero vector or any other constant vector, all we have to do is change the starting number:

=SEQUENCE(10,,0,0)

And for the fun of it:

Alternatively, you could use the new MAKEARRAY function:

=MAKEARRAY(10, 1, LAMBDA(r, c, 1))

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.