Skip to main content

How to apply data validation to allow numbers only in Excel?

Author: Siluvia Last Modified: 2019-11-25

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

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations