Create an Input Message (Data Validation) in Excel & Google Sheets

In this tutorial, you will learn how to create an input message using data validation settings in Excel and Google Sheets.

 

 

Create an Input Message

In Excel, you can create an input message using Data Validation to validate users’ input in a cell. This can be useful for restricting the user’s entry to only numbers, characters, or some other criteria. Let’s use the following data table to demonstrate how to create data validation rules and input messages.

 

 

In the table, all data is entered except 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, only numeric values should be allowed in Sales amount (Column E). Here are the steps to follow:

1. First, select the cell where you want to create an input message (e.g., E3). Then, in the Ribbon, go to Data > Data Validation and click on Data Validation…

 

 

2. In the Data Validation screen, go to the Input Message tab. Here, enter the Title of the input message, and the Input Message text.

 

 

3. Next, go to the Settings tab. In the Allow drop-down list, choose Whole number to allow only whole numbers to be entered. (Other options include Decimal, Text length, List, Date, and Time.)

4. Set Minimum and Maximum limits. In this case, use 1–1,000,000. (All Sales values must be whole numbers no greater than 1 million.) Click OK.

 

 

5. Finally, go to the Error Alert tab and set the Style. In this case, choose Stop, which means that Excel won’t allow anything other than whole numbers to be entered.

Other options are…

• Warning. If invalid data is entered, Excel will raise a warning message and ask if you want to enter that data.
• Information. If invalid data is entered, only the information message is displayed, and entry is allowed.

6. Now, on the right side, enter the Title of the message and the Error message text.

 

 

Now, clicking on cell E3 gives you an input message (Enter Sales Amount), which you defined in Data Validation. If you enter the number 5 here, the data validation rule is met and the corresponding Total Sales value is calculated in cell G3.

 

 

Let’s try to enter some text in cell E3 instead of a number. In this case, an error message appears, and Data Validation will reject the input unless you enter a whole number.

 

 

Copy the Input Message to the Entire Column

Now, copy this data validation to all cells in the Sales amount column (E3:E16).

1. First, position the cursor in the bottom right corner of cell E3 until the fill handle appears.

 

 

2. Now click and hold the fill handle, and drag it down to cell E16.

 

 

The data validation settings are copied from cell E3 to the range E3:E16; if you select any cell in this range, we’ll get an input message like the one in cell E3.

Create an Input Message in Google Sheets

We’ll use the same example to explain how to create an input message using data validation in Google Sheets.

1. First, select the cell where you want to create an input message (E3). Then in the Menu, select the Data tab and choose Data Validation.

 

 

2. In the Data validation screen, first choose Number as the Criteria and set limits (between 1 and 1 million).
3. Choose if you want to Show a warning or Reject the input.
4. Finally, you have an option to Show validation help text and enter your input message text. Then press Save.

 

 

As a result, an input message is created in cell E3 and works the same as in Excel. You can copy this data validation down the column following the instructions for Excel.