How to Remove Duplicates but Keep the First Value in Excel

When using Excel with large datasets, one of the most important and widely used tasks is to remove duplicate values from a data set. Today I’ll show you how to remove duplicates from your Excel data set and keep the first value at the same time.

Here we’ve got a data set with the Names, IDs, Marks, and Grades of some students in an examination of Sunflower Kindergarten.

Today our objective is to delete the duplicate values while keeping the first values from this data set. Here, be careful. The student’s name, Frank Orwell seems to be a duplicate value. But, the IDs are different. So, these are not duplicate values in the whole dataset.

1. Using Remove Duplicates Feature from Excel Toolbar to Delete Duplicates Excluding First Value

The quickest way to remove duplicates from a dataset is to use the Remove Duplicate feature from the Excel toolbar. Go through the steps below to accomplish this.

📌 Steps:

• First, select the whole data set.
• Afterward, go to Data > Remove Duplicates tool in Excel Toolbar under the section Data Tools.
• Subsequently, click on Remove Duplicates.

• Now, put a check on all names of the columns you want to erase duplicates from.
• Subsequently, click on the OK button.

Thus, you will get the duplicates removed automatically from your data set.

2. Using Advanced Filter to Remove Duplicates and Keep the First Value

You can use the advanced filter of Excel to eliminate duplicates by keeping the first value in your data set. Follow the steps below to do this.

📌 Steps:

• First and foremost, select the whole data set.
• Following, go to Data > Advanced tool in Excel Toolbar under the section Sort & Filter.
• Click on Advanced.

• As a result, the Advance Filter dialogue box will appear.
• In the Advanced Filter dialogue box, put a check on Unique records only.
• Last but not least, click on OK.

Consequently, you will get the duplicate rows removed automatically from your data set.

Read More: How to Remove Duplicate Names in Excel

3. Inserting UNIQUE Function to Remove Duplicates While Keeping the First Value in Excel

You can also use the UNIQUE function of Excel to delete the duplicate values while keeping the first value in Excel. To do this, follow the steps below.

📌 Steps:

• First, click on cell B5 and insert the following formula.

• Subsequently, press the Enter key.

It will delete the rows with duplicate values while keeping the first one, and create a fresh copy of the data set in this new location.

Read More: How to Delete Duplicates But Keep One Value in Excel 

4. Using Power Query to Remove Duplicates but Keep the First Entry

You can also use the Power Query tool to achieve your desired target here. Go through the steps below to accomplish this.

📌 Steps:

• At the very beginning, select the whole data set.
• Following, go to Data > From Table / Range tool in the Excel Toolbar under the section Get & Transform Data.

• Subsequently, click on From Table / Range.
• Consequently, the Create Table window will appear.
• In the Create Table dialogue box, Put a check on My table has headers.
• Subsequently, click OK.

• As a result, the Power Query Editor will open with your data set.
• Now, hold the Ctrl of your keyboard and select individual column headers.
• Afterward, From the Remove Rows option under the Home tab, click on Remove Duplicates.

• Consequently, the duplicate rows will be removed keeping the first rows.

• Now, under the Home tab, click on the Close & Load button.

• Thus, the filtered table will now be loaded to a sheet in your Excel file. Customize its properties as you want to see the result.

Finally, you will get your desired result which would look like this.

Read More: How to Remove Duplicate Rows Except for 1st Occurrence in Excel 

5. Applying VBA Codes to Eliminate Duplicates and Keep the First Value

If all the above-mentioned methods can’t satisfy you, you can use a VBA code to remove duplicate rows from your data set.

📌 Steps:

• Open a new VBA window and insert another new module.
• To do this, go to the Developer tab > Visual Basic Tool.

• Afterward, the VB Editor will open.
• Following, click on the Insert tab > choose the Module option.

• Following, Insert the following code in the module and press Ctrl + S.

Code:

Sub Remove_Duplicates()
  Dim Rng As Range
  Set Rng = Selection
  Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

• It produces a Macro called Remove_Duplicates. I want to remove the duplicate rows based on Columns 1 and 2 (Name and ID). You use your one.

• Now, a Microsoft Excel dialogue box will appear.
• Following, click on the No button.

• As a result, the Save As dialogue box will appear.
• Now, choose the Save as type: option as .xlsm file and click on the Save button.

• Thus, your file is macro-enabled now.
• Now, close the VB Editor and select your whole dataset.
• Following, go to the Developer tab > Macros tool.

• As a result, the Macro window will appear.
• Following, choose the Remove_Duplicates macro and click on the Run button.

Thus, the duplicates will be removed and the first values will remain. And, the outcome should look like this.

How to Identify Duplicates in Excel Without Deleting

Now, say you need to identify the duplicate student IDs without deleting the values. You can do this by conditional formatting. Follow the steps below to do this.

📌 Steps:

• First, select the student IDs cells (cells C5:C15 here).
• Following, go to the Home tab > Conditional Formatting tool > Highlight Cells Rules option > choose Duplicate Values… option.

• As a result, the Duplicate Values window will appear.
• Now, choose the styling in the values with text box and click on the OK button.

Thus, you will see the duplicate student IDs will be filled with light red color with dark red text. And, the result should look like this.

Read More: How to Undo Remove Duplicates in Excel

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, I have shown you 5 suitable methods to remove duplicates and keep the first value in Excel. Read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

Related Articles

• Remove Both Duplicates in Excel
• How to Hide Duplicates in Excel 
• Excel Remove Duplicates Not Working 

<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel