How to apply data validation to allow numbers only in Excel?
To allow only numbers in a range of cells, this tutorial provides some methods to help you easily achieve it.
Apply data validation to allow numbers only in Excel
Apply VBA code to allow numbers only in Excel
Easily allow numbers only in a range of cells with an amazing tool
More tutorials for Data validation...
Apply data validation to allow numbers only in Excel
You can use data validation with a custom formula to allow only numbers in a range of cells in Excel. Please do as follows.
1. Select the range of cells that you only allow numbers to be entered.
2. Click Data > Data Validation > Data Validation. See screenshot:
3. In the Data Validation dialog box, please configure the following operations:
- 3.1 Select Custom from the Allow drop-down list;
- 3.2 Copy the below formula into the Formula box;
- =ISNUMBER(A2)
- 3.3 Click the OK button.
Note: In the formula, A2 is the first cell of the selected range.
From now on, only numbers can be entered into the specified range.
Apply VBA code to allow numbers only in Excel
Besides, you can apply the below VBA code to allow numbers only in a range of cells.
1. In the worksheet you will limit inputs. Right click the sheet tab and click View Code from the context menu.
2. In the Microsoft Visual Basic for Applications window, copy the below VBA code into the Code window.
VBA code: Allow numbers only in a range of cells
Public mBol As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20191120
Dim xStrV As String
Dim xRg As Range
Dim xIRg As Range
Dim xFNum As Integer
On Error Resume Next
If Not mBol Then
Application.ScreenUpdating = False
Set xRg = Range("A2:A12")
If Not Intersect(xRg, Target) Is Nothing Then
xStrV = Target.Value
If Not IsNumeric(xStrV) Then
mBol = True
Target.Value = vbNullString
MsgBox "Only numbers are allowed in this range", , "Kutools"
End If
Application.ScreenUpdating = True
End If
Else
mBol = False
End If
End SubNote: In the code, A2:A12 is the range of cells which you only allow entering numbers. Please change it as you need.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
From now on, only numbers can be entered into the specified range.
Easily allow numbers only in a range of cells with an amazing tool
If you want to try an easier method, here recommend you to use the Prevent Typing feature of Kutools for Excel.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the range of cells which you only allow to enter numbers, click Kutools > Prevent Typing > Prevent Typing.
2. In the Prevent Typing dialog box, please:
- 2.1 Check the Allow to type in these chars option;
- 2.2 Enter 0123456789 into the text box;
- 2.3 Click the OK button. See screenshot:
3. Click OK in the next popping up Kutools for Excel dialog box to go ahead.
Note: If there is data validation in selected range, click Yes will remove them, or you can click No to cancel the operation.
4. Another Kutools for Excel dialog box pops up to remind you which numbers are allowed to type in specified range. Click OK to finish the whole settings.
From now on, only numbers are allowed to type in these specified range.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Related articles
Allow only date format in specific cells
How could you restrict a list of cells to only allow entering date format in Excel worksheet? In this article, we will talk about some interesting tricks for dealing with this job.
Validate email addresses in a column of worksheet
As we all known, a valid email address is composed of three parts, the user name, the "at symbol" (@), and the domain. Sometimes, you just allow others to enter only the email address format text into a specific column of worksheet. Methods in this article will make it possible to achieve in Excel.
Apply data validation to force phone number format in Excel
May be, there are various phone number formats can be used when typing into the Excel workbook. But, how could you only allow one phone number format to be entered in a column of worksheet? For example, I just want the phone number as this format 123-456-7890 to be allowed entering. The methods in this article will do you a favor.
Use data validation to only allow SSN number entered in Excel
For recording the SSN number of all employees and forcing them to type the SSN number in xxx-xx-xxxx format in a column, you can use the Data Validation function to solve the problem.
Three methods to validate cells to only accept IP address in Excel
While using Excel, do you know how to set up a column or a range of cells to only accept IP address format (xxx.xxx.xxx.xxx)? This article provides several methods for you to deal with it.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
