How to Delete Duplicates in Excel

Welcome to our blog post about how to delete duplicates in Excel. Having duplicate data in your spreadsheet can cause errors and inaccuracies, which can be both frustrating and time-consuming to correct. Fortunately, Excel offers several ways to identify and eliminate duplicate entries quickly and efficiently. In this post, we will guide you through the process of identifying and deleting duplicates in Excel step-by-step, ensuring your data is accurate and error-free. Let’s get started!

Checking for duplicates in Excel

Before we can delete duplicates in Excel, we need to identify them. The easiest way to do this is by using Excel’s built-in Duplicate Values feature. Here’s how:

1. Select the entire range of cells from which you want to delete duplicates.
2. Click on the Home tab on the Excel ribbon, then click on Conditional Formatting.
3. Select Duplicate values.
4. A dialog box will appear, select the rule type for Duplicate and select a format or leave it as default.
5. Click on OK.

Deleting duplicates in Excel

Once we’ve identified the duplicates in our Excel table, we can delete them using one of Excel’s several built-in options.

Method 1: Using Remove Duplicates

The easiest way to remove duplicates is by using the Remove Duplicates function:

1. Select the range of cells from which you want to remove duplicates.
2. Click on the Data tab on the Excel ribbon and select Remove Duplicates.
3. A dialog box will appear showing all the columns in the selected range, with checkboxes next to each column.
4. Select the checkbox(es) for the column(s) that you want to check for duplicates.
5. Click on OK.
6. Excel will remove all duplicates, leaving only the unique values in your table.

Method 2: Using Filter

You can also use Excel’s Filter function to remove duplicates:

1. Select the range of cells from which you want to remove duplicates.
2. Click on the Data tab on the Excel ribbon and select Filter.
3. Click on the Filter dropdown for the column you want to filter and select Filter By Selected Cell’s Color.
4. Select the cell color that denotes duplicate values.
5. Excel will filter all duplicates, leaving only the unique values in your table
6. You will need to copy the unique values to a new table if you want to remove the duplicates permanently in the old range

Using one of the above methods, you can quickly and easily remove duplicates from your Excel table, ensuring accuracy and saving you time.

Important Note

Before deleting duplicates, it’s important to consider the nature of your data and ensure that removing duplicates won’t affect its integrity. In some cases, duplicates may be deliberate or may contain different information that you need to account for. Make sure to save a backup of your data before removing duplicates, just in case you need to revert to the original version.

Preventing Duplicates in Excel

To ensure that duplicates don’t occur in the first place, you can use Excel’s built-in validation tools or use formulas that will flag duplicates automatically as they’re entered. To set up validation:

1. Select the range of cells where you want to prevent duplicates.
2. Click on the Data tab on the Excel ribbon and select Data Validation.
3. In Data Validation dialog box, select the Settings tab, then select List in the Allow list.
4. In the Source box, type the reference for the cells that contain the original values or select the cell range.
5. Select the Ignore Blank option if you’d like the cell to be left blank if the validation fails
6. Click on OK.

With validation set up, Excel will prevent the entry of any value that already exists in the selected cell range, thus eliminating the possibility of duplicates.

In Conclusion

Deleting duplicates in Excel is an essential task for maintaining accurate and error-free data. By following the steps outlined in this post, you can quickly and easily identify and remove duplicates from your Excel table. Furthermore, you can prevent the occurrence of duplicates in the future by using Excel’s built-in validation tools. We hope that this guide has been helpful to you and that you can use these tips to streamline your workflow.

FAQs

Here are some common questions and answers related to deleting duplicates in Excel:

Can I delete duplicates from multiple columns?

Yes, you can delete duplicates from multiple columns in a range using the Remove Duplicates function. When you select the range, make sure to select all the columns that you want to check for duplicates.

What happens to the cells left empty after removing duplicates?

If you use the Remove Duplicates function, cells left empty will remain empty, and cells containing unique values will be retained. If you use the Filter function to remove duplicates, the duplicates will be hidden, and the unique values will be shown. You will need to copy these unique values to a new range to remove the duplicates permanently.

Can I remove duplicates in Excel for Mac?

Yes, the Remove Duplicates and Filter functions are also available in Excel for Mac and work the same way as in Excel for Windows.

Can I remove duplicates in Excel Online?

Yes, the Remove Duplicates and Filter functions are also available in Excel Online. However, the steps may vary slightly from Excel for Windows or Mac.

What if I want to keep one duplicate and remove others?

You can do this by using a formula to identify duplicates and then manually selecting the ones you want to delete. Here’s one method to do that:

1. Insert a new column next to the column containing duplicates.
2. Enter the formula =COUNTIF(A:A,A1)>1 where A is the column you want to check for duplicates.
3. This formula checks if the value in the current cell (A1) appears more than once in column A.
4. Filter the new column by those with a value of 1 and manually delete duplicates that you wish to remove.
5. Copy the filtered column to a new range if you want to keep them as unique values, or leave them as is if you just want to manually remove selected duplicates.