How to remove all duplicates but keep only one in Excel?
This tutorial introduces the tricks to remove all duplicate values from a list but keep only one in Excel, read the details:
Remove all duplicates but keep only one with Remove Duplicates function
Remove all duplicates but keep only one with VBA
Remove all duplicates but keep only one with Kutools for Excel 
| Feature | Remove Duplicates | VBA Code | Kutools for Excel |
|---|---|---|---|
| Best for | Quickly removing duplicate values with Excel’s built-in tool | Automating duplicate removal with code | Selecting or removing duplicate values while keeping the first occurrence |
| Keeps one duplicate | Yes | Yes | Yes |
| Setup complexity | Very simple | Advanced | Very simple |
| Preview / control before deletion | Limited | Limited | Better control because duplicates can be selected before clearing |
| Main advantage | Built into Excel and fast for standard duplicate removal | Automates repeated duplicate-removal tasks | Flexible selection options and keeps the first occurrence easily |
| Main limitation | Removes duplicates immediately and offers limited selection control | Requires VBA knowledge and macro execution | Requires Kutools for Excel |
| Recommended use | Simple one-time duplicate cleanup | Repeated or automated duplicate-removal tasks | Users who want more control over which duplicates are selected or removed |
Remove all duplicates but keep only one with Remove Duplicates function
In Excel, the Remove Duplicate feature can help you remove all but keep one duplicate value.
1. Select the data list you need, and click Data > Remove Duplicates. See screenshot:
2. Then in Remove Duplicates dialog, check the column name that you want to remove duplicates from, and if your data has header, check My data has headers option, too. See screenshot:
3. Click OK. And a dialog pops out to tell you how many duplicates have been removed, just close it. And now all duplicates have been remove but keep only one in the list.
Remove all duplicates but keep only one with VBA
If you are interested in VBA, here I also can introduce a VBA code to solve this task.
1. Press Alt+F11 keys simultaneously to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below VBA to the new Module window.
VBA: Remove all duplicates but keep one.
Sub RemoveAllDeplicateButOne()
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=Name
End Sub3. Press F5 key or click Run to execute the VBA, and the duplicates in list in column A have been removed but keep one.
Tip: In the above VBA, A indicates the column A, and A1: A indicates the list range, you can change them as you need.
Remove all duplicates but keep only one with Kutools for Excel
There is a powerful utility in Kutools for Excel named Select Duplicate & Unique Cells can quickly select or highlight all duplicates except first one, and then you can remove them.
After free installing Kutools for Excel, please do as below:
1. Select the data range and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:
2. In the displayed dialog, check Duplicates (Except 1st one) option, and click Ok. See screenshot:
3. Then a dialog pops out to remind you the number of selected cells. And just click OK to close it. See screenshot:
4. And all the duplicates but first one are selected, press Delete key to clear them.
Tip: You also can remove all duplicates but keep one with Select Duplicate & Unique Cells like this:
Select all the unique values including the first duplicates, and then copy them by pressing Ctrl + C and paste to another location by Ctrl + V.
Conclusion
Removing duplicate values while keeping one occurrence is a common way to clean Excel data, reduce repeated records, and prepare lists for further analysis.
Remove Duplicates is the quickest built-in option for simple one-time cleanup. VBA is more suitable when the task needs to be repeated or customized through automation. If you want more control before deleting anything, Kutools for Excel can first select duplicate values while keeping the first occurrence, allowing you to review, delete, copy, or process the selected duplicates as needed.
Tip: Before removing duplicates, make sure you select the correct columns used to identify duplicate records, and consider keeping a backup if the original data is important.
Choose Remove Duplicates for quick cleanup, VBA for automated workflows, or Kutools when you prefer a more flexible way to identify and handle duplicates before removing them.
Related Articles:
- How to combine multiple cells with leading zeros into a cell in Excel?
- How to generate random number without duplicates 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