Apply data validation to cells

If you're creating a sheet that requires users to enter data, you might want to restrict entry to a certain range of dates or numbers, or make sure that only positive whole numbers are entered. Excel can restrict data entry to certain cells by using data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to create messages that prompt users for correct entries and notify users about incorrect entries.), prompt users to enter valid data when a cell is selected, and display an error message when a user enters invalid data.

Do any of the following:

ShowRestrict data entry

  1. Select the cells that you want to restrict data entry in.
  2. On the Data tab, under Tools, click Validate.

Data tab, Tools group

 Note   If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.

  1. On the Allow pop-up menu, select the type of data you want to allow.
  2. On the Data pop-up menu, select the type of limiting criteria that you want, and then enter limiting values.

 Note   The boxes where you enter limiting values will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.

ShowPrompt users for valid entries

When users click in a cell that has data entry requirements, you can display a message that explains what data is valid.

  1. Select the cells where you want to prompt users for valid data entries.
  2. On the Data tab, under Tools, click Validate.

Data tab, Tools group

 Note   If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.

  1. On the Input Message tab, select the Show input message when cell is selected check box.
  2. In the Title box, type a title for your message.
  3. In the Input message box, type the message that you want to display.

ShowDisplay an error message when invalid data is entered

If you have data restrictions in place and a user enters invalid data into a cell, you can display a message that explains the error.

  1. Select the cells where you want to display your error message.
  2. On the Data tab, under Tools, click Validate.

Data tab, Tools group

 Note   If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.

  1. On the Error Alert tab, in the Title box, type a title for your message.
  2. In the Error message box, type the message that you want to display if invalid data is entered.
  3. Do one of the following:
To On the Style pop-up menu, select
Require users to fix the error before proceeding Stop
Warn users that data is invalid, and require them to select Yes or No to indicate if they want to continue Warning
Warn users that data is invalid, but allow them to proceed after dismissing the warning message Important

See also

Create a drop-down list to enter data

 
 
Applies to:
Excel for Mac 2011