A spreadsheet is a great way of packing lots of information into a dense area — but, more often than not, these documents will become unmanageable and unwieldy when they’re in use for a long period of time.
Whether your spreadsheet is tracking your family’s budget or the people who subscribe to your newsletter, beyond a certain point it will be impossible for you to scan through manually with any degree of accuracy. This can be a real problem if you need to check for duplicates.
Excel 2008 for Mac requires more work to remove duplicate values in a data set. Select all formulas by making C2 the active cell, and using the keyboard.
Fortunately, you can delegate the grunt work to your computer. By using conditional formatting or the COUNTIF function, you can find duplicates in columns or duplicates across rows of a spreadsheet in no time at all. Here’s how to do it.
Finding Duplicates Using Conditional Formatting
Conditional formatting is the quickest and easiest way to find duplicates on a single sheet. We’re going to use this technique to sort through this database of email addresses, weeding out any duplicates that have arisen thanks to users submitting the same information more than once, and registering the same address under more than one name.
The first step is to select all the data we want to scour for duplicates. Then, head to the Styles section of the Home tab and navigate to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
This will bring up a window that allows you to customize the way that duplicate values are styled. This is an optional step, so if you’re just looking to find duplicates and don’t care about the color scheme, click OK to move on.
You should find that any duplicate entries are now highlighted in red. This should make it easy for you to delete unnecessary cells, or even at least understand at-a-glance which information is extraneous.
Finding Duplicates Using COUNTIF
Sometimes, you might want to check for duplicates across more than one sheet, and that requires a combination of conditional formatting and the COUNTIF formula. This technique will also work with older versions of Excel, and its Mac equivalent, Numbers.
This time, our database of email addresses is split across two different sheets in the same document. First, we need to set up a functionNeed to Learn Excel? 10 Experts Will Teach You for Free!Need to Learn Excel? 10 Experts Will Teach You for Free!Learning how to use Excel's more advanced features can be tough. To make it a little easier, we have tracked down the best Excel gurus who can help you master Microsoft Excel.Read More that can find duplicates in the column next to our first set of data.
We’re going to use COUNTIF, which uses the syntax COUNTIF(range, criteria). For my example, the function will read:
There’s a couple of important details to unpack here, so that you can repurpose this function for your own needs. First, there’s the reference to a different sheet — mine reads Sith! because of the subject matter, but you might need to use Sheet2! or whatever name you’ve given that particular sheet.
Second, there’s the criteria, which unlike the range does not use an absolute reference. This is because we want the range of cells being checked to remain the same every time, but we want each individual function to look for an individual email address.
Once that function is in place, use the bottom right hand corner of the cell to fill as many rows as are required.
As you can see in cell B5, the function will count any duplicates that it finds on the other sheet. We can make this information a bit easier to digest by applying another piece of conditional formatting to the column.
Highlight the entire column by clicking on its header, then head to the Home tab and navigate to Conditional Formatting > Highlight Cells Rules > Greater Than.
Enter 0 into the pop-up, and make any styling edits that you require using the drop/down. Press OK and you should see something like the results below.
The number of duplicates present on the other sheet will be counted and highlighted for your convenience. We can combine that with the first technique in this article to give a full picture of all the duplicates in the document.
With that information at your fingertips, removing any duplicates from your document should be a breeze.
Going Further with VBA
The techniques in this article will allow you to check your spreadsheet for duplicates, but this kind of task is even easier to accomplish once you’re a master of VBAHow You Can Make Your Own Simple App With VBAHow You Can Make Your Own Simple App With VBAFor those of you that would really love to be able to write your own application, but have never typed a single line of code before, I'm going to walk you through making your very...Read More.
VBA is an offshoot of Microsoft’s Visual Basic programming language that’s used across the Office suite and beyond, but it’s particularly potent when used in conjunction with Excel. VBA allows users to create macros that automate tasks, and otherwise do more with Excel.
As explained above, it’s certainly possible to find duplicates in Excel without learning VBA. However, if you find yourself needing to carry out this kind of task on a regular basis, it might be something that’s worth learning about. It’s an accessible language that can be used to great effect as part of the Microsoft Office suite, so check it out if you’re looking to expand your knowledge.
And remember, finding duplicates is just one scenario where conditional formatting comes in handy. You can also use it to highlight other types of data in a spreadsheet. Learn how to use conditional formatting in Excel and conditional highlighting in Numbers on Mac.
You might also want to learn how to add checkboxes, ratings, and sliders in Numbers and how to use formulas and functions in NumbersHow to Use Formulas and Functions in Numbers on MacHow to Use Formulas and Functions in Numbers on MacUse spreadsheets on your macOS? Learn how to use formulas and functions in Numbers to make your data easier to work with.Read More.
Explore more about: Microsoft Excel.
The tutorial explains how to remove duplicates in Excel 2016, Excel 2013, and Excel 2010. You will learn a few different techniques to find and delete duplicate values with or without first occurrences, get rid of duplicate rows, detect absolute duplicates and partial matches.
Although Microsoft Excel is primarily a calculation tool, its sheets are often used as databases to keep track of inventories, make sales reports or maintain mailing lists.
A common problem that occurs as a database grows in size is that many duplicate rows appear in it. And even if your huge database contains just a handful of identical records, those few duplicates can cause a whole lot of problems, for example mailing multiple copies of the same document to the same person, or calculating the same numbers more than once in a summary report. So, before using a database, it makes sense to check it for duplicate entries, to make sure you are not wasting time on repeating your efforts.
In a couple of our recent articles, we discussed various ways to identify duplicates in Excel and highlight duplicate cells or rows. However, there may be situations when you may want to ultimately eliminate duplicates in your Excel sheets. And that's exactly the subject of this tutorial.
Remove Duplicates tool - eliminate repeated rows
If you are using the recent version of Microsoft Excel 2007, Excel 2010, Excel 2013 or Excel 2016, you have a bit of advantage because these versions contain a built-in feature for finding and removing duplicates called, not surprisingly, Remove Duplicates.
This tool allows you to find and remove absolute duplicates (cells or entire rows) as well as partially matching records (rows that have identical values in a specified column or columns). To perform this, follow the below steps.
Note. Because the Remove Duplicates tool permanently deletes identical records, it's a good idea to make a copy of the original data before removing duplicate rows.
- To begin with, select the range in which you want to ddelete dupes. To select the entire table, press Ctrl + A.
- With the range selected, go to the Data tab > Data Tools group, and click the Remove Duplicates button.
- The Remove Duplicates dialog box will open, you select the columns to check for duplicates, and click OK.
- To delete duplicate rows that have completely equal values in all columns, leave the check marks next to all columns, like in the screenshot below.
- To remove partial duplicates based on one or more key columns, select only those relevant columns. If your table has many columns, the fastest way is to click the Unselect All button, and then select the columns you want to check for dupes.
- If your table does not have headers, clear the My data has headers box in the upper-right corner of the dialog window, which is usually selected by default.
Done! All duplicate rows in the selected range are deleted, and a message is displayed indicating how many duplicate entries have been removed and how many unique values remain.
Note. Excel's Remove Duplicates feature deletes 2nd and all subsequent duplicate instances, leaving all unique rows and first instances of identical records. If you want to eliminate duplicate rows including first occurrences, use one of the following solutions: filter out duplicates with 1st occurrences or utilize a more versatile Duplicate Remover for Excel.
Get rid of duplicates by copying unique records to another location
Another way to get rid of duplicates in Excel is separating unique values, and copying them to another sheet or a different workbook. The detailed steps follow below.
- Select the range or the entire table that you want to dedupe.
- Navigate to the Data tab > Sort & Filter group, and click the Advanced button.
- In the Advanced Filter dialog window, do the following:
- Select the Copy to another location radio button.
- Verify whether the correct range appears in the List Range This should be the range you've selected on step 1.
- In the Copy to box, enter the range where you wish to copy the unique values (it's actually sufficient to select the upper-left cell of the destination range).
- Select the Unique records only
- Finally, click OK, and the unique values will be copied to a new location:
Note. Excel's Advanced Filter allows copying the filtered values only to another location on the active sheet. If you want to copy or move unique values or duplicate rows to another sheet or a different workbook, you can easily do it using our Duplicate Remover for Excel.
How to remove duplicate rows in Excel by filtering
One more way to delete duplicate values in Excel is to identify them using a formula, filter out, and then delete duplicate rows.
An advantage of this approach is versatility - it lets you find and delete duplicate values in one column or duplicates rows based on values in several columns, with or without first instances. A drawback is that you will need to remember a handful of duplicate formulas.
- Depending on your task, use one of the following formulas to detect duplicates.Formulas to find duplicate values in 1 column
- Duplicates except 1st occurrences:
=IF(COUNTIF($A$2:$A2, $A2)>1, 'Duplicate', ')
- Duplicates with 1st occurrences:
=IF(COUNTIF($A$2:$A$10, $A2)>1, 'Duplicate', 'Unique')
Where A2 is the first and A10 is the last cell of the range to be searched for duplicates.Formulas to find duplicate rows- Duplicate rows except 1st occurrences:
=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1, 'Duplicate row', 'Unique')
- Duplicate rows with 1st occurrences:
=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, 'Duplicate row', 'Unique')
Where A, B, and C are the columns to be checked for duplicate values.For example, this is how you can identify duplicate rows except for 1st instances:For more information about using duplicate formulas, check out How to identify duplicates in Excel. - Duplicates except 1st occurrences:
- Select any cell within your table, and apply Excel's auto filter either by clicking the Filter button on the Data tab, or Sort & Filter > Filter on the Home tab.
- Filter out duplicate rows by clicking the arrow in the header of the 'Duplicate' column, and then check the 'Duplicate row' box.If someone needs more detailed guidelines, please see How to filter duplicates in Excel.
- And finally, delete duplicate rows. To do this, select the filtered rows by dragging the mouse across the row numbers, right click them, and select Delete Row from the context menu. The reason you need to do this instead of simply pressing the Delete button on the keyboard is that it will delete entire rows rather than just the cell content:
In a similar manner, you can find and delete a specific duplicate occurrence(s), for example only 2nd or 3nd instances, or 2nd and all subsequent duplicate values. You will find an appropriate formula and step-by-step instructions in this tutorial: How to filter duplicates by their occurrences.
Well, as you have just seen there is a number of ways to find and remove duplicates in Excel, each having its strong points and limitations. But what would you say if instead of those numerous duplicate removing techniques, you had one universal solution that wouldn't require memorizing a bunch of formulas and would work in all scenarios? The good news is that such a solution does exist, and I will demonstrate it to you in the next and final part of this tutorial.
Duplicate Remover - universal tool to find & delete duplicates in Excel
Unlike the inbuilt Excel Remove Duplicate feature, the Ablebits Duplicate Remover add-in is not limited to only removing duplicate entries. Like a Swiss knife, this multi-tool combines all essential use cases and lets you identify, select, highlight, delete, copy and move unique or duplicate values, absolute duplicate rows or partially matching rows, in 1 table or by comparing 2 tables, with or without first occurrences.
It works flawlessly on all operating systems and in all versions of Microsoft Excel, including Excel 2003, Excel 2007, Excel 2010, Excel 2013 and Excel 2016.
In a moment, you will see how quickly and efficiently you can find and eliminate duplicate rows or identical cells in your Excel sheets.
How to get rid of duplicates in Excel with 2 mouse clicks
Whenever you need to do away with repeated entries in Excel, perform these simple steps:
- Select any cell in the table that you want to dedupe, and click the Dedupe Table button on the AblebitsData tab. Your entire table will get selected automatically.
- The Dedupe Table dialog window will open, and all the columns will be selected by default. You pick Delete duplicates from the Select the action drop-down list and click OK. Done!
As you can see in the following screenshot, all duplicates rows except 1st occurrences are deleted:
Tip. If you want to remove duplicate rows based on values in a key column, leave only that column(s) selected, and uncheck all other irrelevant columns.
And if you want to perform some other action, say, highlight duplicate rows without deleting them, or copy duplicate values to another location, select the corresponding option from the drop-down list:
If you want more options, such as deleting duplicate rows including first occurrences or finding unique values, then use the Duplicate Remover wizard that provides all these features. Below you will find full details and a step-by-step example.
How to find and delete duplicate values with or without 1st occurrences
Removing duplicates in Excel is a common operation. However, in each particular case, there can be a number of specificities. While the Dedupe Table tool focuses on speed, the Duplicate Remover offers a number of additional options to dedupe your Excel sheets exactly the way you want.
![Mac Mac](/uploads/1/2/5/8/125860593/577210352.png)
- Select any cell within the table where you want to delete duplicates, switch to the Ablebits Data tab, and click the Duplicate Remover button.
- The Duplicate Remover wizard will run and the entire table will get selected.The add-in will also suggest creating a backup copy, and because you are going to permanently delete duplicates, we strongly advise that you check this box. Verify that the table has been selected correctly and click Next.
- Select what records you want to find and remove. The following options are available to you:
- Duplicates except 1st occurrences
- Duplicates including 1st occurrences
- Unique values
- Unique values and 1st duplicate occurrences
In this example, let's delete duplicate rows including 1st occurrences: - And now, select the columns to search for duplicates. Because our aim is to eliminate duplicate rows, be sure to select all the columns (which is usually done by default).
- Finally, select the action you want to perform on dupes and click the Finish button. In this example, we expectedly choose the Delete duplicate values option.
That's it! The Duplicate Remover add-in swiftly does its job and notifies you how many duplicate rows have been found and deleted:
That's how you can wipe duplicates off your Excel. I hope at least one of the solutions discussed in this tutorial will work for you. And if you are interested to try our deduplicate tools, I encourage you to download a fully-functional trial version, and let us know your feedback in comments.
If you like our Duplicate Remover add-in, we will be happy to make you an exclusive 15% off offer. Just use the following coupon code on the order form: AB14-BlogSpo. It is valid for Duplicate Remover as a separate product and as part of Ultimate Suite for Excel.
I thank you for reading and hope to see you on our blog next week!