How to prevent duplicate values in a column in Excel?
When working with data in Excel, preventing duplicate entries in a column can be crucial for maintaining data integrity. This guide provides detailed instructions on how to prevent duplicates in Excel.
Compare methods for preventing duplicate entries
Prevent duplicate entries with Data Validation feature
Quickly prevent duplicate entries with Kutools for Excel
Compare methods for preventing duplicate entries
Both Excel's Data Validation feature and Kutools for Excel can prevent users from entering duplicate values in a selected range. The main difference is how much setup is required and how much control you want over the validation rule.
| Method | Best for | Setup | Key advantage |
|---|---|---|---|
| Data Validation | Users who want a built-in Excel solution and customized validation messages | Create a Custom validation rule with a COUNTIF formula, then configure the Error Alert | No add-in required and the validation rule can be customized |
| Kutools for Excel | Quickly preventing duplicates without building validation formulas | Select the range and apply Prevent Duplicate | No formula or manual error-alert setup is required |
Prevent duplicate entries with Data Validation feature
For example, I am entering identify card number into a range of cells (A1:A100), and I want to make sure all item numbers entered in range A1:A100 are unique. To prevent duplicates, follow these steps:
1. Select the range of cells that will contain the item numbers.
2. Go to "Data" > "Data Validation" > "Data Validation". See screenshot:
3. And a "Data Validation" dialog box will display. Set the following operations:
- Click the "Settings" tab;
- Then click drop down list under "Allow", choose "Custom";
- Enter the below formula into the "Formula" box.
=COUNTIF($A$1:$A$100,A1)=1
4. Still in the "Data Validation" dialog box, do the following operations:
- Click "Error Alert" tab;
- Under the "Title" box, enter a title. For example: "Duplicate Entry";
- In the "Error message" box, enter the proper message. Such as: "The value was already entered. All Item Numbers must be unique. Please try again."
5. Click "OK" to finish it. Now when you enter a duplicate number, Excel will prevent the entry and alert the user with an error message, as shown below:
- =COUNTIF($A$1:$A$100,A1)=1 is used in Excel's Data Validation feature to ensure each entry within a specified range is unique. It works as follows:
- COUNTIF($A$1:$A$100,A1): Counts how many times the value in cell A1 appears within the range $A$1:$A$100.
- =1: Ensures that the count is equal to 1, meaning the value in A1 should appear only once in the range.
- If this condition is met, the entry is valid. Otherwise, Excel prevents the duplicate entry and displays an error message.
Quickly prevent duplicate entries with Kutools for Excel
If you want to avoid creating a custom Data Validation formula and configuring its settings manually, Kutools for Excel provides a much quicker solution. Its Prevent Duplicate feature lets you prevent duplicate entries in a selected range with just a few clicks.
1. Select the range that you want to prevent duplicate entries in.
2. Click Kutools > Prevent Typing > Prevent Duplicate, see screenshot:
Kutools will automatically monitor the selected range for duplicate entries. When you enter a value that already exists in the range, a warning message will appear and the duplicate entry will be prevented:
Both Data Validation and Kutools for Excel can effectively prevent duplicate entries. Data Validation offers more control over the underlying rule and warning message, while Kutools provides a much simpler setup for users who want to prevent duplicates without creating formulas or configuring validation settings manually.
Related Articles:
- Excel data validation: add, use, copy and remove data validation in Excel
- In Excel, the Data Validation feature is a powerful tool that you can use to limit what a user can enter into a cell. For instance, you can set rules to limit the length of text, restrict entries to specific formats, enforce unique values, or ensure text begins or ends with certain characters. These capabilities help maintain data integrity and reduce errors in your worksheets.
- Apply data validation to force phone number format in Excel
- Maybe, there are various phone number formats can be used when typing into the Excel workbook. But, how could you only allow one phone number format to be entered in a column of worksheet? For example, I just want the phone number as this format 123-456-7890 to be allowed entering in a worksheet.
- Data validation to restrict character length in Excel
- If you want users to enter a limited number of characters in an Excel cell (for example, only 7-10 characters), how can you do this? This article explains how to limit the number of digits using the Data Validation function in Excel.
- Validate email addresses in a column
- As we all known, a valid email address is composed of three parts, the user name, the "at symbol" (@), and the domain. Sometimes, you just allow others to enter only the email address format text into a specific column of worksheet. Is it possible to achieve this task in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in