Quick Start: Apply data validation

You use data validation to control the type of data or the values that users enter into a cell. For example, you can use data validation to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered.

Watch the video


Icon image

Select one or more cells to validate, and then on the Data tab, in the Data Tools group, click Data Validation.

Data Tools group on the Data tab

Issue: The Data Validation command is unavailable.

A Microsoft Excel table might be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

You might currently be entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering data, press ENTER or ESC.

The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.

Icon image

In the Data Validation dialog box, click the Settings tab, and then select the type of data validation that you want.

For example, if you want users to enter a 5-digit account number, in the Allow box, select Text Length, in the Data box select equal to, and in the Length box type 5.

data validation dialog box

Icon image

Do one or both of the following:

  • To display an input message when the cell is clicked, click the Input Message tab, click the Show input message when cell is selected check box, and then enter the input message options that you want.

  • To specify a response when users enter invalid data in the cell, click the Error Alert tab, click the Show error alert after invalid data is entered check box, and then enter the alert options that you want.

Top of Page

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!