Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Data validation allow numbers only

Read time: 39 minutes

Data Validation is a feature in Microsoft Excel which restricts the values or type of data that users enter into a cell.  It automatically checks whether the value entered is allowed based on the specified criteria. This step by step tutorial will assist all levels of Excel users in allowing numbers only by using Data Validation

Figure 1. Final result: Data validation allow numbers only

Working formula: =ISNUMBER(C3)

Syntax of ISNUMBER Function

ISNUMBER function tests if a value refers to a number and returns TRUE; otherwise it returns FALSE

=ISNUMBER(value)

  • value – the value that we want to test;  value argument can be a blank or empty cell, error or logical value, text, date, number or a cell reference

Setting up Our Data

Our table consists of two columns: Student ID (column B) and Age (column C).  We want to restrict the values entered in column C and allow numbers only. We can do this by using Data Validation.  

Figure 2. Sample data for data validation to allow numbers only

Allow numbers only using Data Validation

We want to restrict the values to input in column C to numbers only.  We can do this with Data Validation by following these steps:

Step 1.  Select the cells whose values we want to restrict.  In this case, select cells C3:C7

Step 2.  Click the Data tab, then the Data Validation menu and select Data Validation

Figure 3.  Selecting Data Validation

The Data Validation dialog box will pop up.  

Figure 4.  Data Validation preview

Step 3.  Click the Allow: drop-down button and select Custom as Validation criteria

Step 4.   Enter the following in the Formula bar:  =ISNUMBER(C3)

ISNUMBER returns TRUE for numeric values and FALSE if otherwise.  If the value entered in the cell is not a numeric value, Data Validation fails and the value is restricted.  

Figure 5.  Creating a Data Validation rule

Step 5.   Click OK

We have now restricted the values in C3:C7 to allow numbers only through Data Validation.  Let us try and enter “18” into cell C3.  

Figure 6.  Numeric value allowed by Data Validation

The value “18” is allowed because it is a numeric value.  Now let’s input “twenty” into cell C4.

Figure 7.  Non-numeric value restricted by Data Validation

The value “twenty” is not a number, hence Data Validation has restricted the input of “twenty” into cell C4.  For restricted values, Excel shows a default warning message that says

“The value you entered is not valid.  A user has restricted values that can be entered into this cell.”  

We are then presented with three options: Retry, Cancel or seek Help.  

Customize Error Alert in Data Validation

While using Data Validation, we can customize the error alert or warning message when an invalid value is entered.  This way, we can help the user input the correct values by specifying the values that are allowed in the selected cells.  

Let us follow these steps:

Step 1.  Click the Data tab, then the Data Validation menu and select Data Validation.  

Step 2.  Click the Error Alert tab

Step 3.  Enter the following in the Title bar: Numbers Only

Step 4.  Enter the following in the Error Message bar: Please input numbers only!  

Figure 8.  Customizing error alert in Data Validation

Step 5.  Click OK.

Let’s enter the value “twenty” again into cell C4.  

Figure 9.  Data Validation showing customized error alert

The value “twenty” is still restricted but this time, the error alert says “Please input numbers only!”.  We are thus guided to enter only numbers into the selected cells.  

See below table that is filled up with numbers as allowed by Data Validation.  

Figure 10.  Output: Data Validation allow numbers only

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins
I need a check box to show/hide an answer of an if function
Solved by Z. U. in 23 mins
I need a formula to compare the data in two columns and then export the mismatched data in the 3rd column
Solved by S. Q. in 20 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc