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.
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.