Sometimes we don't want to enter duplicate information from the collected data. We will take the unique telephone number as an example to show how to use the Validation function to prevent duplicate values.

Select this column, and click Validation at Data, you can also use the shortcut Alt+D+L. Head to the Setting tab, click the Allow drop-down button and select Custom, and enter countif(D:D,D1)=1.

This formula will make the value in cell D1 only appears once in column D; otherwise, it suggests duplicate values appear.

After setting, click OK.  At this time, if we enter duplicate entries in column D, an Error Input will pop up to limit the input.

How can we cancel the restriction? Select the data area, press Alt+D+L to open Data Validation, where you can click the Clear all button to remove the rules.

By doing so, the Error Input will no longer appear.

This is all about using Validation to prevent duplicate values. Did you get it?

Excel is the most powerful tool to manage and analyze various types of Data. This free WPS Spreadsheet tutorial for beginners covers in-depth lessons for Excel learning and how to use various Excel formulas, tables and charts for managing small to large scale business process.