The Data validation helps stop duplicates from happening in the first place by preventing entry. See how not to allow duplicates in Google Sheets using it.
The Data validation ensures data quality by ‘forcing’ correct data entry in cells.
There are many built-in rules within, but nothing to prevent duplicate entries from happening in Google Sheets.
We can create data validation rules and insert them within the custom formula. We will follow it to prevent duplicate data entry in Google Sheets.
Features of My Custom Formula Rule
- The formula supports preventing duplicate entries in single, multiple columns, selected range, or an entire tab.
- It is a set-and-forget type formula. You may apply this formula in an entire range and forget about duplicates.
- When you or somebody access your Sheet and try to enter any value more than once, it will be rejected or flagged depending on the setting.
- If you want, allow duplicates ‘n’ times.
Example of how not to allow duplicates:
In the following example, I have used a custom formula rule for the range A1:G to prevent duplicates in one of my Google Sheets spreadsheets.
How to Prevent Duplicates in Google Sheets (Data Validation)
Let’s see how not to allow duplicates in Google Sheets by setting up the above formula.
I’ve set my formula for the range A1:G. You may either expand or reduce the number of columns and rows in the covered area by modifying it.
=COUNTIF($A$1:G, INDIRECT(ADDRESS(ROW(),COLUMN(),)))=1Note:- To understand the functions in the formula, please check Google Sheets Function Guide.
Steps (for the cell range A1:G):
- In a new Sheet, select the range A1:G or the range you want. If you select C1:C100, in the formula, replace
$A$1:Gwith$C$1:C100. - Go to the menu Data and select Data validation.
- Click on the “+ Add rule” button on the sidebar panel.
- Under Criteria, open the drop-down and choose “Custom formula is.”
- Copy-paste my above COUNTIF-based formula.
- Under “Advanced options,” select either “Show a warning” or “Reject the input,” depending on whether you want to flag or reject the duplicate entries outright.
- Click “Done.”
This way, we can deny duplicates (allow unique values) in selected columns in Google Sheets.
How to Allow Duplicates N Times in Google Sheets
For any reason, do you want to allow duplicates N times?
=OR(COUNTIF($A$1:G, INDIRECT(ADDRESS(ROW(),COLUMN(),)))=1,COUNTIF($A$1:G, INDIRECT(ADDRESS(ROW(),COLUMN(),)))<=2)This formula allows the duplicates two times.
If anyone tries to enter a value more than twice, Google Sheets may flag all three occurrences or prevent the third value depending on the settings.
Again the range is A1:G. If you want to allow two duplicates in column A, replace $A$1:G with $A$1:A.
How not to allow duplicates more than 5 times?
Just change the <=2 in the formula to <=5. That prevents duplicates of more than five occurrences.
If you have fewer columns, select the range accordingly and make that reflect in the formula too.
Must Read: Find and Remove Duplicates in Google Sheets: Different Options.
Hi, Prashanth,
I have an HTML form submitted to Google Sheets when it’s filled out.
But every time a new entry is created, it duplicates the entry in Google Sheets and sends two email notifications.
It should be one entry to Sheets and one email notification. Have you ever seen anything like this or possibly have a fix?
Hi, Chris,
Sorry! I’ve no idea about that.
It is possible to prevent duplicate values across all the sheets in a workbook instead of on just one sheet?
Hi, Aaran,
Right now, you can’t specify a workbook as a whole. But you can of course specify individual sheets in a workbook one by one.
This conditional formatting is an example:
How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.
Hi, I want
My Sheet1 C2:C get data validations and don’t make duplicates from another sheet range J1:J
Is that possible using data validation?
Hi, Guenahel,
I hope you can find an answer to your this data validation query in one of my earlier posts here – Reject a List of Items in Data Validation in Google Sheets.
Hi there. Is it possible to check for several ranges at the same time? Different sheets with similar information? If so, how do I do that? I can’t seem to “ctrl – Select” them all, nor comma it’s ranges.
Hi, Max,
I have conditional formatting to highlight duplicates across the tabs.
How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.
Are you looking for data validation to reject input depending on duplicates in multiple tabs?
If so, please elaborate on the problem.
Is it possible to check for a maximum number of occurrences as your formula allows AND the entered number to be less than a stated number?
For example, I want to allow a maximum number of duplicates to be 4 and the entered number to be between 1 and 20 (> 0 but < 21).
Hi, Tom,
Try this formula, please.
=and(existing formula,and(A1>0,A1<20))Best,
Does work if you copy and paste the data, rather than manually type it in.
If you fill the cells down, it won’t work either.
Hi, Leo,
That’s correct! The “reject input” may not have any effect. But the duplicates will get flagged anyhow.
The problem with data validation and conditional formatting are it get copied along with the values.
Thanks, it has worked. Now, will the formula apply to all data entered in column A? Again, how can I disable the main menu to allow the users just to enter data only without interfering with my settings?
Hi, Herbert,
It depends on the range that you have selected. I am talking about the “Cell range” in the data validation settings. You can refer my screenshot above where I have selected the range A1:G. For entire column A you can change that to A1:A.
I don’t know anything about disabling menu in Google Sheets. I suggest you to use the Protect Sheet feature that you can find under the Tools menu in Google Sheets. Only give access to the required cell range for editing. This probably prevents them from interfering with your settings.
Check this Google Sheets Documentation for more details.
https://support.google.com/docs/answer/1218656?co=GENIE.Platform%3DDesktop&hl=en