Duplicates in Microsoft excel are more likely to be identical columns of particular data that you are working on within your worksheets. Clones can create more harm than good because they cause chaos which might cause businesses and people a sizeable amount of money, sometimes they can ruin a company's reputation that causes a lack of trust from their customers. The worst part is that when they accumulate, they manipulate all the workings in excel. To avoid the manipulative nature and behavior of the duplicates, there are several ways of deleting them. Some of the methods are listed and explained below:
1. Use advanced filters.
This is one of the easiest ways to get rid of duplicates. It can be done by the use of advanced filters. Advanced filters can be helpful in extracting matches and differences between two columns, filtering rows that match other items in the list, and finding exact matches including uppercase and lowercase.
To start with, you need to select a cell inside the data and go to the Data tab and click on the advanced command. This will then open the advanced filter window, it will then present two options for you to choose from, and one will filter the list in place the other one will be copied to another location.
Filtering the list in place is likely to hide the rows that have any duplicates. Make sure to tick, unique records only.
As you can see from the picture below all the unique records have been hidden
copying to another location will create a copy of the data. Then excel will guess the range of the data but you have the option of adjusting in the listed range. There is another option of criteria range, but this has to be left out then fill the copy to option if the copy to another location was chosen. Tick the box for unique records only.
Press the enter key, and all the duplicates will be eliminated.
2. Use of the power query method
Power query is all about data transformation, hence it has the ability to remove duplicate values from a set of data. To start with, you need to select a set or the table of values you want to remove duplicates from then select the data tab, and then choose a from table/query range.
Power query can be used to delete duplicates based on one or more columns and to select several columns you hold the Ctrl button and select several columns.
The next step is to right-click on the heading of the selected columns and select the remove duplicates option from the menu that appears, and all the duplicates will be removed.
3. Use of a pivot table
Basically, pivot tables are used for analyzing data, but they can be consequently used to remove duplicates from your data. This method won't actually be removing the duplicates from your data. Still, it helps get rid of them when the pivot table is created at the end because it only displays the unique values from the selected data set. let's use the following data as an example
To start with, you need to create a pivot table based on your selected data. You will select a cell inside your data or the whole set of data then click on the Insert tab, select the pivot table and click ok in the create pivot table in the dialogue box.
This will give you a blank pivot table, now add all the fields into the rows area of the pivot table. You need to change the layout of the pivot table to tabular format and to do it; you Select the pivot table and go to the design tab and select the report layout. In the report layout, there are two options you need to change, the first one you select the show in tabular form option, and the other one is to select the repeat all item labels option.
You will then be required to remove subtotals from your pivot table, to do it you will head to the design tab and select subtotals and then select do not show subtotals.
This will then give you a pivot table that has a tabular set of data. The pivot table will have removed the duplicates from your data because they list unique values for the items in the rows area.
