- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

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

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.

## Conclusion

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.

- Related Questions & Answers
- Check if a number is Full Prime in C++
- Bash program to check if the Number is a Prime or not
- C# Program to check if a number is prime or not
- Python program to check if a number is Prime or not
- PHP program to check if a number is prime or not
- Check if a number is Quartan Prime or not in C++
- Check if a number is Primorial Prime or not in Python
- Check if a number is Primorial Prime or not in C++
- Check if a number is a Pythagorean Prime or not in C++
- Write a C# program to check if a number is prime or not
- Check if a number is divisible by all prime divisors of another number in C++
- Is the reversed number a prime number in JavaScript
- Python program to check if the given number is a Disarium Number
- Python Program to Check if a Number is a Perfect Number
- Python Program to Check if a Number is a Strong Number
- Golang Program to Check if a Number is a Perfect Number