< Go Back

Data validation allow numbers only

★ 39 minute read

In Excel, you will definitely have numerical data for recording, in that case, if you want to keep a check on the data type of entries in the cell, you must enable data validation for it. To set data validation allow only numbers only in cell data validation settings need to be acting accordingly.

Excel Data Validation function restricts the type of data that is into a cell. Any cell can have a format to receive an input entry of some specific type. For example, if it is required that only numbers are permissible in the cell, then data validation setting can be adjusted to allow numbers only.

Data validation allow numbers only

Formula

=ISNUMBER(range)

Explanation

Microsoft Excel has many built-in data validation rules for numbers. This article explains that how to set the data validation to allow numbers only, in the cell. To allow only the numbers in a cell, you can use the data validation option by setting it to the custom option based on providing the ISNUMBER function for that specific cell.

The ISNUMBER formula requires only one attribute:
Range(required): The range of cells for which you want to apply criteria.

In the example below, data validation is active in some cells B2:B7.

Example 1

In this example, some hypothetical products and their costs are entered in an Excel spreadsheet. Now it is required by the user that the cost column only accepts values which are numbers. In that case, data validation rules must be active for these cells. Data validation check is running, while the user adds or changes a value of that cell.

To set the data validation to allow numbers only you should follow the following steps.
First of all, select the cells or range of the cells to which data validation to allow only numbers is required.

 

 

Figure 1. Selection of data for applying rules of data validation

After that click on the Data button in the menu bar. From the pop-down menu, go to the Data validation settings as shown below.

Figure 2. Opening the Data Validation pop-up window

When you click on the Data validation option, a pop-up window will appear. In that window, go to the setting tab and select the option “Custom” from the drop-down menu available for “Allow” option. As shown below:


Figure 3. Setting the Data validation to custom

In order to set the data validation to allow only numbers in the cells, use the formula:

=ISNUMBER(B2:B7)

in the “Formula” option.

 

Figure 4. Setting the Data validation to allow numbers only

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar