Validating data

Surprisingly, none of the inbuilt Excel data validation rules cater for a very typical situation when you need to restrict users to entering only numbers in specific cells.

But this can be easily done with a custom data validation formula based on the ISNUMBER function, like this one: Where D2 is the uppermost cell of the selected range.

In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets.

Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation in Excel as well as experiment with a handful of different validation formulas.

On another sheet in the workbook, you'll create a list of valid dates.

In this example, the sheet with the list is named Admin Lists.

To limit the entry to a date within a specified range, you can use either the predefined Date rule with the "between" criteria or make a custom validation rule with this generic formula: Please notice that the boundary dates are locked with absolute cell references.

To restrict a user to entering only weekdays or weekends, configure a custom validation rule based on the WEEKDAY function.

In this example, employees will fill in a Vacation Request Form for the year 2017.Copied data is not validated, nor is the data input in the cell before making the rule.To pin down existing entries that do not meet your data validation criteria, use the Circle Invalid Data feature as shown in How to find invalid data in Excel.If the same value already exists in the specified range (count greater than 1), COUNTIF returns FALSE and the input fails validation.Please pay attention that we lock the range with absolute cell references (A:$A) and use a relative reference for the top cell (A2) to get the formula to adjust properly for each cell in the validated range.

Leave a Reply