Microsoft Excel is widely used worldwide for optimal data compilation. However, there are times when data is added to a sheet more than once. When you share a table, someone else can add the same data, or that data is duplicated when the table is merged. The process becomes more complex and lengthy when you have to remove these duplicates one by one. The more data, the higher the chance of duplication.
Luckily, there are several effective ways to remove duplicates in an Excel sheet. These time-saving methods don’t require much effort. Here are the best ways by which you can easily remove duplicates in Excel sheet.
Using the Remove Duplicates option on the Data tab
Microsoft Excel has a built-in tool known as the “Data Tab”. This tool will help you remove duplicate records in your table. Follow these steps to remove duplicates in Excel sheets using the Data tab.
- First of all, select the cell or specific range where you want to remove duplicates.
- Now go to the Data tab and in the Data Tools group, click Remove Duplicates.
- A dialog box will appear, check all boxes and click OK.
- Excel will remove the duplicates and a dialog box will appear. It will show you how many duplicates have been removed from the table. It will also show the number of unique values.
Using the advanced filter option
The advanced filter in Microsoft Excel allows you to filter out duplicate values and copy unique values to a separate location. Take a look at the steps you need to follow to remove duplicates using the Advanced Filter option.
- Select the cell or range where you want to remove duplicates.
- Now go to the Data tab and click on the Advanced Filter command. The advanced filter window will open.
- Select “Copy to Elsewhere”to copy the unique value in the Excel sheet to different locations.
- Now select and check the “List Ranges”option and make sure it’s the exact range you specified.
- In the Copy To option, enter the range where you want to copy your unique values.
- Be sure to check the “Only unique entries”box and click “OK”.
- Unique cells will be copied to another cell.
How to use Power Query tool to remove duplicates in Excel?
Power Query in Microsoft Excel is a function related to data transformation. It allows you to import data from various sources, clean up and transform your data. This is one of the easiest ways to remove duplicates in an Excel sheet. Follow the instructions below.
- Select a cell and go to the Data tab, select Get and Transform Data Section and click From Table/Range.
- A dialog box will appear asking you to create a capacity request table. Specify a range of values and click OK.
- The Power Query editor windows will appear. It will show you two tabs. You can remove duplicates based on one or more columns, or the entire table.
- To remove duplicates based on one or more columns, right-click on the header of a particular column. Use the CTRL button to delete multiple columns and delete duplicates accordingly.
- To remove duplicate records based on the entire table, click the button in the upper left corner of the data preview. Now select the Remove Duplicates option.
- Click “Close and Load”and the data will be loaded into your spreadsheet.