How To Create Error Message Pop-Up Alert When User Input Wrong Data In Excel

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.

  1. Select the cells which you want to apply data validation.
  2. Click Data —> Data Validation —> Data Validation menu item.
    excel data - data validation - data validation menu item
  3. Then it will popup the Data Validation dialog.
  4. 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.
    excel - data validation - settings tab
  5. The Input Message tab let you define tip note when you input cell data.
    excel - data validation - input message tab
  6. 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.
    excel - data validation - input message tab effect
  7. The Error Alert tab allows you to define the alert dialog type, title and error message when the cell data is invalid.
    excel - data validation - error alert tab
  8. below is the result picture when you input a number which is invalid.
    excel - data validation - input invalid cell data error alert

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.

  1. Select all the cells that you want to validate values or you can press Ctrl + A to select the entire excel worksheet.
  2. Then you should define a cell data validation rule follow section 1.
  3. Click Data —> Data Validation —> Circle Invalid Data menu item to circle all the invalid cell data in a red circle like below.
    excel - data validation - circle all invalid data
  4. You can click Data —> Data Validation —> Clear Validation Circles menu item to clear all the red validation circles.

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.