You can create your own data validation to allow numbers only in Excel. To do this you need to use the ISNUMBER formula. In this tutorial, you learn how to create a data validation to allow numbers only using the ISNUMBER function.
How to use ISNUMBER Function in Excel for Data Validation
Data validation is a good practice to reduce issues during the audit. The data validation Excel ISNUMBER function helps maintain this by allowing only number and date values. In the following example, you have two columns values and results. The values column has data belonging to different types and the result column shows whether the values are numbers. To do this:
- Go to cell B2.
- Insert the formula
- Click Enter.
- Drag the formula to the cells B2 to B8 with your mouse.
This will show in column B whether the values in column A are numbers.
Data Validation to Allow Numbers only
Using this logic you can set a data validation rule that allows only numbers in a column. The following example uses a student data sheet. Column A has the names, column B has the classes and Column C will have the ages. To limit column C to accept only number values:
- Select cells C2:C6 by selecting cell C2 and dragging it till cell C6 with your mouse.
- Go to Data > Data Validation.
- set Allow to be Custom.
- In the Formula field type the formula
- Click Ok.
This will set a data validation to allow numbers only in column C. Trying other data types will result in an error. You can set your own custom error message. To do this:
- After completing step 4, go to the Error Alert tab and set the Error Message to“Input not a number. Please enter a valid number.”
- Click Ok.
If you try to enter a non-number in column C, Excel will show this message now.