How To Remove Duplicates In Excel – Easy Excel Tutorial (2020)

August 20, 2020 3:05 pm

There are instances when the data we store in our Excel Spreadsheet has redundancy and duplication.

Although, having duplicates of data, can sometimes be of use for future references, we could try and avoid having multiple copies of datasets in the spreadsheets.

In the article, we’re going to look at methods to find and remove duplicates in your Excel spreadsheet.

Using ‘Remove Duplicates’ And ‘Conditional Formatting’

Finding

To check cells for duplicates, select them.
After that, click Home -> ‘Conditional Formatting’ -> ‘Highlight Cell Rules’ and finally, Duplicate Values.
A box, after values with, helps us to pick formatting for the cells having duplicate values.

How To Remove Duplicates In Excel – Easy Excel Tutorial

Removing

When we delete duplicate data, it is gone forever. Therefore, it is recommended you copy the original data and store it somewhere else as a backup before deleting.

Select the cells with duplicate data.
Click on Data -> Remove Duplicates.
Then, under Columns, select how and where you want to remove the duplicate data.
Click OK.

Using ‘Advanced Filter’

On the Data tab, there’s a tab; namely, Sort & Filter Group, select the ‘Advanced’ option.
The dialog box, Advanced Filter, appears.
Click ‘Copy to another location’, then select your desired range of duplicate cells in the List Range box.
In the Copy to box, mention the cell number you want.
Check the ‘Unique records only’ option.
Click OK.

Using the Formula Bar

You can also use the Formula Bar provided in MS-Excel. The Formula Bar takes in a formula with the cell numbers as inputs. For example, you have three entries/columns of data, namely, Player_Name, Team and Age, in columns A, B and C, respectively.

Now, to concatenate all the data into one cell, we select another column, say column E.
Then, type in the formula, ‘= [@Player_Name] & [@Team] & [@Age]’ or ‘= TEXTJOIN (“ ” , FALSE , PlayerList [@Player_Name] : [Age])’ into the formula bar, for column E.
You will then need to make another column, namely, column F, to count the duplicate values. You can use this to filter out rows of data that are duplicates, later.
Fill in the formula, ‘= COUNTIFS( $E$3 : E3, E3)’. This formula counts the total number of times; the given value is repeated.
Later, you can use the Advanced Filter to remove or filter out duplicate rows.

Using VBA

Built-in commands in VBA exist to help remove duplicates within list-objects. Another such command is,

Sub RemoverDuplicates() Dim DuplicateValues

As Range Set DuplicateValues =

ActiveSheet.ListObjects(“PlayerList”).Range

DuplicateValues.RemoveDuplicates Columns :=Array(1, 2, 3),

Header:=xlYes End Sub’

This formula removes duplicates from an Excel table named, PlayerList.
‘Columns:=Array(1, 2, 3)’ set columns on which we base detection. And since all the three columns are listed, the entire table will be checked.
‘Header:=xlYes’ gives back to Excel that the first row in our list contains ‘column headings’.
*Do make a copy of your data before running this VBA code as its aftereffects can’t be undone.

Using Power Query

Power Query handles data transformation and thus, can find and remove duplicate data.

After selecting your desired table, go to the Data Tab.
Then choose a From Table/Range query.
Now, select the columns based on which you wish to remove duplicates. Hold ‘Ctrl’ if you want to choose multiple columns.
When you Right-click on the previously selected column heading, choose to Remove Duplicates from the menu.
This can be done either to find and remove duplicates in columns, rows or the entire table. You can choose accordingly.

Using a Pivot Table

Pivot tables analyse our data. However, they can be used to hide duplicates too. We will be just displaying the unique values from your data set using a pivot table.

To first create a pivot table on your data.
One selecting a cell(s) inside your data, go to the Insert tab.
In the ‘Create PivotTable’ box, select ‘PivotTable’ and click on OK.
Change the layout to a tabular format.
Go to Design -> Report Layout. There we get two options, Show in Tabular Form and Repeat All Item Labels.
You may also need to remove some subtotals from the pivot table. On the Design tab, select ‘Subtotals’ -> select ‘Do not show subtotals’.
Our table without duplicates is ready.

How To Remove Duplicates In Excel – Easy Excel Tutorial (2020)

Conclusion

Duplicate values sometimes pose a significant threat to a clean and accurate data set. These basic methods in Excel help us remove these hidden duplicates that we sometimes miss.

Piyush

Follow me here

Share0

Tweet0

Share0

About the Author

Piyush Kashyap is a Ph.D student at Sant Longowal Institute of Engineering and Technology, Sangrur. He is a budding editor/ writer and has been working as a part-time reviewer for online content. He loves to read tech-based articles and has a knack for reviewing such articles He likes to stay updated about the latest trends in technology. He has also been working as a reviewer for many scientific journals. He also writes articles based on science. Know More About Piyush

Learn more