Macros for validating data in excel
Here is an example in the simplest form: When setting up a formula-based Excel validation rule, please keep in mind that all cell references in your formula are relative to the upper left cell in the selected range.
In situations when a certain column or a range of cell should not contain any duplicates, configure a custom data validation rule to allow only unique entries.For this, we are going to use the classic COUNTIF formula to identify duplicates: When a unique value is entered, the formula returns TRUE and the validation succeeds.If the same value already exists in the specified range (count greater than 1), COUNTIF returns FALSE and the input fails validation.Data Validation Example | Create Data Validation Rule | Input Message | Error Alert | Data Validation Result Use data validation in Excel to make sure that users enter certain values into a cell. On the Data tab, in the Data Tools group, click Data Validation. In this example, we restrict users to enter a whole number between 0 and 10.