How To Find Duplicate In Excel Sheet

How to find duplicates in Excel? Today we will explore this question in and out. We will get-go first past understanding How to Highlight Duplicates in Excel. Our 2d objective will exist learning How to Remove Duplicates. I volition explore several approaches to this chore – using the Data ribbons Remove Duplicates feature, but also showing how we can use MS Queries to remove duplicates and as well do additional filtering/sanitizing of our data set.

Find Duplicates in Excel

Example Table with DuplicatesThere are several means to go around Finding or Highlighting duplicates in Excel:

• i. Find using Excel Formulas
• ii. Highlight using Conditional Formatting

Following today's post nosotros will apply the example Excel Tabular array on the right.

one. Detect Duplicates using Excel Formulas

To Discover Duplicates in Excel using Formulas we volition utilize the COUNTIF Excel formula to verify if a certain row equally been repeated higher up. This will work only on a single cavalcade (our unique column identifier). Scroll downwards to the end to acquire how to bank check for duplicates based on all three columns.

Add Is Duplicate? Column to your Worksheet

Add a Is Duplicate? Column to your Worksheet. In our case bold that the Final column uniquely identifies records in my table input the following formula in the first jail cell of your new Is Duplicate? Column: Add together COUNTIF Excel Formula to Notice Duplicates Excel Formula:

=IF(COUNTIF(B$1:B1;"="&B2);"Indistinguishable";"")        

Elevate the Formula down to all Cells

Drag the COUNTIF Formula from the First Cell down to the Last.

Instead of dragging the Formula down manually you tin:

1. Select the Starting time Jail cell with the Formula
2. Re-create by Formula past hitting CTRL+C
3. Select all cells downward by striking CTRL+▼
4. Paste the Formula by hitting CTRL+P

Find Unabridged Row Duplicates

Did y'all notice that in the previous example two rows were in fact not duplicates? Run across below: Fake Duplicates every bit Last proper noun is not unique! What should you do if there is no Column that Uniquely identifies your rows? We need to employ a Helper Column! Come across Column D beneath: Add together a Helper Column that Concatenates All Columns Simply add together the Helper Column and Concatenate all other Columns like so:

=A2&"_"&B2&"_"&C2        

Next you need to use the COUNTIF Formula on the Helper Column instead.

I recommend separating columns with some Separator (such equally the _) as otherwise y'all may once again go False Positive Duplicates

2. Highlight Duplicates using Provisional Formatting

Conditional Formatting in HOME Ribbon TabTo Highlight Duplicates in Excel nosotros need to utilise the Conditional Formatting feature in the Domicile Ribbon Tab in Excel. Follow the steps below: How to Highlight Duplicates in Excel using Conditional Formatting

Select the Column to Highlight Duplicates

Select the Column in which y'all want to Highlight your Duplicates. Equally a Tip you tin Select the Outset Cell and striking CTRL+Finish.

Open up Conditional Formatting

Go to the HOME Excel Ribbon Tab and select Conditional Formatting.

Create New Rule

Every bit nosotros want to Highlight Duplicate Cell Values in Excel we need to create a new Conditional Formatting Rule. Click New Dominion.

Select Duplicate formatting

Select the Format only unique or duplicate values option to ascertain the Formatting of Indistinguishable Values.

Select Duplicate formatting

In the new window make certain Format All is gear up to duplicate. Side by side set the formatting for your Duplicates.

Once completed you lot should see your Duplicate Cells Highlighted in your Excel spreadsheet.

Remove Duplicates in Excel

Having answered the question of How to Find Duplicates in Excel, but what y'all often want to exercise is How to Delete Duplicates in Excel. So lets now tackle the consequence of How to Remove Duplicates in Excel.

Similarly as with Finding Duplicates, there several means to become around Removing Duplicates:

• 1. Remove Duplicates with Data Ribbon Remove Duplicates
• ii. Remove Duplicates using MS Query

Remove Duplicates with Information Ribbon

Fortunately Removing Duplicates is a typical scenario in Excel supported by the Data Ribbon Tab.

Select Excel Table

Select your Entire Excel Table past hitting CTRL+A.

Remove Duplicates

Go to the Data Ribbon Tab in Excel and select Remove Duplicates.

Select Columns with Duplicates

In this terminal step you need to Select All Columns in which at that place are Duplicates. If you but want to remove rows in which there are duplicates in a single column – remember to select only that Column.
Once y'all finish hit OK and done!

Remove Duplicates with MS Query

Microsoft Query is a smashing tool to run MS Queries (SQL) on Excel data.

If y'all haven't employ MS Queries read my post on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

The DISTINCT SQL statement filters only the Singled-out rows within a Tabular array. You lot can use it on any SQL SELECT Query. See my case beneath:

The table on the Left is the Source Table the table on Right is the Effect Table. This is the Microsoft Query you can utilise to filter but DISTINCT rows:

SELECT DISTINCT * FROM [Sheet1$]

Where replace Sheet1 with the name of your Worksheet.

Count Duplicates in Excel

Similarly as with the previous case, there several ways to go around Counting Duplicates:

• i. Count Duplicates using Excel Formulas
• 2. Count Duplicates using MS Query

Count Duplicates using Excel Formulas

To Count Duplicates in Excel follow the same steps equally in Find Duplicates using Excel Formulas except you lot need to utilize the post-obit Excel Formula using COUNTIF for the case in this postal service:

=COUNTIF(B$i:B1;"="&B2)

Similarly every bit in Find Entire Row Duplicates using Excel Formulas to Count Entire Rows that are duplicates, follow the above steps and create a similar Helper Column as such:

=A2&"_"&B2&"_"&C2        

Count Duplicates using MS Query

Microsoft Query is a great tool to run MS Queries (SQL) on Excel data.

If you haven't use MS Queries read my postal service on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

Nosotros can easily utilize a Microsoft Query to count only the duplicate records within our Information Ready:

Here is the MS Query for the in a higher place Table:

SELECT Commencement,Final,Age, (COUNT(*)-1) Every bit [Duplicates] FROM [Sheet1$]  GROUP By Get-go,Last,Historic period  HAVING COUNT(*) > one  Social club By COUNT(*) DESC

Source: https://analystcave.com/find-duplicates-in-excel/

Posted by: haneywhick1943.blogspot.com

Share this post