Some times you may want to verify user input data type or value in excel cell. And excel just provide such feature for you to customize the cell data validation process. You can define which data type and what data value are valid for one or multiple excel cell. If the data is not valid, you can define the error message which will popup 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 which you want to apply data validation.
- Click Data —> Data Validation —> Data Validation menu item.
- Then it will popup the Data Validation dialog.
- Click the Settings tab. The Allow drop down list let you select cell data type. The Data drop down list let 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 apply the settings change to all the cells that has already configured with these validation settings.
- The Input Message tab let you define tip note when you input cell data.
- If you check 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 Error Alert tab allows you to define the alert dialog type, title and error message when the cell data is invalid.
- below is the result picture when you input a number which is invalid.
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 fules, you can follow 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 Data —> Data Validation —> Circle Invalid Data menu item to circle all the invalid cell data in a red circle like below.
- You can click Data —> Data Validation —> Clear Validation Circles menu item to clear all the red validation circles.