Generate a Prime Numbers sequence 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, repeat a sequence of numbers multiple times, generate the Fibonacci sequence and in this tutorial, how to generate a sequence of prime numbers. 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 as sequence of prime numbers. Yes, you read correctly! We will generate an automation with Excel built in functions only such as the SEQUENCE function and more, to generate a primes by calling this new function.

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 — Work out the Logic in VBA
· Part II — Generating a Prime Numbers sequence with Excel built-in functions only
∘ Part II.a — Check if a number is Prime
∘ Part II .b — Generate the Prime Numbers sequence
· Summary
· Bonus — Getting familiar with the SEQUENCE function
· Downloads

Part I — Work out the Logic in VBA

I did state in the introduction that we will not be using VBA. However, in this case, it is quite valuable to figure out the logic for how to generate a sequence of prime numbers in VBA before we can jump ahead to using Excel’s built in functions.

Firstly, please make sure that you have the following set up at the top of the VBA module. The Option Base 1 statement is important to ensure that the array indexing starts at 1.

Option Explicit
Option Base 1

Secondly, we should write a function that checks if a number is prime. It is better to have a separate function for this check for reusability purposes. The main idea here is that if we can find a factor that divides perfectly into a given number N, then this number is not prime. Otherwise, Bingo! We have found a prime number. 😊

Private Function IsPrime(N As Long) As Boolean

    On Error GoTo error_handler:
    
    IsPrime = False
    
    If N < 2 Then GoTo end_check
    
    Dim Limit As Long
    Limit = WorksheetFunction.Floor_Math(VBA.Sqr(N))
    
    If Limit = 1 Then GoTo it_is_prime
    
    Dim i As Long
    For i = 2 To Limit
        If N Mod i = 0 Then Exit Function
    Next i
    
it_is_prime:
    IsPrime = True

end_check:
    Exit Function

error_handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, _
        Title:="Is Prime"
    On Error GoTo 0
    
End Function

Next, we can write the integers sequence generator. We will make use of this base sequence to check each of it’s elements one-by-one if it is prime.

Private Function IntSequence(N As Long _
                            , Optional StartInteger As Long = 1 _
                            , Optional StepSize As Long = 1 _
                            ) As Long()

    On Error GoTo error_handler
    
    Dim sequence() As Long
    ReDim sequence(1 To N)
    
    Dim counter As Long
    For counter = 1 To N
        sequence(counter) = StartInteger + StepSize * (counter - 1)
    Next counter
    
    IntSequence = sequence
    Exit Function
    
error_handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, _
        Title:="Generate Integers Sequence"
    On Error GoTo 0
    
End Function

Alternatively, we could also make use of WorksheetFunction.Sequence(N) to generate the base sequence as follows:

Public Sub testSeqWkFunc()
    
    Dim nums() As Variant
    nums = WorksheetFunction.sequence(1, 10)
    
    Dim i As Long
    For i = 1 To 10
        Debug.Print nums(i)
    Next i
    
End Sub

However, I prefer to write my own function for this tutorial. 🤓

Next, we can generate the prime numbers sequence up to the specified NumbersUpTo limit by checking each number if it is prime or not. If yes, then add it to the collection of primes. It is as easy as that.

Private Function GeneratePrimesSequence(NumbersUpTo As Long) As Long()

    On Error GoTo error_handler
    
    Dim sequence() As Long
    sequence = IntSequence(NumbersUpTo)
    
    Dim primesCol As Collection
    Set primesCol = New Collection
    
    Dim i As Long
    For i = 1 To NumbersUpTo
        If IsPrime(i) Then primesCol.Add i
    Next i
    
    Dim primes() As Long
    primes = IntegerCollectionToArray(primesCol)
    
    GeneratePrimesSequence = primes
    Exit Function

error_handler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description, _
        Title:="Generate Primes Sequence"
    On Error GoTo 0
    
End Function

Note that the code above makes use of this short helper function:

Private Function IntegerCollectionToArray(col As Collection) As Long()

    Dim arr() As Long
    ReDim arr(1 To col.count)
    
    Dim i As Long
    For i = 1 To col.count
        arr(i) = CLng(col.Item(i))
    Next i
    
    IntegerCollectionToArray = arr
    Erase arr
    
End Function

Lastly, we turn the primes generator function into an User Defined Function (UDF) by specifying Application.Volatile:

Public Function UDF_GeneratePrimesSequence(NumbersUpTo As Long) As Variant

    Application.Volatile
    
    If NumbersUpTo > 1 Then
        UDF_GeneratePrimesSequence = Application.Transpose( _
                                     GeneratePrimesSequence(NumbersUpTo))
    Else
        UDF_GeneratePrimesSequence = CVErr(xlErrValue)
    End If

End Function

Therefore, we can use the UDF in Excel simply as follows:

In summary, the general algorithm for generating a prime numbers sequence up to a specified limit is:

• Generate a base sequence of numbers up to the specified limit NumbersUpTo.
• Next, we need some way to check if a number is prime or not.
• Lastly, if the test number is prime, then add it to the sequence.

Part II — Generating a Prime Numbers sequence with Excel built-in functions only

We have worked out the basic algorithm in Part I, so how difficult can it be to replicate this with Excel’s built-in functions such as the SEQUENCE dynamic array function? Well, RUN DMC said it best: It’s tricky! 😏

Disclaimer: The following solution is the best idea that I could come up with at the time after a lot of experimentation!

Part II.a — Check if a number is Prime

Let’s begin with the easiest part by generating a base sequence of integers, starting at the first prime number 2:

=SEQUENCE(100,,2)

Next we figure out the number we need to loop for until we find out if a number is prime or not:

