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.
Prevent duplicate entries with Data Validation feature
Quickly prevent duplicate entries with Kutools for Excel
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, it will show up like this::
- =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 only appear once in the range.
- If this condition is met, the entry is valid. Otherwise, Excel prevents duplicate entries and displays an error message.
Quickly prevent duplicate entries with Kutools for Excel
It is somewhat difficult for us to use Data Validation function to prevent duplicate entries, now I will introduce you an easy and quick way to solve this task. With "Kutools for Excel", you can prevent duplicate entries with one click.
1. Select the range that you want to prevent duplicate entries.
2. Click "Kutools" > "Prevent Typing" > "Prevent Duplicate", see screenshot:
Kutools will automatically monitor the selected range for duplicate entries. When you enter the same data with the first data in the range, you will get the following warning message:
Both Data Validation and Kutools for Excel are effective ways to prevent duplicate entries in a column. Choose the method that best fits your workflow and requirements. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
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