WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android


Use Validation to prevent duplicate values

Uploaded time: March 17, 2022 Difficulty Beginner

Use Validation to prevent duplicate values

Use Validation to prevent duplicate values

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. 

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.