How to apply data validation to allow numbers only in Excel?
When you need to ensure that only numerical values are entered into specific cells or ranges in your Excel worksheet—such as for IDs, calculation inputs, invoice numbers, or survey scores—it is crucial to set up restrictions to prevent accidental entry of text, special characters, or unwanted symbols. Excel provides several methods to achieve this, each with distinct advantages and use cases. This tutorial covers multiple approaches to restrict input so that only numbers are accepted, helping you maintain accurate and reliable data.
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
Excel formula approach for numeric-only validation
More tutorials for Data validation...
Apply data validation to allow numbers only in Excel
One straightforward way to restrict cell entries to numbers only is by using Excel's built-in Data Validation feature combined with a custom formula. This method is particularly useful for quick application on standard Excel sheets, does not require macros, and easily integrates into existing documents. You can apply it to single or multiple ranges, but be aware that it will not prevent the user from pasting non-numeric values or entering numbers in a format Excel interprets as text (like leading apostrophes). Follow these steps to set up the restriction:
1. Select the range of cells where you want to restrict input to numbers only. For example, if your data will be entered in cells A2 to A12, select this range first. Selecting the right range at the start helps to apply restrictions exactly where needed and avoid overwriting existing rules elsewhere in your worksheet.
2. Click Data > Data Validation > Data Validation. See screenshot:
3. In the Data Validation dialog box, perform these actions:
- 3.1 In the Allow dropdown, select Custom to define a personalized validation rule.
- 3.2 Enter the following formula into the Formula box. This formula checks that the entry in the first cell (e.g., A2) is recognized by Excel as a number: =ISNUMBER(A2)
- 3.3 Click the OK button to apply the validation setting.
Note: Make sure that A2 in the formula matches the first cell reference in your selected range. If your range starts at a different cell, update the formula accordingly (for example, use =ISNUMBER(B5) if your range begins at B5).
After these steps, only numerical values can be directly entered into your specified cells. If a user tries to type letters, symbols, or other invalid content, Excel will prevent the input and display an error message. Keep in mind that if someone uses copy–paste to enter prohibited data, this method may not prevent it. Also, input like a number stored as text (for example, '123) will not pass the validation. For enhanced control over pasted data or for more complex rules, consider using VBA or add-in tools. If you want to allow decimals or restrict to whole numbers only, use Data Validation's built-in Whole number or Decimal options instead of 'Custom'.
Troubleshooting tip: If your validation does not seem to work, check that you have no conflicting validation rules already applied in the target range, and ensure your formula uses the correct relative cell reference for the range selection. If you wish to display a user-specific message upon invalid entry, click the Error Alert tab in the Data Validation window and customize the error text.
Apply VBA code to allow numbers only in Excel
For greater flexibility and to intercept pasted as well as typed input, using a VBA macro is an effective option. This script will monitor a defined cell range and clear any entry that is not a number, displaying a warning to the user. VBA-based solutions are especially suitable if you want to enforce data entry integrity at a deeper level or apply complex logic. Be aware that VBA macros must be enabled in your workbook for this method to function.
1. In the worksheet where you wish to restrict entries, right click the sheet tab at the bottom and choose View Code from the context menu. This opens the Microsoft Visual Basic for Applications (VBA) editor for that sheet.
2. In the Microsoft Visual Basic for Applications window, copy the following VBA code and paste it directly into the code area for the selected sheet:
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: Replace A2:A12 in the line Set xRg = Range("A2:A12") with your actual cell range if you need to monitor different cells. This code will immediately clear any non-numeric entry and alert the user. It also applies to pasted values. However, if multiple cells are pasted at once, the code will clear them all, preventing pasting into the target area. This makes it most effective for single-cell entry scenarios.
3. To close the VBA editor and return to your worksheet, press Alt + Q. Now, try typing or pasting into the specified cells: only numbers will remain; any other input will be removed and a message displayed. If the macro does not work, ensure macros are enabled and the code is entered in the sheet-specific code window, not in a general module.
Tip: You can further customize the message or extend the logic for decimals, integers, or additional feedback as needed. If you have multiple target ranges on different sheets, remember to repeat the above steps in each relevant worksheet module.
Easily allow numbers only in a range of cells with an amazing tool
For users seeking a time-saving method without formulas or coding, Kutools for Excel offers the Prevent Typing utility, which can easily constrain entries to numbers and block unwanted characters in just a few clicks. This approach works especially well when applying restrictions to irregular ranges, mixed data, or when users are less familiar with Excel’s built-in validation settings. Kutools also provides clear prompts and additional options, making it user-friendly and efficient for both beginners and experienced users. Note, however, that this utility requires Kutools for Excel to be installed.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the range of cells for which you want to allow only number input. Then, on the ribbon, click Kutools > Prevent Typing > Prevent Typing.
2. In the Prevent Typing dialog, perform these steps:
- 2.1 Select the option Allow to type in these chars;
- 2.2 In the provided text box, enter 0123456789 (do not include spaces or punctuation). This limits input strictly to number keys 0–9.
- 2.3 Click the OK button to apply this restriction. See screenshot:
3. If the selected range already has existing data validation, a Kutools for Excel dialog box will appear asking whether to remove the current rules. Click Yes to proceed and remove the existing validations, or No to cancel the new setting. Confirming this step replaces older validations with the newly configured number-only rule.
4. A confirmation dialog box will pop up indicating the new restrictions are in effect and summarizing which numbers are allowed. Click OK to finish.
Now, the selected range will only accept the numbers 0 through 9 when typing. Any attempt to enter other characters—such as letters, symbols, or spaces—will be blocked in real time. Please note: This method only controls typed input, so pasting content that contains non-numeric characters may not be restricted.
Note: If you later need to disable or modify the restriction, return to Kutools > Prevent Typing and click Clear Restrictions for your selected 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.
Excel formula approach for numeric-only validation
Besides the above methods, you may sometimes want to highlight cells containing non-numeric entries for review rather than immediately blocking input. Conditional Formatting, coupled with Excel formulas, can visually indicate unwanted data entries, making this approach perfect for auditing, collaborative input, or pre-existing data clean-up scenarios. This solution is non-intrusive and does not block entries, but it draws attention to errors to facilitate manual correction.
1. Select the range where you want to identify non-numeric entries, for example, A2:A12.
2. Go to Home > Conditional Formatting > New Rule. In the dialog, choose Use a formula to determine which cells to format.
3. Enter the following formula into the box:
=NOT(ISNUMBER(A2))This formula returns TRUE for any cell that does not contain a number, triggering the chosen formatting.
4. Click Format, set a highlighting color or style, and then click OK to apply. Repeat for additional ranges as needed.
Cells with non-numeric input in your target range will now be easily identified. This method is ideal for identifying improper input without actively blocking user actions. Remember, this alternative is especially effective for reviewing existing sheets or as a gentle prompt for users in collaborative environments.
Tip: For more advanced checks, such as allowing only positive numbers or excluding decimals, you can modify the formula. For example, =AND(ISNUMBER(A2),A2=INT(A2),A2>0) will highlight all cells that are not positive integers.
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 format123-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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in