## How to generate all prime numbers between two given numbers in Excel?

In Excel, have you ever tried to list all prime numbers between two given numbers? This article, I will talk about some interesting methods to deal with this task.

**Generate all prime numbers between two given numbers with formulas**

**Generate all prime numbers between two given numbers with User Defined Function**

#### ** Generate all prime numbers between two given numbers with formulas**

Combining the **Define Name** function and formulas, you can list or generate all of the prime numbers between the two specific numbers. Please do as follows:

**1**. Please create the first range name by clicking **Formulas** > **Name Manager**, and in the **Name Manager** dialog box, click **New** button, see screenshot:

**2**. In the **New Name** dialog box, in the **Name** text box, please enter** rng** as the range name, and then enter this formula: **=ROW(INDIRECT(Sheet1!$B$1&":"&Sheet1!$B$2))** (* Sheet1* is your current worksheet you used,

*and*

**B1***are the start and end numbers you specified )into the*

**B2****Refers to**text box, then click

**OK**button to return to the former dialog. See screenshot:

**3**. Go on clicking **New** button to create another range name, in the **New Name** dialog box, enter a name **prime** into the **Name** text box, and then enter this formula: **=SMALL(IF(MMULT(--(IF(rng>TRANSPOSE(rng-Sheet1!$B$1+2),MOD(rng,(rng>TRANSPOSE(rng-Sheet1!$B$1+2))*TRANSPOSE(rng-Sheet1!$B$1+2)))=0),rng-Sheet1!$B$1+2)=0,rng),ROW(INDIRECT("1:"&Sheet1!$B$2)))** (* rng* is the first range name you have created in step2) into the

**Refers to**text box, see screenshot:

**4**. Then click **OK** and close the dialogs, and select one column that you want to list all the prime numbers, and enter this formula: **=IFERROR(prime,"")**(* prime* is the range name you created in step 3) into the formula bar, and then press

**CTRL + SHIFT + ENTER**keys together, all the prime numbers between the given two numbers are listed as following screenshot shown:

#### ** Generate all prime numbers between two given numbers with User Defined Function**

If the first method is somewhat difficult, here, you can apply a **User Defined Function** to solve it.

**1**. Hold down the **ALT + F11** keys to open the** Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** > **Module**, and paste the following code in the **Module** Window.

**VBA code: Generate all prime numbers between two specific numbers:**

```
Function PRIME(St, En As Long)
'Updateby Extendoffice 20160613
Dim num As String
For n = St To En
For m = 2 To n - 1
If n Mod m = 0 Then GoTo 20:
Next m
num = num & n & ","
20:
Next n
PRIME = num
End Function
```

**3**. Then save and close this code, go back to the worksheet, please enter this formula: **=prime(10,100)** (* 10 *is the start number, and

*is the end number that you want to get the prime numbers between, you can change them to your need), and then press*

**100****Enter**key, and all the prime numbers are displayed into one cell, see screenshot:

### Best Office Productivity Tools

**Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. ** **Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...**

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!