When you’re working with a lot of data in Apple Numbers on your Mac, you may run into a situation where you have duplicates. These can be names, email addresses, products, colors or anything else.
If you have a lot of data in your spreadsheet, finding and removing duplicates can be challenging. After all, Numbers doesn’t have a “remove duplicates” button.
Here, we’ll show you a few different methods you can use to find duplicates and mark or remove them if you want.
Find duplicates in numbers with sorting
If you don’t have a lot of data in your spreadsheet, you can sort it and check for duplicates manually. This method can save you time in the long run, but only if your sheet doesn’t have thousands of rows.
Once you’ve sorted your data, you’ll be able to more easily locate duplicates and mark or delete them as you need to.
Find duplicates in numbers with functions
Numbers has two built-in functions you can use to find duplicates. These are the IF and COUNTIF functions. IF can display duplicates as True or False or whatever word you assign. COUNTIF will display how many times the item appears to indicate duplicates.
Find duplicates with the IF function
To illustrate how the function works, our example data will show the product names in column A, and the column headers in row 1 of our table. For this to work you will need to serialize your data according to the above methods.
Want to make the “duplicate” cell stand out even more? Try using conditional formatting in the numbers to make them a different color so you know which rows to delete.
If you prefer not to use your own wording and only display true for duplicates and false for non-duplicates, you can enter (A2)=(A1) in the editor. It works without adding IF before it.
You should now see numbers in that new column representing the number of times the item appears in your duplicate column. For our example in the screenshot above, you can see that the cap appears three times, the coat once, and the gloves twice.
Remove duplicates from numbers
You can manually remove duplicates by using the IF or COUNTIF function to find each cell that says Duplicate, True, or any number greater than 1, and remove them one by one. Sorting the formula column allows you to remove duplicates very quickly, but you need to be careful that you don’t remove the duplicates along with the original.
Merge and delete duplicates by numbers
You might want to remove duplicates, but you also don’t want to lose any data. For example, you might have inventory data for products, like in our example. That’s why you want to sum those amounts before removing duplicates. To do this, you must first merge the data. For this task, you’ll use both formulas and functions in Numbers.
Once you’ve finished merging the data, it’s important that you double-check to make sure everything was added correctly.
Remove duplicates
After you’ve merged the data, you’ll use the Sort action again to remove the duplicates. But first, you’ll need to create new columns to copy and paste the data results into as values so they’re not formulas.
Now, you can sort by the remaining Duplicates Indicator column using the sorting instructions at the beginning of this tutorial. You should see all your duplicates grouped together so you can delete those rows.
You can also delete original quantities and duplicate columns used for functions and formulas. This will leave you with no duplicate and merged data.