Excel How To Remove Duplicates (3 Easy Ways)

This post demonstrates how to remove duplicates from your Microsoft Excel worksheets. As with just about anything in Excel, there are multiple ways. I’ll show you 3 of the easiest and fastest ways to do it, specifically: the Remove Duplicates command, the UNIQUE function, and Power Query.

Video

Narrative

We’ll just walk through each of the 3 ways one at a time.

Remove Duplicates Command

Let’s say we have a single column of data:

One option for removing duplicates is to select any cell within the range and go to Data > Remove Duplicates. A dialog box will appear asking you to confirm a few things.

In this case, since we only have a single column, all we need to confirm is whether our data has a header row. Once we click OK, a dialog box will appear confirming the number of duplicate values removed.

And when we inspect our range, bam, duplicates removed:

Trying to IDENTIFY the duplicates rather than REMOVE them?

One way to identify potential duplicates, rather than remove them, is with Conditional Formatting. We can select our range of cells, go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values, and choose to highlight the duplicates. Once we click OK, it will show us which items appear more than once in the list.

Does the data have multiple columns?

Now let’s consider data with multiple columns.

The key idea here is to think about which columns have to match for a row to be considered a duplicate. For example, would the row be considered a duplicate if only the Item column matched? Or would both the Item and Amount columns have to match for the row to be considered a duplicate?

Once we have defined this, we can communicate it to Excel inside the Remove Duplicates dialog.

We check the boxes for the columns that have to match for the row to be considered a duplicate. For example, if we select both Item and Amount, then both of those values have to match for the row to be considered a duplicate.

Writing a formula with the UNIQUE Function

Another option for removing duplicates is by using the UNIQUE function. The idea is that you use the UNIQUE function and provide your data range as the function argument. It will return a de-duped list of the values. If your range is a single column, it will return a single column of unique values. If your range has multiple columns, it will return them excluding any rows where the values in all columns match.

Note: depending on your version of Excel, you may or may not have this function. To quickly determine if your version of Excel has this function, type “=U” into any blank cell. If UNIQUE shows up in the drop-list, you have it.

For example, the source data is on the left side of the screenshot, and the UNIQUE function was used to generate the de-deduped list on the right:

In summary, the UNIQUE function will return one row for each unique value in the list. You can also include multiple columns, and it will return one row for every unique combination.

Power Query

Finally, let’s take a look at Power Query. We can select any range in our table, go to Data > From Table/Range to open the table in the Power Query editor:

Now, we select the columns we want Power Query to check for matching values. We can hold down the Shift or Ctrl key to select multiple columns. If we select the Item and Amount columns, both of those column values have to match for the row to be considered a duplicate.

Then we use the Home > Remove Rows > Remove Duplicates. We then use the Home > Close & Load command to send the results back to Excel.

The nice thing about using Power Query is that once the query is set up, we can simply click Refresh to update it next time the source data table changes (for example, if you copy/paste new rows into the source table).

Conclusion

So those are a few different ways to remove duplicates in Microsoft Excel. Depending on your version of Excel and the data you’re working with, one of these methods may be more suitable than the others. With the right approach, you can easily remove any duplicate values from your worksheets.

If you have any questions or any other ways to remove duplicates, please let me know by posting a comment below … thanks!

Sample file

FAQs

Here are some frequently asked questions.

How do I delete duplicates in Excel but keep one?

To delete duplicates in Excel while keeping one instance of each value, you can follow these steps:

• Select the range of cells or columns where you want to remove duplicates.
• Click the Data > Remove Duplicates command
• In the Remove Duplicates dialog box, make sure to select the columns you want to check for duplicates.
• Click the OK button to remove the duplicates, keeping the first occurrence of each value.

Is there an Excel formula to remove duplicates?

Depending on your version of Excel, you may have the UNIQUE function which will return a de-duped list of the range you pass as the function argument. For example, =UNIQUE(A1:A10) will return a list that excludes duplicate values in the A1:A10 range.

