Conditional Data Entry in Excel Using Data Validation
Here are a few examples of conditional data entry rules:
Allow data entry from a pre-defined list only (using drop-down lists). Allow data entry only when the specified cell(s) are filled. Allow DATE entry between two specified dates only.
You can also combine multiple conditions to create a data entry rule. This type of conditional data entry in excel can be done using the data validation feature in Excel. It can enable data entry in the specified cells only when the specified conditions are met, else it shows an error.
Allow Data Entry from a Pre-defined List
You can restrict the user to choose from a list by creating a drop-down list. For example, suppose you have a list of countries as shown below, and you want to allow the entry of only one of these names in cell C1:
You can create a drop-down list that will restrict the entries to only the ones mentioned in the list. If you try to enter any other text string, it will show an error (as shown below):
Here is how you can create a drop-down list:
Select the cell where you want to show the drop down list. In this example, it is cells C1. Go to Data –> Data Tools –> Data Validation. In the data validation dialogue box, select the settings tab and make the following changes: Allow: List Source: $A$1:$A$6 (you can use the range where you have the data). Ignore Blank: Checked (uncheck this if you don’t want the user to enter blank). In-cell dropdown: Checked (this would enable the drop down feature).
This will create a drop-down list in the selected cell. Now you can either select them from the drop-down list, or manually enter the data in it. If you enter any data that is not from the source data, it will show an error. CAUTION: If you copy and paste over the cell which has the data validation rules, the data validation rules disappear. Download the Example File
Data Entry when a Dependent Cell is Filled
This could be the case when you want the user to go in a sequence and complete filling a form/questionnaire/survey. Let’s say I have a something as shown below:
In this data set, I want the user to first fill the name (first name and last name is mandatory) and then move on to fill the date. If the user skips entering the name, then I want to show an error (as shown below):
This can easily be done using data validation. To do this:
Select the cell where you want to apply this condition. In the above example, it is cell B5. Go to Data –> Data Tools –> Data Validation In the data validation dialogue box, select the settings tab and make the following changes: Allow: Custom Formula: =AND($B$1<>””,$B$3<>””). Ignore Blank: Unchecked (make sure this is unchecked else it will not work).
Allow: Custom Formula: =AND($B$1<>””,$B$3<>””). Ignore Blank: Unchecked (make sure this is unchecked else it will not work).
In this case, we have used an AND function that checks whether both B1 and B3 have already been filled. If not, then it shows an error. CAUTION: If you copy and paste over the cell which has the data validation rules, the data validation rules disappear.
Date Entry Between Two Specified Dates
There is an inbuilt feature in data validation that will let you do this. You can specify the upper and lower date limits, and if the user enters a date which is outside of this range, he/she will get an error. To do this:
Select the cell where you want to apply this condition. In the above example, it is cell B5. Go to Data –> Data Tools –> Data Validation In the data validation dialogue box, select the settings tab and make the following changes: Allow: Date Data: Between Start Date: Enter the start date here (any date that is before this date will not be accepted). End Date: Enter the end date here (any date that is after this date will not be accepted).
You can also use a cell reference or a formula to specify the date. For example, you can use TODAY() function as one of the date limits (if you want the lower limit to the current date).
Allow: Date Data: Between Start Date: Enter the start date here (any date that is before this date will not be accepted). End Date: Enter the end date here (any date that is after this date will not be accepted).
Since Excel stores the dates as numbers, you can also use numbers instead of dates. For example, instead of using 01-01-2015, you can also use the number 42005. CAUTION: If you copy and paste over the cell which has the data validation rules, the data validation rules disappear. Download the Example File You can combine multiple conditions as well. For example, let’s say you want to enter a date in cell B5 with the following conditions:
First Name and the Last have already been filled by the user. The entered date is between 01-01-2015 and 10-10-2015.
To do this:
Select the cell where you want to apply this condition. In the above example, it is cell B5. Go to Data –> Data Tools –> Data Validation In the data validation dialogue box, select the settings tab and make the following changes: Allow: Custom Formula: =AND($B$1<>””,$B$3<>””,B5>=DATE(2015,10,1),B5<=DATE(2015,10,10)) Ignore Blank: Unchecked (make sure this is unchecked else it will not work)
This formula checks for four conditions – whether the two cells (B1 and B3 are already filled, and whether the date entered in cell B5 is within the specified date range). CAUTION: If you copy and paste over the cell which has the data validation rules, the data validation rules disappear. Download the Example File Similarly, you can create and test for multiple conditions while allowing data entry in Excel.
Excel Data Entry Form. Using Drop-down Lists in Excel. 100+ Excel Interview Questions and Answers. How to Remove Drop-Down List in Excel?
Thank you for any help.
