Excel: How to Remove Duplicates and Keep First One


There are two ways to remove duplicates in Excel while keeping the first occurrence:

Method 1: Use the Remove Duplicates Button

Method 2: Use the UNIQUE() Function

The following examples shows how to use each method in practice with the following dataset in Excel:

Example 1: Use the Remove Duplicates Button

One way to remove duplicate rows in Excel while keeping the first occurrence is by using the Remove Duplicates button in the  Data tab.

First, highlight the cells in the range A1:B10.

Then, click the Data tab along the top ribbon and then click the Remove Duplicates button in the Data Tools group:

In the new window that appears, check the box next to each column name:

Once you press OK, the duplicate rows will automatically be removed:

We can see that Excel removed 3 duplicate rows and 6 unique rows were left.

Example 2: Use the UNIQUE() Function

Another way to remove duplicate rows in Excel while keeping the first occurrence is by using the UNIQUE() function.

In this particular example, we can type the following formula into cell D1:

=UNIQUE(A1:B10, FALSE, FALSE) 

Once we press Enter, a list of unique rows from the range A1:B10 will automatically be shown:

Excel remove duplicates and keep first

Notice that each of the duplicate rows have been removed while the unique rows remain.

This approach is particularly useful if you want to keep your original dataset while also creating a copy with only unique rows.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Count Duplicates
Excel: How to Find Duplicates Using VLOOKUP
Excel: How to Remove Duplicate Rows Based on Two Columns

Leave a Reply

Your email address will not be published.