How To Check If The Number Is A Prime Number In Excel?

MS ExcelMicrosoft TechnologiesAdvanced Excel Function

What if you could find out whether a number in your Excel data set is prime or not in less than 5 steps? We will tell you how in this tutorial, but first, let us understand what a prime number is.

A prime number is a number which is divisible only by 1 and itself, and the number 1 is not a prime number. Applying this simple definition, you can use the formula described in the steps to determine if a number is indeed a prime number or not.

Array Formula For Checking Prime Numbers

An array formula simultaneously performs multiple calculations on items in a row or column. Depending on the instructions, the result can be either multiple or single. An example worksheet illustrates how you can use the formula mentioned in the steps to test whether a number is prime.

Step 1 − Select an empty cell next to the one you want to check for a prime number. In this case, we select cell B2.

Step 2 − To check the values for cell A2, enter the following formula in cell B2 and press the Control + Shift + Enter keys to complete the function.

Array formula to check if the number in MS Excel is a prime number =IF(A2=2,"Prime",IF(AND(MOD(A2,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A2),0)))) <>0),"Prime","Not Prime"))

If you want to check the values of other cells in the array, for example, cell C7, you should modify the array formula before pasting −

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

Step 3 − In cell B2, "Prime" will appear if the number is prime, and "Not Prime" if not. The array formula will be closed with a set of { } in the formula box, indicating that the function has been successfully performed.

Step 4 − To check for a column of numbers, like those in Column A, simply drag the green box in the corner of active cell B2, where the array function was performed, to the numbers you want. From a list of multiple values, you can identify prime numbers in seconds using this method.

How Does The Formula Work?

As mentioned before, an array formula contains a set of functions to get the correct result. It 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.

Following are the instruction set in the formula −

  • Select the cell number

  • If the number is 2, it is a prime number; otherwise, go to the next stage.

  • Determine the number's square root.

  • Divide the number by all the integers between 2 and the number's square root.

  • If the remainder in any division is zero, the value is non-prime.

  • Otherwise, it is the prime number.


Practising Excel daily and learning new tools and tricks improves your skills. There are more such tutorials to help you master the spreadsheet in no time.

Updated on 20-Sep-2022 07:30:43