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: 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:
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:
UNIQUE Function
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: =UNIQUE(range)
Find Tool
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:
Power Query
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: Power Query will automatically enter the values in a table on your worksheet.
