How to only allow unique values in Excel?
When managing data in Excel, ensuring data accuracy is essential, especially when collecting information in columns that must not contain duplicate entries—such as product codes, employee IDs, registration numbers, or other unique identifiers. Unintentional duplicate input can lead to errors in calculations, reporting, or subsequent processing. This article discusses several practical techniques to restrict input to unique values within a column or range, helping users efficiently maintain data integrity in their worksheets. Each method has its own applicable scenarios and advantages. Troubleshooting tips, explanatory notes, and alternative solutions are also provided to guide you in choosing the most suitable approach for your needs.
Only allow unique values in worksheet with Data Validation
Only allow unique values in worksheet with Kutools for Excel
Only allow unique values in worksheet with VBA code
Only allow unique values in worksheet using Excel formula helper column
Only allow unique values in worksheet via Remove Duplicates feature
Only allow unique values in worksheet with Data Validation
Excel's Data Validation feature allows you to set rules for what can be entered into cells. To restrict input so that only unique values are accepted within a specific column or range, follow these steps:
1. First, select the cells or column where you want to enable unique value entry. For example, suppose all your unique IDs are in column E; click on column E to select it. Go to Data in the ribbon, then choose Data Validation > Data Validation.
2. In the Data Validation dialog box, configure the settings as follows for enforcing unique entries:
(1.) Go to the Settings tab;
(2.) From the Allow drop-down list, select Custom;
(3.) In the Formula box, enter: =COUNTIF($E:$E,E1)<2 (where E is your target column and E1 is the first cell in your selection.) Adjust the references if your data is in a different column (for example, change E to A if working with column A).
The formula works by counting how many times each input already exists in the column; if the count is less than 2, the entry is allowed as unique.
3. Click OK to apply the validation. Now, whenever a duplicate value is entered into the specified column, Excel will display a warning and prevent the entry unless the value is unique. The default warning may read “This value already exists,” or similar.
Applicable scenarios: This solution is excellent for simple lists and settings where only a single column needs unique values. However, Data Validation does not prevent duplicates if values are pasted into the column from elsewhere—so it’s advisable to enter values manually or regularly check for duplicates after pasting.
Tips: You can customize the warning message in the Error Alert tab of the Data Validation dialog.
Precautions: Make sure your selection covers all cells users would enter data in, or extend the validation rule by selecting the entire column if necessary.
Troubleshooting: If Data Validation seems not to work, double-check that your formula’s cell references are correct and the validation is applied over your intended range.
Only allow unique values in worksheet with Kutools for Excel
The above method only can prevent the duplicate entries in one column, if you have Kutools for Excel, its Prevent Duplicate utility can help you quickly prevent duplicate in a range of cells and only one column or row.
After you have installed Kutools for Excel, here is how to use the Prevent Duplicate feature:
1. Select the column or range where you need to prevent duplicate entries and only allow unique data input. This might be a single column, multiple columns, or a range such as A1:D15.
2. Click on Kutools in the Excel ribbon, then go to Prevent Typing and choose Prevent Duplicate. This starts the process of setting up the uniqueness rule for your selected range.
3. You’ll see a warning message informing you that applying this feature will remove any pre-existing Data Validation rules in your selected range. This ensures that Kutools' rules don't conflict with other validation settings.
If you wish to continue, click Yes to confirm. Kutools will then apply the uniqueness enforcement.
4. Another prompt box appears confirming which cells have been processed. This helps ensure that you’re aware of where uniqueness is now required.
5. Click OK to finish. Now, if you try to enter or paste duplicate data within the specified range (for example, cells A1:D15), Kutools will display a prompt that the input is invalid and you must enter unique values only.
Applicable scenarios: Best used to control over multiple columns/rows.
Tips: Before applying, consider whether any existing Data Validation rules are important to your workflow, as they will be removed from the selected range.
Precautions: Double-check your cell selection before enabling the feature, especially in complex tables.
Troubleshooting: If the prompt doesn’t appear, or duplicates still get through, confirm Kutools for Excel is properly installed and updated.
There are more than300 functions available to streamline your tasks. You can download Kutools for Excel for free trial.
Only allow unique values in worksheet with VBA code
If you are comfortable with macros and wish to add more advanced logic to validate for unique values, using VBA (Visual Basic for Applications) can be a flexible solution. VBA scripts can be tailored to check for duplicates during data entry and instantly notify users when an invalid value is found, automatically removing the duplicate entry if desired.
1. Right-click the sheet tab where you want to allow only unique values, and select View Code from the context menu. In the pop-up Microsoft Visual Basic for Applications window, copy and paste the following code directly into the sheet module (not a standard module):
VBA code: Only allow unique values in worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160829
Dim xRg As Range, iLong, fLong As Long
If Not Intersect(Target, Me.[A1:A1000]) Is Nothing Then
Application.EnableEvents = False
For Each xRg In Target
With xRg
If (.Value <> "") Then
If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
iLong = .Interior.ColorIndex
fLong = .Font.ColorIndex
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
.ClearContents
.Interior.ColorIndex = iLong
.Font.ColorIndex = fLong
End If
End If
End With
Next
Application.EnableEvents = True
End If
End Sub
Note: In this code, A1:A1000 refers to the cells monitored for unique input. If your unique data is in a different range, adjust these references to match the column or range you are using.
2. After entering the code, click Save and close the VBA window. If you have macro security enabled, make sure macros are allowed in your workbook settings.
Now, entering duplicate values in range A1:A1000 will immediately trigger a warning message.
3. Click OK in the message box, and the duplicate input will be removed from the cell.
Only allow unique values in worksheet using Excel formula helper column
In addition to Data Validation and VBA, a practical alternative is using a helper column in combination with Excel formulas to identify duplicate values. This method does not block entry but provides instant feedback and is a useful option when you want to quickly review and clean up your data.
1. Add a helper column adjacent to your data, for example, column F if your data is in column E. In cell F1, enter this formula:
=IF(COUNTIF($E$1:E1,E1)=1,"Unique","Duplicate")2. Press Enter to confirm, then drag the formula down to apply it to all rows. The formula checks each entry in column E, marking “Unique” for the first occurrence and “Duplicate” for subsequent entries.
Tips: Use this helper column to filter out duplicate rows or to visually highlight issues before sharing your data.
Applicable scenarios: Best for reviewing existing historical data or when manual cleanup is needed rather than real-time prevention.
Only allow unique values in worksheet via Remove Duplicates feature
If your priority is not to restrict entry but to regularly clean up a list to keep only unique values, Excel's built-in Remove Duplicates feature is straightforward and effective.
1. Select the column or table you want to process.
2. Go to Data > Remove Duplicates. In the dialog, choose which columns to check. Click OK and Excel will automatically keep only the first occurrence of each value and delete further duplicates.
Applicable scenarios: Ideal for cleaning up large tables after mass data entry or import.
Pros/Cons: Fast and simple, but does not prevent future duplicates—it only removes existing ones.
Summary and Suggestions: The best approach depends on your workflow and level of control needed. Use Data Validation or Kutools for proactive prevention; VBA for custom rules and automation; formulas and Remove Duplicates for reviewing and cleaning up data manually. Always check your selection and rule logic before large-scale application, and backup your workbook when testing new solutions.
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