Home Tech 9 Ways To Find and Remove Duplicates in Excel

9 Ways To Find and Remove Duplicates in Excel

0

Duplicate values in a data set may be quite inconvenient! It can result in significant mistakes and an overestimation or underestimation of your results.

It is often important to Remove Duplicates in Excel and Excel make detecting and deleting these duplicates from your data a breeze.

We’ll explore my proven seven ways for locating and removing repeating values from an Excel data set in this article.

As a side note, if an alternative tool to use is Google’s, spreadsheet. While you would pay to have access to Excel, the Google counterpart is cheap and maybe a good option to explore.

That said, let’s get right into it!

What Are Duplicate Values?🤷‍♂️

When an identical value or collection of values appears in your data, you have duplicate values. These values can be defined in a variety of ways for a given collection of data.

Remove Duplicates in Excel

In the example shown above, there is a simple piece of data for a list of automobiles with three columns for Make, Year, and Model.

The first graphic shows all of the duplicates based only on the car’s make.

The second picture shows all of the duplicates based on the car’s Make and Model. As a result, there is one less duplication.

The Third picture shows all of the duplicates based on all of the table’s columns. As a result, fewer values are deemed duplicates.

Duplicates based on a single column vs. the full table might provide drastically different outcomes. You should be aware of the version you want and what Excel is doing at all times.

How to Use The Remove Duplicates Command to Remove Duplicate in Excel

Duplicate value removal in data is a typical activity. It’s so prevalent that the ribbon has a specific command for it.

Go to the Data tab and click the “Remove Duplicates command” on a cell within the data that you wish to remove duplicates from.

The “Remove Duplicates window” will appear when Excel has selected the complete set of data.

9 Ways To Find and Remove Duplicates in Excel

Then you must inform Excel if the first row of data has column headers. If this option is selected, the data on the first row will be ignored while looking for and deleting duplicate entries.

9 Ways To Find and Remove Duplicates in Excel

After that, you may choose which columns to utilize to find duplicates. If you have a large list of columns in your data, you may utilize the “Select All and Unselect All buttons” above.

When you hit OK, Excel will delete all duplicate values it discovers and provide you with a total count of the number that were eliminated and how many remain.

Because this command will change your data, it’s preferable to run it on a duplicate of your data set to keep the original data unchanged.

How to Use Advanced Filters to Find And Remove Duplicates in Excel

There’s also a technique to remove duplicate values from your data using the ribbon. The sophisticated filters make this feasible.

Navigate to the Data tab and select the option for the “advanced filter” command on a cell inside the data.

9 Ways To Find and Remove Duplicates in Excel

Doing this opens up a window for “Advanced Filter.

You may either Filter the list in its current position or Copy it to a different location. If the list is filtered in its current position, it will hide any duplicate rows, but moving the data to another location will duplicate the data.

Excel will make an educated guess about the data range, but you may change it in the List range. If the Copy to another place option was selected, the Criteria range can be left blank, but the Copy to the box must be filled.

Select “Unique records only” from the drop-down menu.

9 Ways To Find and Remove Duplicates in Excel

If you click OK, the duplicate values will be removed.

Advanced filters may be a useful tool for getting rid of duplicate values while also making a copy of your data. Advanced filters, on the other hand, will only be able to do this on the full table.

How to Use Pivot Table to Find And Remove Duplicates in Excel

Isn’t the sole purpose of pivot tables to analyze data?  You may also utilize them to get rid of duplicate data!

With this approach, you won’t be eliminating pre-existing values from your data; instead, you’ll be utilizing a pivot table to present only the data set’s unique values.

Create a pivot table using your data first. Pick a cell or the whole range of data in your data, then go to the Insert tab, select PivotTable, and hit OK in the option for Create PivotTable dialog box.

9 Ways To Find and Remove Duplicates in Excel

Add all fields to the pivot table’s Rows area using the new empty pivot table.

9 Ways To Find and Remove Duplicates in Excel

After that, you’ll need to alter the layout of the resultant pivot table to make it tabular. Select Report Layout from the Design tab while the pivot table is chosen. You’ll need to alter two choices in this section.

9 Ways To Find and Remove Duplicates in Excel

Choose the option to “Show in Tabular Form” from the drop-down menu.

Select the option to “repeat all item labels”.

You’ll also want to get rid of any subtotals in the pivot table. Select Subtotals from the Design tab, then Do Not Show Subtotals.

9 Ways To Find and Remove Duplicates in Excel

You’ve created a pivot table that looks like a table of data!

Because pivot tables only show distinct values for elements in the Rows section, duplicates values in your data will be immediately removed.

How to Use Power Query to Find And Remove Duplicates in Excel

Because Power Query deals a lot with data transformation, you can trust it to discover and eliminate duplicate values.

Select the table containing values from which duplicates should be removed, then navigate to the Data tab and select a From Table/Range query.

9 Ways To Find and Remove Duplicates in Excel

How to Remove Duplicates in Excel Using One Column or More

You may use Power Query to eliminate duplicates in a table based on one or more columns.

