How To Allow Input Numbers Only In Excel Cell

This article will show you some examples of how to allow input numbers only in excel cells.

1. How To Use Data Validation Tool To Allow Input Numbers In Number Range Only.

  1. This example will use the excel Data Validation tool to allow input numbers in a number range.
  2. Select a cell range to which you want to apply the data validation rule.
  3. Then open the Data Validation dialog window by clicking the Excel Data tab —> Data Validation item in the Data Tool group.
  4. Click the Settings tab, and select the Whole number or Decimal item from the Allow drop-down list.
  5. Select the between item from the Data drop-down list.
  6. Input the number 100 in the Minimum input text box.
  7. Input the number 200 in the Maximum input text box.
  8. Then click the Input Message tab in the Data Validation dialog window.
  9. Check the checkbox Show input message when cell is selected, then when you select a cell in the cell range, the message inputted in this tab will be shown.
  10. Input the text Input Number in the Title text box, and input text Input a number between 100 and 200. in the Input message text area.
  11. Click the Error Alert tab, and check the checkbox Show error alert after invalid data is entered.
  12. Select the Stop item from the Style drop-down list. There are 2 other style items that are Warning, and Information.
  13. Input the text Incorrect Number in the Title text box, and input the text The input number is out of rangeĀ in the Error message text area, and click the OK button to create the rule.
  14. Now when you select a cell in the cell range, it will display a yellow tip dialog beside the cell, the tip dialog title and message are just the title and message you input in the Data Validation dialog Input Message tab.
  15. When you input a number that is not between 100 and 200, it will show an error dialog with the title and message that you input in the Error Alert tab of the Data Validation dialog window.
  16. For example, when you input the number 299 in a cell, it will display an alert dialog with the title Incorrect Number and the message The input number is out of range.

2. How To Use Formula To Allow Input Number Value Only In Excel Cell.

  1. Besides the built-in data validation operator, you can use the excel formula to create a custom validation rule to allow input number value in the excel cell only.
  2. Open the excel Data Validation dialog window.
  3. Click the Settings tab, and select the Custom item from the Allow drop-down list.
  4. Then input the formula =ISNUMBER(A1) in the Formula input text box.
  5. You can input the title and message in the Input Message tab and Error Alert tab.
  6. Click the OK button to create the data validation rule.
  7. Now when you input a none number value in cell A1, it will pop up an alert dialog with the error message that you enter.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.