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 Sub

Note: 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.

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.

Related articles