You could get certain data duplicated when you share your Excel workbook with your peers or simply be a result of absent-mindedness. Regardless of the reason, you can work your way around removing these errors through different methods in Excel.
In this article, we will discuss various ways you could find and remove duplicate values in Microsoft Excel. Keep reading this article to eliminate all duplicate data from your workbook!
How to Remove Duplicate Values in Excel?
We have found six different methods you can use to remove duplicate values from your workbook. These methods range from using the Remove Duplicates tool to using the Power Query.
You can refer to any one of the methods we’ve listed below to determine the best method for you.
Remove Duplicate Tool
The Remove Duplicate tool is the designated tool in MS Excel to get rid of repeated data in MS Excel. When you run this tool, Excel will remove the copies of your data while keeping the original data intact.
However, it is important to consider that this change is permanent, so we recommend you make a copy of your table before using this tool. Similarly, this tool isn’t case-sensitive, so it will consider repeated data in a different case as duplicates.
Follow these steps to use the Remove Duplicate tool in MS Excel:
- On the spreadsheet, select the table with the repeated data.
- Head to the Data tab from the menubar.
- On the Data Tools section, select the Remove Duplicates tool.
- Under Columns, select the columns you want Excel to scan through for duplicate data.
- Select OK.
Excel will automatically remove duplicate data from the selected cells.
Filter Duplicate Data
The Filter tool is another efficient method of removing such redundancies from your Excel worksheet. Using this method, you can either remove duplicates from the table or copy only the unique records to different cells.
Here are the steps you can refer to using the Filter tool to remove duplicates from your spreadsheet:
- Launch MS Excel to open your workbook.
- Navigate through the menu bar to select the Data tab.
- Locate the Sort & Filter section, then select Advanced.
- On the pop-up, select either:
- Filter the List, In-Place: Excel will remove duplicates from the selected table itself.
- Copy to Another Location: Excel will create a copy of unique records and paste it into the range you’ve specified in the ‘Copy to:’ section.
- Specify the List Range. Your list range will be the table which you want to remove the duplicate values from.
- Select the box next to Unique records only.
- Click OK.
Use Conditional Formatting
Conditional Formatting doesn’t automatically remove duplicate values, but it highlights the repeated data. This method is best suited for users who do not immediately want to delete repeated data.
After Excel highlights the duplicated data, you go ahead and manually remove each column. Follow these steps to use conditional formatting to highlight repeated data:
- On an open workbook, select the table with the duplicate data.
- Locate the Styles section on the Home tab.
- Drop the menu down for Conditional Formatting.
- Hover over Highlight Cells Rules.
- From the fly-out menu, select Duplicate Values.
- Next to values with, drop the menu down to select how you want to format the cells with duplicate values.
- Click OK.
If you own the Office 365 version of Excel, you can copy unique records on different cells using the UNIQUE function. Besides you can also use the web version of Excel to use the UNIQUE function.
Enter a formula with the UNIQUE function in this format to copy unique values to another location in your sheet:
You can use the Find tool in Excel if you know which data is repeated on your sheet. Instead of locating the duplicate columns, the find tool will locate all repeated keywords in the sheet. While it also highlights the repeated keyword, it also gives you the number of times the value is repeated in your workbook.
Follow these steps to use the Find tool in MS Excel:
- On your keyboard, hit the combination Ctrl + F to call the Find tool.
- On the Find and Replace window, enter the keyword you want Excel to locate next to Find what.
- Select the Options << button for additional settings:
- Within: Specify where you want Excel to look for the keyword.
- Search: Select if you want Excel to look for data by rows or columns.
- Look in: Choose whether you want Excel to look for the entered keyboard in Formulas, Values, or Comments.
- Match Case: Select the box next to Match case if you only want Excel to locate words according to the mentioned case.
- Match Entire Cell Contents: You can check the box next to this option if you want Excel to match the content of whole cells.
You can also use Power Query to remove all duplicate data from your Excel Sheet. It is worth remembering that the Power Query is case-sensitive. This means that Power Query will only remove data with the same word case. For instance, Power Query will not recognize Daisy and daisy as repeated keywords.
If you want to eliminate this issue, you can convert all the words into camel case before proceeding to remove duplicate data.
Follow these steps to use the Power Query to remove duplicate data from your Excel workbook:
- On an open workbook, select your table with repeated data.
- From the menubar, select the Data tab.
- Locate the Get & Transform section to select the From Table option.
- Make sure all cells from your table are selected on the Create Table pop-up, then click OK.
- On Power Query Editor, press and hold the Shift key, then select the last header to select your table.
- Right-click on the header and select Remove Duplicates.
- To load the unique values to your workbook, head to the Home tab and select Close & Load.
Power Query will automatically enter the values in a table on your worksheet.