How to Use Data Validation | Microsoft Excel

How to Use Data Validation | Microsoft Excel

What is use of data validation?

Data Validation is a process in which you can arrange input as per required. Data validation gives you a flexibility and precise data management. Data validation restrict the input data or value that you apply to the cell.

Data Validation


For Example, In the column of the employee number the data validation condition is a number between 1 to 9999. If you write other than the range 0001 to 9999 then it will give you an error message. You can also specify the error message and input message also.

Importance of Data Validation

  • Restricted Entries: You can give the selection from the list which allows listed items only. For Example, Departments of the organization list like (Project, HR, Management etc)
  • Number Restriction:  It will give you a specific range for input. For Example, Employee number should be between 0001 and 9999. If a number is not between the range it will show you an error message.
  • Restrict date and time: You can restrict the date. For Example, Employee birth date should be in the range of 1-1-81 to 1-1-85. For time. For time validation you can restrict the time of lunch for every employee.
  • Text character limit: you can restrict the number of latter in the name section to 10 or less/more as per your requirements
  • Validations through value and formulas: you can set an incentive for an employee as per his work. It will restrict the maximum/minimum value of the incentive.

Input message in Data Validation

Input message is a message when you point your cursor at the cell which Is restricted to the specified limit. It will show the limit and importance of the data.
Input Message window has title and input message box so you can show user to which data to enter n the cell. For Example, Employee number should be between the range of 1 to 9999 then input message will show enter data between 1 to 9999 only.

Error message is a message when you have entered wrong data in the restricted cell. There are three style of error message
    Stop: It will prevent you to enter wrong data. It will show you retry and cancel message.
    Warning : It will warn you that you are entering invalid data but it can be accepted. Warning        message show Yes(Accept invalid data), NO(Do not accept invalid data), cancel(cancel the entry)
    Information: The information message is flexible it will allow you to enter the invalid data and gives information about the entered data. It will show option OK(Accept), cancel(Reject)


Importance of Data Validation

If you have already data and you apply data validation, Data validation will not show invalid data at that time. You should use circle invalid data tab (Data>Data Validation>Circle Invalid data). Which highlight invalid data with a circle? You can hide invalid data or remove circle by using clear validation circle (Data>Data Validation>Clear Validation Circle) and correct it. When data is corrected the circle will be omitted automatically.

If you want to protect your worksheet or workbook, you must complete your data validation before apply protection.

If you use drop-down list and the data is wider then cell then you should increase the width of the cell to a better view of data

To remove Data Validation Data>Data Validation>Clear All

To find where the data validation is applied Home>Editing group>Find & Select>Data Validation. After finding data validation area you can copy, edit and remove the settings

If you make any changes to data validation cell and you want these changes to apply for all your data then select the checkbox in a setting window saying apply these changes to all other cells with the same setting.

Leave a Reply

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