How to Enable Conditional Data Entry in Excel using Data Validation

How to Enable Conditional Data Entry in Excel using Data Validation

Sometimes when you are creating worksheets with data inputs, you may want to restrict the data input format (i.e. numbers, decimals, date, text etc.) and their range. This is very user friendly because it doesn’t allow you to input invalid data.

For this select the cell and go to Data tab > Data Validation

In the dialog box, there are three tabs Settings, Input Message and Error Alert change the settings as you per your requirement.

Settings:  

I have entered Date as the valid data format. By enabling Ignore blank, the cell can be left blank. The date range can also be set, which I have set the valid date to be greater than 1/1/1930.

Input Message

This is used to show an input message when the cell is selected. You have to first enable this option by marking the tick at the top of the dialog box. Then you can enter a Title and Input message. A sample is shown in the snap shot below.

The preview will be displayed as follows:

Error Alert

This option is used to show error alert after invalid data is entered. You can type the message title and error message in the given spaces.

If you enter invalid data you will get an error message and the error message is shown below.

Note:

  • To remove data validation from a cell, select the cell > go to Data tab, click Data Validation, and then click Clear All.

  •  You also can use Go To Special tool to quickly select all cells with data validation.