=LAMBDA(x, FLOOR.MATH(SQRT(x)))(A2#)

Here is where things get tricky! We first need to generate a sequence of factors up to the number calculated in column B, so that we can divide each factor into the test number and return the remainder with the MOD function.

=IF($B2 > 1, MOD($A2, SEQUENCE(, $B2-1, 2)), 1)

Note: Make sure to press F9 to calculate the sheet after you drag down the formula i.e., if your Calculation method is set to Manual. Then you should see the dynamic arrays on each row as follows:

Our clue here is if we see any remainder of 0 starting in column C looking to the right, then we know that particular number is not prime. For number 2 and 3, I’m forcing the remainder to 1 as can be seen in the IF statement above.

Therefore, counting zeros should work in theory:

=COUNTIF(C2:N2, 0)

However, if we combine the counting function with the above formula:

=COUNTIF(IF($B2 > 1, MOD($A2, SEQUENCE(, $B2-1, 2)), 1), 0)

The nested formula returns the #VALUE! error, unfortunately. The COUNTIF function probably does not work with dynamic arrays. Hence, we need to think differently i.e., pass the sequence of remainders to a Lambda function and sum all the zero remainders that we find.

=SUM(LAMBDA(x,IF(x=0, 1, 0))(IF($A2<2, {0}, IF($B2 > 1, 
MOD($A2, SEQUENCE(, $B2-1, 2)), {1}))))

The zero counts in column P do match the zero counts in column O:

Note that the formula above has some validation included.

Next, we simply check if the sum of zero counts is equal to 0. Then we know that we do not have any zero remainders, thus the test number is prime.

=SUM(LAMBDA(x,IF(x=0, 1, 0))(IF($A2<2, {0}, IF($B2 > 1, 
MOD($A2, SEQUENCE(, $B2-1, 2)), {1})))) = 0

This returns a Boolean value i.e., if the test number in column A is prime:

Next we need to combine the whole prime validation function with the calculation for the loop count stored in temporary variable loop_to into one Lambda function, so that we can save it in Name Manager:

=LAMBDA(test_num, LET(loop_to, LAMBDA(x, FLOOR.MATH(SQRT(x)))(test_num), 
SUM(LAMBDA(x,IF(x=0, 1, 0))(IF(test_num < 2, {0}, IF(loop_to > 1,
MOD(test_num, SEQUENCE(, loop_to - 1, 2)), {1})))) = 0))(A2)

Note that all you need to input into this convoluted formula is test number from column A:

Lastly, we save the formula without the input argument in to the Name Manager in the Formulas Ribbon:

Now we can use the saved IsPrime formula just like an UDF as you can see in column R:

An alternative wacky formula that I came up, makes use of TEXTJOIN to create a string of the remainders, and then searches for “,0” in the text string.

=LAMBDA(test_num, IF(INT(test_num) <> test_num, FALSE, IF(test_num < 2, 
FALSE, LET(loop_to, FLOOR.MATH(SQRT(test_num)), ISERR(SEARCH(",0", 
IF(loop_to > 1, "," & TEXTJOIN(",",TRUE, MOD(test_num, SEQUENCE(, 
loop_to - 1, 2))), ",1")))))))($A2)

This does work indeed. I do prefer to stick to arithmetic rather than manipulating text. Therefore, we shall use the formula saved as IsPrime.

Part II .b — Generate the Prime Numbers sequence

In the last part we need to apply the IsPrime saved function to all generated numbers. Easy, right? Well, not quite. This still requires a bit of extra thinking.

=MAKEARRAY(100,1,LAMBDA(r,c, IsPrime(r)))

We shall use the MAKEARRAY function, as I have tried to pass a Sequence to the IsPrime saved function ie., =LAMBDA(x, IsPrime(x))(SEQUENCE(100)) and it didn’t seem to work. Making a Boolean array did work successfully:

The idea is then to multiply each test number with it’s corresponding Boolean check. Excel does evaluate TRUE to 1 and FALSE to 0 in arithmetic operations.

=MAKEARRAY(100,1,LAMBDA(r,c, IsPrime(r))) * SEQUENCE(100)

To make this more dynamic, we need to pass the number limit instead of hardcoding 100. We nest the above element-wise product inside the following Lambda function:

=LAMBDA(N, MAKEARRAY(N,1,LAMBDA(r,c, IsPrime(r))) * SEQUENCE(N))(100)

At this point, Donkey might ask “Are we there yet?” 😊 Yes, as you can see we are able to generate a sequence of prime number up to the N specified. However, as you may have noticed, there are a lot of zeros in the generated sequence, which we do not want.

Let’s first save the above automation as the UnfilteredPrimes function:

We will use the saved UnfilteredPrimesfunction to filter out the zeros as follows:

=FILTER(UnfilteredPrimes(100), UnfilteredPrimes(100) <> 0)

Lastly, let’s automate this filter as follows:

=LAMBDA(N,FILTER(UnfilteredPrimes(N), UnfilteredPrimes(N) <> 0))(100)

We can now save the Lambda function to the Name Manger, and hey presto!

We finally have prime numbers generator built only with Excel functions, no VBA! 😅

Summary

There are three steps that you need to follow to generate a sequence of prime numbers in Excel.

Firstly, we save the function “IsPrime” in the Name Manager. As the name indicates, this function checks if a given number is prime:

=LAMBDA(test_num,
LET(
loop_to,
LAMBDA(x, FLOOR.MATH(SQRT(x)))(test_num),
SUM(LAMBDA(x,IF(x=0, 1, 0))(IF(test_num < 2, {0}, IF(loop_to > 1,
MOD(test_num, SEQUENCE(, loop_to - 1, 2)), {1})))) = 0))

Secondly, we save the function “UnfilteredPrimes” in the Name Manager. This generates a sequence of numbers up to the specified max number N, and checking each number if it is prime. If the test number is not prime, we output a zero:

=LAMBDA(N, MAKEARRAY(N,1,LAMBDA(r,c, IsPrime(r))) * SEQUENCE(N))

Lastly, we save the function “Primes” which will filter out the zeros from the previous function.

=LAMBDA(N,FILTER(UnfilteredPrimes(N), UnfilteredPrimes(N) <> 0))

That’s it. In that case that you have jumped straight to the end to see the formulas but they don’t make sense, then please read the explanations above. 🤓

Back to the Table of Contents.

Bonus — 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).

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.

Note: The dynamic array functionality is available for Excel 2021, Excel for Microsoft 365 and Excel for the web subscribers only.

Downloads

You are more than welcome to download the demo file here.

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.