Excel Data Validation Examples

This article will tell you how to add excel data validation rule to help you to validate the cell input data so that it can avoid inputting incorrect data into the related cell. The data validation rule can also be used to find out the invalid data cells that are not allowed by the validation rule.

1. How To Open Excel Data Validation Tool.

  1. Excel data validation tool can help you to create validation rules to validate user input data entry.
  2. After you create a data validation rule, it will show a tip message in a yellow dialog when the user selects a cell.
  3. And it will display an alert dialog when the user inputs incorrect data entry ( data that are not allowed by the data validation rule ) in excel cells.
  4. To use the Excel data validation tool, you should first select the targeted excel cells, then click the Data tab —> Data Validation icon ( located in the Data Tools group ) to open the Data Validation dialog window.
  5. Then you can add data validation rules to the cell in the Data Validation dialog window by selecting different settings.
  6. You can also click the button Clear All to clear the data validation rule from the cells.

2. How To Show Invalid Data In Excel Cells.

  1. Suppose you want to validate the existing cells data in the current excel worksheet.
  2. Then you select the cell region that contains all the cell data.
  3. And you create a data validation rule following the steps in section 1.
  4. If you want to use the validation rule to validate and show all the cell’s data that are not valid. You can click the Data tab —> Data Tools group —> the drop-down arrow beside the Data Validation icon —> Circle Invalid Data item.
  5. Then it will use a red circle to circle out all the invalid data cells.
  6. If you want to remove all the invalid red circles, you can click the Data tab —> Data Tools group —> the arrow beside the Data Validation item —> Clear Validation Circles menu item.

3. Excel Data Validation Examples.

  1. How To Validate Text Data In Excel.
  2. How To Allow Input Numbers Only In Excel Cell.
  3. How To Validate Date Input In Excel.

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.