You must choose which columns will be used to eliminate duplicates. To select several columns, hold down the Ctrl key during selection.

Remove Duplicates from the menu by right-clicking on the specified column header and choosing the remove duplicate heading.

9 Ways To Find and Remove Duplicates in Excel

You may also navigate Remove Rows / Remove Duplicates from the Home tab to get this command.

= Table.Distinct(#”Previous Step”, {“Make”, “Model”})

When you investigate the formula produced, you’ll notice that it uses the Table.

The second option specifies which columns should be used in the function.

How to Remove Duplicates in Excel Using One Or More Columns

You may pick all the columns in the table and then delete duplicates to eliminate duplicates across the board. There is, however, a quicker technique that does not need to choose all of the columns.

In the upper left corner of the data preview, there is a button with a list of instructions that may be applied to the full table.

Select Remove Duplicates from the table button positioned at the top left corner.

= Table.Distinct(#”Previous Step”)

By looking at the formula that’s been produced, you’ll notice that it utilizes the same Table.
There is no second parameter in this function. The formula will act on the entire table if the second parameter is omitted.

How to Keep Duplicates in Excel Using a Single Columns or The Whole Table

There are other instructions in Power Query for retaining duplicates for specified columns or the whole table.

Follow the same procedures as before, except instead of deleting duplicates, use the Keep Rows / Keep Duplicates command. This will display all of the information with a value that is duplicated.

How to Remove Duplicates in Excel Using A Formula

To identify duplicate values in your data, you can use a formula.

To begin, create a helper column that combines the data from any columns on which your duplicate definition will be based.

= [@Make] & [@Model] & [@Year]

9 Ways To Find and Remove Duplicates in Excel

All three columns will be concatenated into a single column using the technique above. Each column is joined using the ampersand operator.

= TEXTJOIN(“”, FALSE, CarList[@[Make]:[Year]])

The method above can be used instead if you have a lengthy list of columns to merge. This allows you to refer to all of the columns with a single range.

After that, you’ll need to create a new column with the aim of counting the duplicate data. Later on, this will be used to filter away data rows that appear many times.

= COUNTIFS($E$3:E3, E3)

9 Ways To Find and Remove Duplicates in Excel

You can count the frequency of appearances for the current value in the list of values by copying the above formula along the column.

If the count is 1, the value is the first time it appears in the data, and it will be added to your list of unique values. If the count is two or more, the value has previously occurred in the data and is a duplicate that may be deleted.

Filters should be added to your data set.

Choose the Filter command option from the Data tab.

Ctrl + Shift + L is a keyboard shortcut.

You may now use the Count column to filter your results. Filtering on 1 returns all unique values and eliminates any duplicates.

The viewable cells from the resultant filter can then be copied and pasted elsewhere. To select only the visible cells, use hotkey Alt + ;.

How to Remove Duplicates in Excel Using Conditional Formatting

You may use conditional formatting to identify duplicate values in datasets.

You’ll need to create a helper column to combine the data from the columns, much like using the formula approach. If you wish to identify repetition based on more than one column, you’ll need this combined column since conditional formatting doesn’t function with data across rows.

You must choose the merged data column.

Navigate Conditional Formatting / Highlight Cells Rules / Duplicate Values from the Home tab to construct the conditional formatting.

9 Ways To Find and Remove Duplicates in Excel

This will bring up the Duplicate Values window for conditional formatting.

You have the option of highlighting Duplicate or Unique data.

To highlight the data, you may choose from a number of standard cell forms or design your own unique format.

9 Ways To Find and Remove Duplicates in Excel

Caution: The previous techniques for finding and removing duplicates treat the first value’s instance as a duplicate and ignores it. This technique, on the other hand, will just emphasize the first instance and will not distinguish between them.

Once the values have been highlighted, it is possible to use the filter by the color option to filter on duplicate or unique values. Don’t forget to apply filters to your data. Select the Filter command from the Data tab, alternatively, the keyboard shortcut Ctrl + Shift + L works fine.

You can also explore our video on “3 EASY Ways to Find and Remove Duplicates in Excel“.

3 EASY Ways to Find and Remove Duplicates in Excel

Final Thoughts on how to Remove Duplicates in Excel

I have gone through a number of hacks to Remove Duplicates in Excel. Most of these are easy-to-follow methods, others are a bit advanced. Whatever your need is, you would be able to find a hack that suits your Excel level or needs.

I am curious to know what other hacks and shortcuts you have found, so please leave these in the comments section.

As a rule of thumb, there is no rule on the thumb with regards to which of these you should use. Think of what you want to achieve, the dataset you have, and which of the methods you feel the most comfortable using. These should be the only things informing the decision you make.

You should also note that, with formulas, it is really easy to make a mistake and ruin it all. You must check the formulas, character by character, and word by word to make sure you do not have bugs that will cause errors in your result.

If you have further questions on how to Remove Duplicates in Excel, let me know in the comment section and I will probably leave you a few workarounds or explanations.

That is it. Enjoy working on your data set!