Restrictinged Values through validating text entries in Microsoft ExcelValidating Number Entries in Microsoft Excel 2010

 

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

img1

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

img3

  • Select column B, go to the menu and select the “Data” tab. Inthe “Data Tools” group select “Data Validation”. The dialog box will appear.

img4

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

img5

  • Select

    the “Error Alert” tab and type the message in the “Error Message” asOnly Numbers Allow and click on ok.

img7

img8

  • When you enter any text in Column B, it will give the “Error Message”.

img9

img10

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.

img11

img12

This is how we can validate the number entriesin Microsoft Excel 2010 and 2013.



Example:


Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>