Highlight the cells
Data Validation can be used to restrict the entry of data within a column.
This example shows you how to prevent duplicate entries (both text and numbers) from being entered into a column.
Note that this technique is not restricted to just columns but can also be used with rows and cell ranges (see step 6).
Find the correct formula
You can use worksheet functions in the formulas given for the data validation.
In this example we will use the COUNTIF function.
This function returns the number of non blank cells with a value that satisfies a condition.
We can use this function to tell us if an entry already exists in a particular range.
(we include the relative reference B1 since we have included the whole column in the formula)
Activate the Data Validation dialog box
Highlight the cells you want to apply the data validation to. In this case the whole of column B.
Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box, select "Custom" (or List).
Is there any difference if you entered "=1", "<=1" or "<2".
Enter a duplicate value
Type the following data onto a worksheet and try and type a duplicate entry into column B.
In this example we have tried to enter the text "France" which already exists.
The following error message will appear and the text will be removed from the cell.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext