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.

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.

More tutorials for Data validation...


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.