How to eliminate repeated values (remove duplicates) in Excel?
For a list of data with repeated values, you may need to eliminate the repeated ones and keep only the unique values in the list. This article shows you two methods of eliminating repeated values (duplicates) from a list in Excel.
Supposing you have a fruit table as below screenshot shown, now you want to eliminate all repeated fruits from the Fruit column. Please do as follows.
1. Select the entire table, then click Data > Remove Duplicates. See screenshot:
2. In the Remove Duplicates dialog box, only check the column you want to remove duplicates based on (here I choose the Fruit column), then click the OK button.
3. Then a prompt box pops up to tell you how many duplicates are removed, please click the OK button.
Now all repeated values are eliminated from the specified column list, and only the unique ones remained.
The above method can only remove duplicates except the first one. If you want to remove all duplicates including the first one, or remove unique values from a list, you can try theSelect Duplicate & Unique Cells utility of Kutools for Excel.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the list with the repeated values you want to eliminate, then click Kutools > Select > Select Duplicate & Unique Cells.
2. In the Select Duplicate & Unique Cells dialog box, if you only want to remove duplicates except the first one, please select the Duplicates (Except 1st one) option in the Rule section. Check the Select entire rows box, and then click the OK button. See screenshot:
3. Then a dialog box pops up to tell you how many rows are selected, please click the OK button. And now right click on any selected row, click Delete from the right-clicking menu to eliminate the rows with repeated values.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)