Does Remove Duplicates remove both value rows in Excel?

No, when you use the “Remove Duplicates” feature in Excel, it only removes the duplicated rows, keeping the first occurrence intact. It does not remove both values, but rather retains one instance of each unique value.

Why won’t Remove Duplicates work in Excel?

There can be a few reasons why the Remove Duplicates command may not work in Excel. Here are a few common causes:

• The range you selected contains merged cells. Unmerge the cells and try again.
• The range you selected contains empty cells. Remove any empty cells or fill them with values.
• The range you selected contains data that is not in tabular format. Make sure your data is organized in columns with a header row.

What is the best Excel formula to find duplicates?

The COUNTIF function is commonly used to identify duplicate values. For example, the formula “=COUNTIF(A:A, A1)>1” can be used to identify duplicates in column A.

How do I remove duplicates in Excel using COUNTIF?

To remove duplicates in Excel using the COUNTIF function, you can follow these steps:

• Insert a new column next to the column containing the data you want to check for duplicates.
• In the first cell of the new column (e.g., B2), enter the formula “=COUNTIF($A$1:A2, A2)” assuming your data is in column A.
• Copy the formula down to apply it to all cells in the new column.
• Copy the formula column, and Paste Special > Values to remove the formula but keep the results.
• To hide the rows, you can filter the data based on the new column greater than 1.
• To delete the rows, you can sort the data based on the new count column and then delete rows greater than 1.

How do I remove duplicates from both sets in Excel sheets?

If you have two sets of data in separate Excel sheets and you want to create a new combined table that removes duplicates from both sets, you can follow these steps for a one-time project:

• Copy and paste both sets of data into a single sheet, placing each set in separate columns.
• Select the range of cells containing both sets of data.
• Go to the “Data” tab in the Excel ribbon.
• Click on the “Remove Duplicates” button in the “Data Tools” group.
• In the “Remove Duplicates” dialog box, make sure all columns you want to check for duplicates are selected.
• Click the “OK” button to remove the duplicates, keeping the first occurrence of each value.

If you do this on a recurring basis, you could pull both tables into Power Query. Then do an Append query to combine them. Then use the Remove Rows > Remove Duplicates command.

How does remove duplicates work with multiple columns in Excel?

When you use the “Remove Duplicates” feature in Excel with multiple columns, it considers the combination of values across all selected columns to determine duplicates. It removes rows where the values in all selected columns match another row in the data. The first occurrence of the unique combination of values across those columns is retained.

What is the shortcut to remove duplicates in Excel?

The shortcut to remove duplicates in Excel is as follows:

Select the range of cells or columns containing the data you want to check for duplicates.

• Press the “Alt” key on your keyboard to display the Excel ribbon shortcuts.
• Press “A” for the “Data” tab.
• Press “M” again for the “Remove Duplicates” button.

Note: this may vary depending on your version of Excel, but when you press Alt you will see the corresponding letters for your version.

Which command is used to remove duplicates in Excel?

The “Remove Duplicates” command is used to remove duplicates in Excel. It is located in the “Data” tab of the Excel ribbon, in the “Data Tools” group.

How do I group duplicates in Excel?

You can group duplicates in Excel with a PivotTable or Power Query. To group duplicates with a PivotTable:

• Select the range of cells or columns containing the data you want to group.
• Insert > PivotTable.
• Drag the field that has the labels you want to group by to the Rows layout area.
• Drag the numeric field that has the values you want to sum to the Values layout area.
• View the beautifully grouped values in the resulting PivotTable.

To group them with Power Query:

• Use the Data > From Table/Range button to pull the table into the Power Query editor.
• Select the column that has the labels you want to group.
• Use the Transform > Group By command.
• In the resulting dialog, pick the operation (like Sum) and select the numeric column to add.
• Click OK and then Close & Load back to an Excel table.