How to Remove Duplicates in Excel in 2024 (Two Methods)

Microsoft Excel is an incredibly powerful tool that helps us handle and analyze data efficiently. However, duplicate data can often create confusion and lead to inaccuracies in analysis. Thankfully, Excel offers several ways to identify and remove duplicates, ensuring that your data remains accurate and organized.

In this tutorial, we will discuss two effective methods for removing duplicates in Excel: 1) Remove Duplicates Tool and 2) Conditional Formatting. So, let’s dive in and learn how to get rid of those pesky duplicates.

Method 1: Remove Duplicates Tool

Excel has a built-in feature called “Remove Duplicates” that allows you to identify and remove duplicate data quickly. This feature is particularly useful when working with large datasets that contain numerous duplicates.

Step 1: Select the Data Range

First, you need to select the range of data that you want to remove duplicates from. Click on the top-left cell of the range, hold the left mouse button, and drag the cursor to the bottom-right cell of the range. Release the mouse button to complete the selection.

Step 2: Open the Remove Duplicates Dialog Box

With the data range selected, navigate to the Data tab on the Excel ribbon, and click on the Remove Duplicates button. This will open the “Remove Duplicates” dialog box.

Step 3: Configure the Duplicate Removal Settings

In the “Remove Duplicates” dialog box, you’ll see a list of columns in your data range. By default, all columns are checked, meaning that Excel will remove rows with identical values in all selected columns. If you want to remove duplicates based on specific columns, deselect the columns that should not be considered for duplicate removal.

Click OK to proceed. Excel will remove the duplicates from your data range and display a message indicating how many duplicates were removed and how many unique values remain.

Method 2: Conditional Formatting

Conditional formatting is another useful technique for identifying and removing duplicates in Excel. It helps you visually highlight duplicate values, making it easier to remove them manually or review them for further analysis.

Step 1: Select the Data Range

As with the first method, begin by selecting the range of data that you want to identify duplicates in.

Step 2: Apply Conditional Formatting

With the data range selected, navigate to the Home tab on the Excel ribbon, and click on the Conditional Formatting button. From the drop-down menu, select Highlight Cells Rules and then Duplicate Values.

Step 3: Choose the Formatting Style

In the “Duplicate Values” dialog box, you can choose the formatting style for highlighting duplicate cells. Excel offers several options, such as Light Red Fill with Dark Red Text or Yellow Fill with Dark Yellow Text. Choose the formatting style that works best for you and click OK.

Step 4: Review and Remove Duplicates

Once you’ve applied conditional formatting, Excel will highlight all duplicate cells within the selected data range. You can now review the duplicate values and remove them manually, if necessary.

To remove a duplicate row, right-click on the row number and select Delete from the context menu. Repeat this process for all highlighted duplicate rows.

Bonus Tip: Using Formulas to Remove Duplicates

If you’re looking for an alternative method to remove duplicates in Excel, you can use formulas. One such formula involves using the IF function along with the COUNTIF function.

1. Create a new column next to your data.
2. In the first cell of the new column, enter the following formula (assuming your data is in column A):

   =IF(COUNTIF($A$1:A1, A1) > 1, "Duplicate", "Unique")

   Replace “A1” with the cell reference of the first cell in your data range, and “A” with the appropriate column letter.

3. Press Enter to apply the formula. This formula checks if the value in cell A1 appears more than once in the range A1 to A1. If it does, the formula returns “Duplicate”; otherwise, it returns “Unique”.
4. With the cell containing the formula still selected, move your cursor to the bottom-right corner of the cell until it turns into a small black cross. Click and drag the cursor down the column to copy the formula to all other cells in the new column.
5. Now you‘ll see the words “Duplicate” and “Unique” in the new column, indicating whether each value in your data range is a duplicate or not.
6. To remove the duplicates, you can filter your data based on the new column. Click on the header cell of the new column, navigate to the Data tab on the Excel ribbon, and click on the Filter button.
7. Click on the filter arrow in the header cell of the new column, deselect “Duplicate,” and click OK. Excel will now display only the unique values in your data range.
8. If you want to remove the duplicate rows permanently, you can copy the filtered unique values to a new location or sheet and then delete the original data range.

Remember to delete the formula column once you have removed the duplicates, as it is no longer needed.