How to prevent duplicate entries in Google Sheets?

Preventing duplicate entries in Google Sheets is essential to maintaining clean and accurate data. By using the built-in Data Validation feature, you can easily restrict duplicates in a specific column. This guide will show you how to set up this feature and how to achieve similar results in Microsoft Excel using Kutools for Excel.

Prevent duplicate entries in Google Sheets with Data validation

Prevent duplicate entries in Microsoft Excel with Kutools for Excel

Prevent duplicate entries in Google Sheets with Data validation

Normally, the Data validation in Google Sheets can do you a favor, please do as this:

1. Select the cells where you want to prevent duplicates, then click Data > Data validation, see screenshot:

2. In the Data validation dialog box:

(1) Choose Custom formula is from the drop down list, and enter this formula: =countif(A$2:A2,A2)=1 into the Criteria text box;

(2) Then select Reject input option from the On invalid data section.

Note: In the above formula, A2 is the start cell of the column that you want to use.

3. Click Save. From now on, when duplicate data is entered in that specific column, a warning box will appear to alert you. See screenshot:

Prevent duplicate entries in Microsoft Excel with Kutools for Excel

In Excel worksheet, you can prevent duplicate entries by using a useful tool-Kutools for Excel. With its Prevent Duplicate feature, you can quickly finish this task in Excel.

1. Select the cells that you want to prevent duplicates, and then click Kutools > Prevent Typing > Prevent Duplicate, see screenshot:

2. A prompt box will appear to remind you that any Data Validation cells in your selected range will be removed when applying this feature. See screenshot:

3. Click Yes, and a prompt box will appear as shown in the following screenshot:

4. Click OK. Now, when you enter a duplicate value in the specified column, a warning box will appear. See screenshot: