In this article we will learn about to how to learn about the validate number of entries in Microsoft Excel 2010.
Data Validation is used to make the data error free in Microsoft Excel 2010/2013. It is a technique by which we can control the input by providing adrop down list to the user.
To validate the entries we use the Data Validation option. Through this option we can validate our data and give instructions to users.
Let’s understand how we can validate number entries.
To validate the number entries we have to use the ISNUMBER formula.
ISNUMBER: – In Microsoft Excel, “ISNUMBER” function is used to check if the value in the cell contains number or not.
Syntax of “ISNUMBER” function: =ISNUMBER (value)
Example:Cell A2 contains the number456
=ISNUMBER (A2), function will return true
What is the use of the Input Message?
Input messages are generally used to givedirections to the users regarding the type of input which can be inserted in a cell. This input message is shown near the cell, which can also be moved to another cell if required..
Let’s take an example and understand how we can validate the entries. We have datain the range A2: B10, and we need to restrict the entry in column B except number entries.
- Select column B, go to the menu and select the “Data” tab. Inthe “Data Tools” group select “Data Validation”. The dialog box will appear.
- In the “Settings” tab select “Custom” from the “Allow” drop down list the formula tab will get activated.
- Write the “ISNUMBER” function in the “Formula” box, =ISNUMBER (B1:B31).
the “Error Alert” tab and type the message in the “Error Message” asOnly Numbers Allow and click on ok.
- When you enter any text in Column B, it will give the “Error Message”.
To remove the restrictions from the data follow the below mentioned steps:-
- Go to the “Data Tab” and click on “Data Validation” in the “Data Tools” group.
- To eliminate the rule, choose “Any Value” from the Allow drop down.
Go to the Error Alert tab and uncheck the box that says “Show error alert after invalid data is entered”.
- The above method can be used when we have to clear just one data validation. However, if you have multiple data validations which have been applied in the worksheet, this method is time-consuming. So, you can click on the Clear All button to clear all the data validations which have been applied.
This is how we can validate the number entriesin Microsoft Excel 2010 and 2013.