How to restrict data entry in google spreadsheet cell

Mastering Multiple-Selection Drop-Down Chips in Google Sheets

Multiple-Selection Dependent Drop-Downs in Google Sheets

Create a Dynamic Fibonacci Sequence in Google Sheets

How to Generate Random Groups in Google Sheets

Automatically Pre-fill Google Forms from Google Sheets: A Step-by-Step Guide

How to Set Up Google Docs Forms: A Comprehensive Guide

How to Insert Drop-downs in Google Docs Documents

How to Create a Table and Pin and Unpin Header Rows…

How to Use Section Break in Google Docs

How to Split a Table in Google Docs

How to Create First Line Indent and Hanging Indent in Google…

How to Duplicate Rows Dynamically Based on Cell Values in Excel

Unique List by Section in Excel

REDUCE Function in Excel: Transform Arrays with Ease

Insert a Blank Row After Each Category Change in Excel

Excel Formula to Extract All Rows Between Two Texts in a…

Days Between Weekday Names in Excel and Google Sheets

Display Month Names Only at Month Start (Excel & Google Sheets)

SUMIFS with XLOOKUP in Excel and Google Sheets

Counting XLOOKUP Results with COUNTIFS in Excel and Google Sheets

SORT and SORTBY – Excel Vs Google Sheets

How Not to Allow Duplicates in Google Sheets (Data Validation)

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

1. The formula supports preventing duplicate entries in single, multiple columns, selected range, or an entire tab.
2. It is a set-and-forget type formula. You may apply this formula in an entire range and forget about duplicates.
3. 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.
4. 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(),)))=1

Note:- To understand the functions in the formula, please check Google Sheets Function Guide.

Steps (for the cell range A1:G):

1. 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:G with $C$1:C100 .
2. Go to the menu Data and select Data validation.
3. Click on the “+ Add rule” button on the sidebar panel.
4. Under Criteria, open the drop-down and choose “Custom formula is.”
5. Copy-paste my above COUNTIF-based formula.
6. 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.
7. 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?

If you have fewer columns, select the range accordingly and make that reflect in the formula too.

Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.