Sometimes you may want to verify user input data type or value in excel cell. And excel just provide such a feature for you to customize the cell data validation process. You can define which data type and what data values are valid for one or multiple excel cells. If the data is not valid, you can define the error message which will pop up to the user. This article will show you how to implement it.
1. Create Custom Excel Cell Data Validation Alert Dialog Steps.
- Select the cells that you want to apply data validation to.
- Click Data —> Data Validation —> Data Validation menu item.
- Then it will popup the Data Validation dialog.
- In the Data Validation dialog, click the Settings tab. The Allow drop-down list lets you select the cell data type. The Data drop-down list lets you select the data relation. The Minimum and Maximum input box let you input the cell data minimum and maximum‘s value. You can check the Apply these changes to all other cells with the same settings checkbox if you want to apply the settings change to all the cells that have already configured with these validation settings.
- The Data Validation dialog Input Message tab lets you define tip notes when you input cell data. You should check the Show input message when cell is selected checkbox, and input the title text ( Cell Data Tips ) in the Title text box, and input the text Input a number which between 1 – 100 in the Input message text box.
- If you check the Show input message when cell is selected checkbox, then it will show a yellow tips note beside the cell when you select the cell to input data.
- The Data Validation dialog Error Alert tab allows you to define the alert dialog type, title, and error message when the cell data is invalid. You should check the Show error alert after invalid data is entered checkbox, and select the style ( for example Stop ) from the Style dropdown list, and input the Title and Error message in the related text box.
- Below is the resulting picture when you input a number that is invalid, it will show a tip note when you focus on the excel cell, when the entered number is not valid, it will popup an error alert dialog.
2. How To Find All Excel Cells That Cell Data Is Invalid.
If there is an excel worksheet, and you want to find all the cells that cell data is invalid by some rules, you can follow the below steps.
- Select all the cells that you want to validate values or you can press
Ctrl + Ato select the entire excel worksheet.
- Then you should define a cell data validation rule follow section 1.
- Click the Data —> Data Validation —> Circle Invalid Data menu item to circle all the invalid cell data in a red circle.
- You can click Data —> Data Validation —> Clear Validation Circles menu item to clear all the red validation circles.