Prime Numbers

A prime number is a natural number which has exactly two distinct natural number divisors: One and itself.
The number one is not a prime number.

The formula below will test if a number is prime. It will test all whole numbers not exceeding 268,435,455.

=IF(C9=2,"Prime",IF(AND(MOD(C9,ROW(INDIRECT("2:"&ROUNDUP(SQRT(C9),0))))<>0),"Prime","Not Prime"))

This is an array formula.  Therefore, you must press CTRL SHIFT ENTER after entering the formula and whenever you edit it later.
You must enter the test number in cell C9.

The formula relies on Excel's MOD function to calculate the remainder on every whole number divisor from 2 to
the square root of the test number. If there is always a remainder then the number is prime.

Excel's ability to calculate the remainder fails when the test number exceeds 268,435,455.

You can download a sample spreadsheet: Primality Test.