See all How-To Articles

Enable Error Alert (Data Validation) in Excel & Google Sheets

This tutorial demonstrates how to enable a data validation error alert in Excel and Google Sheets.

 

enable error alert data validation final

 

Enable Error Alert

In Excel, you can add an error alert with a message using Data Validation to check users’ input in a cell. This can be useful when you want to restrict users so they can only enter numbers, characters, or some other criteria. Let’s use the following data table to demonstrate how to create data validation rules and error alerts.

 

enable error alert initial data

 

In the table, all data is entered, except for the sales amount, which needs to be entered by a user. When Sales amount is entered, Total Sales is calculated as the product of Sales amount and Price. Therefore, you need to allow only numeric values for Sales (Column E).

  1. First select the range where you want to create an input message (in this example E3:E15). Then, in the Ribbon, go to the Data > Data Validation.

 

enable error alert data validation

 

  1. In the Data Validation window, in the Settings tab, choose Whole number in the Allow drop-down menu, set Minimum and Maximum number allowed (in this example 1 and 1,000,000), and click OK.

Other options include Decimal, Text length, List, Date, and Time.

 

enable error alert data validation 1

 

  1. Now (1) go to the Error Alert tab, (2) check Show error alert after invalid data is entered, (3) enter message Title (Error), (4) type the Error message (You must enter a whole number between 1 and 1,000,000), and (5) click OK.

 

enable error alert data validation 2a

 

Now let’s go back to the sheet and try to enter a non-numeric value in some of the cells in the data range (for example, E2).

 

enable error alert data validation final

 

As you can see, you can’t enter mouse in cell E2, because it’s not a whole number between 1 and 1,000,000. Therefore, the error message you created in the data validation, appears.

Enable Error Alert in Google Sheets

Similar to Excel, you can create data validation in Google Sheets that will raise an error alert in case that a wrong value is entered.

  1. Select the range where you want to create an input message (in this example E3:E15). Then, in the menu, go to the Data > Data validation.

 

google sheets data validation

 

  1. In the Data validation window, (1) choose Number in Criteria drop-down, and (2) set number range (e.g., between 1 and 1,000,000). (3) Check Reject input (On invalid data), also (4) check Show validation help text, (5) enter a text for error message (Enter a number between 1 and 1,000,000), and (6) click Save.

 

google sheets data validation 2

 

As a result, if you try to enter a non-numeric value (or number not between 1 and 1,000,000), the error will raise.

 

google sheets data validation 3