Data validation in Excel is a way to ensure that an entry is being made in a cell complies with the format desired. To apply data validation to a cell in Excel, go to the ‘Data’ tab and click on ‘Data Validation’.
A new window will open which will allow you to set the data field as desired.
Now, let’s go step by step to understand the each of the categories under data validation in Excel.
#1: Any Value
As the name states, this cell would allow any value whether text, numbers, decimals, date, time, etc. If you select ‘Any Value’ as data validation, it is as good as applying no restrictions tothe cell in connection with the value or data entered.
#2: Whole Number
Selecting whole number for data validation would restrict the cell to containing only whole numbers. When choosing whole numbers for data validation, you would also have to define an accompanying condition which could be a number lesser than a certain value or greater than a certain value or between or not between certain values.
There’s a whole range of options that you can choose from according to the use case. A good example would be adding this to age, let’s say if you have an age requirement of 18+. You can define cell restrictions as greater than 18.
In the same window, next to ‘Settings’, you will also see two other options ‘Input Message’ and ‘Error Alert’. Under input message, type “Enter age in years”. This input message will appear when a user hovers over the cell that has this validation in.
Under ‘Error Alert’, type “Age restriction: 18+”. This alert shows a reason to the user as to why the data validation failed if any value under 18 is entered.
#3: Decimal
Very similar to above, the decimal can be used for data validation for use cases where greater precision is required. It is mandatory to enter a condition for the data in these cells. Similar to above, it is a good idea to define Input Message and Error Alert to let the user know what values are expected in these fields and in what form.
#4: List
The most widely used in drop-down menus is the ‘List’ data validation type. Since drop-down menus are an essential part of most forms, knowing how to correctly use this kind of data validation comes in very handy.
To use this kind of data validation in Excel, you need to have a list of options that you want to provide in the drop-down menu. This list could be in the same sheet or in a different sheet in the same workbook.
Once your list is ready, select ‘List’ under ‘Allow’ in data validation and then click on the ‘Source’ field. Then go to the list that you made and highlight all cells on that list. You will see the cell references for your list in the Source field.
Check the ‘In-cell dropdown’ to display these values as a list in the cell where data validation is being applied.
Verify all information and click ‘OK’. Now go back to the cell that you added this data validation to, you should be able to see that list drop down and all your options displayed.
#5: Date & Time
Data validation can be applied for dates and times, similar to whole numbers and decimals above. It is mandatory to define boundaries, as in between certain dates before or after certain date. The same applies to time-based data validation.
#6: Text Length
When working with forms, you must have come across a scenario where it says that you can only enter a certain number of characters in a field. That is a kind of data validation that text length offers. You can define the number of characters in a certain cell using this type of data validation.
#7: Custom
The custom field in Excel data validation is used to define a formula. For instance, you can define if you want a number or text only in those fields.
To define a number, the formula would be =ISNUMBER(A1)
Similarly for text, the formula would be =ISNUMBER(A1)
Another use case of this custom field is if you have certain minimum and maximum values that can keep changing. You can define a formula with those maximum and minimum cell references. This way you do not define a constant value for the maximum and minimum but the formula flows in with the data and keeps the data validation parameters up to date at all times.
To define a formula for this use case, let’s say that you have a minimum value that is going to be in cell F1 and the maximum value is going to be in cell F2. Below is the formula:
=AND(A1>=$F$1, A1<=$F$2)
When defining $F$1 and $F$2 in the formula, we are tying these cell references. If any changes are made to the sheet like adding in rows or columns, the minimum and maximum data references may change. It is thus a better idea to tie any such parameters to a name and then use those name references in the formula so that the formula doesn’t break with changes to the sheet.
Let’s say, we name cell F1 as ‘Min’ and cell F2 as ‘Max’. The above formula would be as below:
=AND(A1>=Min, A1<=Max)
Conclusion
Data validation is a very useful feature in Excel as it saves a lot of time by enforcing data rules, especially when working with large data sets. Now that you know the correct use case of each of the data validation options, you can practice each use case and play around to see how formulas are defined and how error messages pop up when outside of range data is entered.
To enhance your skills in Excel, check out some more interesting reads here!
