1. ‪Utilities & tools‬ Duplicate Finder is an open-source app that helps you identify all duplicate files beneath a certain folder. When duplicate files are found, Duplicate Finder visualises each one, and even allows you to delete the files you select.
2. ‪Utilities & tools‬ Duplicate Finder is an open-source app that helps you identify all duplicate files beneath a certain folder. When duplicate files are found, Duplicate Finder visualises each one, and even allows you to delete the files you select.
3. This page describes how to remove duplicate rows in Excel, using three different methods. If you want to remove duplicate cells (rather than entire rows of data), you may find the Remove Duplicate Cells page more straightforward. In order to illustrate how to remove duplicate rows in an Excel spreadsheet, we will use the example spreadsheet below, which has data spanning three columns.

• Find And Remove Duplicates
• Find And Delete Duplicates

Learn simple ways to find and remove duplicates in Excel: use standard functions, Conditional Formatting, or a special add-in.

Remove duplicates in Excel: video transcript

In a large Microsoft Excel Worksheet, sometimes you may require to find and remove all the Duplicates Records in a range of data. In Excel, you can accomplis. If you're trying to find duplicates or uniques with the first occurrences, Duplicate Remover wizard can do it all. Click on the icon to start it, it will select the entire table and let you create a backup copy. Next you can pick the type of values you are looking for. Let's find duplicates, click Next and choose the columns you want to check.

In this video we will look at the ways you can highlight and remove duplicates in Excel. We will use conditional formatting to see the duplicates, employ formulas to find and remove duplicate rows, and work with our special add-in to do all this and more.

You have probably tried working with the 'Remove Duplicates' tool in Excel. Let's have a quick look at what it does. Go to Data tab, click the icon, and you will get the list of all columns in your table. Here you can choose key columns you want to check: one column,say, to look for duplicate IDs, or all to find duplicate rows. If I want to find the rows that have the same name and book title, these are the columns I need to select. Once I click ok, it will simply remove all duplicates and leave the original values intact.

Find And Remove Duplicates

Highlight duplicates in Excel

If you prefer to take a look at the records before deleting them, you need to know the ways you can highlight duplicates in Excel. Your main assistant is going to be conditional formatting here.

It is also the quickest way to keep checking your table for repeating values. Select your column or range, go to Home tab and find Conditional Formatting in the Styles group. Select Highlight cell rules, Duplicate values. Here you can pick one of the standard fill and font colors or choose a custom combination.

Please note that Conditional Formatting compares each cell in the range to others, it won't compare columns or rows. Its biggest advantage is that it will keep comparing the cells when you add any new values to the range. However, it also includes the first occurrences as you can see. To exclude them, let's cancel our changes, select the records in column D that we want to compare, and create a new rule in Conditional formatting. Click to use a formula to determine which cells to format. You need to enter the formula in this box:

=COUNTIF($D$2:$D2,$D2)>1

So if it finds more than one occurrence of a value, the rule will highlight duplicates the way you choose when you click Format... Select color, and Click OK to apply the rule.

How to delete duplicates in Excel

When you want to find and then remove duplicates in Excel, you can get the best of the COUNTIF and IF functions. We can use the same formula:

=COUNTIF($D$1:$D2,$D2)>1

This way if the value from D2 is found more than once, it will be marked as TRUE. If you want to make more sense of the formula, you can enclose it in the IF condition:

=IF(COUNTIF($D$1:$D2,$D2)>1,'Duplicate',')

Copy the formula down to see the results.

Standard formula in Excel to remove duplicate rows

What about duplicate rows? What if you want to check the author's name in addition to the book id? In this case we need to modify the formula and use COUNTIFS instead of COUNTIF to deal with several conditions, then include one for each column we want to check. Here is how we shall change our formula:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1, 'Duplicate', ')

This formula lets us check values in each of the first three columns, so if they are the same in the range A2 through C2 and A11 through C11, they are marked as duplicates. If the name or the ID is different, the row is marked as unique. Now that you've marked the records you want, you can simply filter your table by the helper column and remove them, or do whatever you need to.

A special add-in for Excel: highlight duplicates, move or delete them, and more

