Finding Prime Numbers.

This web page is designed to help you to use Excel to find prime numbers.
Remember that a prime number will only divide by 1 and by itself.
2,3,5,7,11 are primes. 4,6,8 and 9 are not.

Open an new Excel Workbook.

This is a method known as the Seive of Erasthones. This is a very slow way to find primes. You could use VBA to write a much better program. You could also adapt the worksheet to work only on odd numbers above 2 or to only work on the list of primes that you have found.

Step 1

Copy all of the contents of this table (Ctrl c) and paste it (Ctrl v) into Excel.

Primes

Target

Remainder

2

5

=MOD(B2,A2)

3

=b2

=MOD(B3,A3)

4

=b3

=MOD(B4,A4)

What do the Formulae do?

You don't need to read this but you might find that this helps you understand what is going on.

You need to know if anything divides into your target number other than 1 or the number itself. If something divides into your target number a whole number of times then it leaves remainder 0.

MOD(number1, number2)
returns the remainder from number1 divided by number2.
eg
MOD(9,4) = 1
MOD(13,5) = 3

Step 2

Your worksheet should look like this.

Select the 2nd and 3rd rows of all three columns. If you grab the bottom right hand corner whilst holding down the left mouse button then you can fill down the A, B & C column as far as 1000 simply by keeping the mouse button down as you drag. You don't need to go any further at this stage.

Step 3

If all has gone well then the top of your spreadsheet should look like this. If you started with 27 in cell B2, as your target number, then you should get the same numbers as me.
You can see that 27 leaves remainder 0 when divided by 3 or 9.
This can be quite hard to spot.

Select column C by clicking on the top cell with "C" in it. This should highlight the whole column. Now click on the Format tab and select conditional formatting.

Now use the tabs in the conditional formatting boxes to set the cells up so that they go purple if the remainder is 0.

Now any divisors of your target number stand out easily. You can use this worksheet to find primes upto 10000 quite easily.

Here I have shown that the number 20413 is composite because it divides by 137 and 149.

Improvements

At present the "primes" column has got composite numbers in as well. You could speed the operation up by removing composite numbers from the list. This would also increase the number of primes that your worksheet can find.
To do this, simply cut the whole row containing the composite number out of the spreadsheet.

Challenges

What is the largest prime that you can find using your spreadsheet?

17 & 19 are twin primes. A pair of twin primes are separated by 2.
What is the largest pair of twin primes that you can find?