Enable Error Alert (Data Validation) in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to enable a data validation error alert in Excel and Google Sheets.
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.
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).
- 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.
- 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.
- 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.
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).
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.
- 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.
- 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.
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.