You can also avoid the formulas, helper columns, or colors. Duplicate Remover add-in lets you select, highlight, copy, move, or remove the results and look for duplicates with or without the original values. Once you install the add-in, you'll find three new tools under Ablebits Data tab. Pick 'Dedupe table' for the quickest results.

The add-in will select the table right away. You can specify if you have headers, choose the key columns : select all to find complete row dupes, or look for certain records, like the same Book ID and title. Then choose from 6 possible actions for the results, and click Ok.

If you're trying to find duplicates or uniques with the first occurrences, Duplicate Remover wizard can do it all. Click on the icon to start it, it will select the entire table and let you create a backup copy. Next you can pick the type of values you are looking for. Let's find duplicates, click Next and choose the columns you want to check. Again, you can select all to find complete row matches, or just the key columns.Finally, you can choose to delete duplicates, highlight them with any color, or choose from a bunch of other options. Click Finish and get your duplicate-free table.

If this add-in is what you need, you can order Duplicate Remover or the entire Ultimate Suite with a 15% discount. Feel free to use the coupon code Preview.

If you have any questions about using Duplicate Remover or any standard Excel ways to find duplicates in your table, please post them as comments, we'll do our best to assist you.

You may also be interested in

How to remove all duplicates but keep only one in Excel?

This tutorial introduces the tricks to remove all duplicate values from a list but keep only one in Excel, read the details:

Remove all duplicates but keep only one with Kutools for Excel

Combine all duplicate rows and keep data with Kutools for Excel

Remove all duplicates but keep only one with Remove Duplicates function

In Excel, the Remove Duplicate feature can help you remove all but keep one duplicate value.

1. Select the data list you need, and click Data > Remove Duplicates. See screenshot:

2. Then in Remove Duplicates dialog, check the column name that you want to remove duplicates from, and if your data has header, check My data has headers option, too. See screenshot:

3. Click OK. And a dialog pops out to tell you how many duplicates have been removed, just close it. And now all duplicates have been remove but keep only one in the list.

Remove all duplicates but keep only one with VBA

If you are interested in VBA, here I also can introduce a VBA code to solve this task.

1. Press Alt +F11 keys simultaneously to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below VBA to the new Module window.

VBA: Remove all duplicates but keep one.

3. Press F5 key or click Run to execute the VBA, and the duplicates in list in column A have been removed but keep one.

Tip: In the above VBA, A indicates the column A, and A1: A indicates the list range, you can change them as you need.

Remove all duplicates but keep only one with Kutools for Excel

There is a powerful utility in Kutools for Excel named Select Duplicate & Unique Cells can quickly select or highlight all duplicates except first one, and then you can remove them.

After free installing Kutools for Excel, please do as below:

1. Select the data range and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the displayed dialog, check Duplicates (Except 1st one) option, and click Ok. See screenshot:

3. Then a dialog pops out to remind you the number of selected cells. And just click OK to close it. See screenshot:

4. And all the duplicates but first one are selected, press Delete key to clear them.

Tip: You also can remove all duplicates but keep one with Select Duplicate & Unique Cells like this:

Select all the unique values including the first duplicates, and then copy them by pressing Ctrl + C and paste to another location by Ctrl + V.

Remove All Duplicates But Keep Only One

Combine all duplicate rows and keep data with Kutools for Excel

If you have some data, and what you want is to combine the duplicate rows then keep the corresponding data as below screenshot shown, you can use Kutools for Excel’s Advanced Combine Rows function.

After free installing Kutools for Excel, please do as below:

1. Select the data and click Kutools > Merge & Split > Advanced Combine Rows. See screenshot:

2. In the Combine Rows Based on Column dialog, select the column you want to remove duplicate from and click Primary Key to make it as the primary key column. See screenshot:

3. Then select the column you just want to keep the data and then click Combine, and select one delimiter form the list. See screenshot:

4. Click Ok, and the duplicate rows are combine and keep the corresponding data.

Combine Duplicates But Keep Adjacent Data

Find And Delete Duplicates

Relative Articles:

• How to generate random number without duplicates in Excel?
  

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!