How to Find and Remove Duplicates in Numbers on Mac

When you’re working with lots of data in Apple Numbers on your Mac, you may run into a situation where you have duplicates. This could be names, email addresses, products, colors, or something else.

If you have a lot of data in your spreadsheet, finding and removing those duplicates can be a challenge. Numbers doesn’t have a “remove duplicates” button, after all.

We’re going to show you a few different methods you can use to find duplicates and then either mark them or eliminate them, if you like.

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 might actually save you time in the long run, but only if you don’t have thousands of rows in your sheet.

Sort by One Column

If you only need to sort a single column in your spreadsheet to find duplicates, follow these steps:

Select the table of data by clicking anywhere in the table and then clicking the circle on the top left. This is to the left of column A.

Move your cursor over the column you want to sort by.

Click the arrow that displays next to the column letter and choose either Sort Ascending or Sort Descending from the action menu that appears.

Sort by Multiple Columns

If you need to sort by multiple columns to find duplicates in your spreadsheet, use these instructions instead:

Follow the same steps as above, but instead of selecting a Sort option in the action menu, click Show Sort Options.

The right-hand sidebar should open to the Sort menu.

Make sure that Sort Entire Table is selected in the first dropdown box.

In the Add a Rule dropdown box below the Sort rows by multiple columns header, and choose the first column you want to sort by. Below that pick Ascending or Descending.

The Sort rows header should now be Sort by, and another Add a Rule dropdown box should appear beneath the first. Choose another column from the Add a Rule drop down and select its sort order.

The data should sort automatically, but if not, click the Sort Now button at the top of the sidebar.

Once you sort your data you should more easily be able to spot the duplicates and mark or remove them as you need to.

Find Duplicates in Numbers With Functions

There are two built-in functions in Numbers that you can use to find duplicates. These are the IF and COUNTIF functions. IF can display duplicates as True or False, or a word that you assign. COUNTIF will display how many times an item appears to indicate duplicates.

Find Duplicates With the IF Function

To illustrate how the function will work, our example data will show product names in column A, and our table has column headers in row 1. You need to have sorted the row for this to work. Just follow these steps for your own spreadsheet:

Add another column or move to an empty column in your sheet where you want to add the duplicate indicator.

Click the cell in the second row of the new or empty column below the header, and open the Functions Editor by entering the Equal sign (=).

Enter IF(A2)=(A1),"Duplicate"," " in the editor. This will compare the cell with the one above it, and enter the word Duplicate if it’s a duplicate. If it’s not a duplicate, it will enter a space. You can change A2 and A1 to B2 and B1 depending on the column you want to search for duplicates within.

Click the checkmark to apply the formula.

Copy the formula to the subsequent cells by clicking the cell it’s in and dragging it down the column when you see the yellow circle on the border.

Want the “Duplicate” cells to stand out even more? Try using conditional formatting in Numbers to make them a different color, so you know which rows to delete.

If you prefer not to use a word of your own and just display True for duplicates and False for non-duplicates, you can simply enter (A2)=(A1) in the editor. This works without adding IF before it.

Find Duplicates With the COUNTIF Function

We’ll use the same example data as above using column A and our column headers. Here’s how to use the COUNTIF function to find duplicates:

Add another column or move to an empty column in your sheet where you want the duplicate indicator.

Click the cell in the second row below the header of the new or empty column, and open the Functions Editor by entering the Equal sign (=).

Enter COUNTIF(A,A2) in the editor. A is the column and A2 represents the row.

Click the checkmark to apply the formula.

Copy the formula to the subsequent cells the same way as Step 5 above.

You should now see numbers in that new column showing how many times the item in your duplicate column appears. For our example in the screenshot above, you can see that Cap appears three times, Coat once, and Gloves twice.

Remove Duplicates From Numbers

You can remove duplicates when using the IF or COUNTIF function manually, finding each cell that says Duplicate, True, or any number over 1 and deleting them one by one. Sorting the formula column lets you remove the duplicates much faster though, but you need to be careful you dn’t delete the originals as well as the duplicates.

Merge and Delete Duplicates From Numbers

Maybe you do want to remove the duplicates, but you also don’t want to lose any data. For instance, you might have inventory data for products, like in our example. So you want to total those amounts before you delete the duplicates. To do this, you’ll need to merge the data first. For this task, you’ll use both a formula and a function in Numbers.

Merge the Data

For our example, we’re going to leave the Duplicate indicator column we used with the IF function because we’ll need it later. Then, we’re going to add another column to the right for our Totals.

Click the cell in the second row of the new column below the header, and open the Functions Editor by entering the Equal sign (=).

Enter (B2)+IF(A2)=(A3),(D3),0 in the editor. (You can see a breakdown of these formula elements below.)

Click the checkmark to apply the formula.

Copy the formula to the subsequent cells.

Formula breakdown:

(B2) is the cell containing our first quantity.
+ will add that quantity to what follows.
IF(A2)=(A3) checks for a duplicate between the two cells.
(D3) is where the result of the quantity total will display.
0 will be added if there’s no duplicate.

Once you finish merging the data, it’s important that you double-check to be sure everything adds up correctly.

Delete the Duplicates

To remove the duplicates after you merge data, you’ll use the sort action again. But first, you need to create new columns to copy and paste the data results as values so they are no longer formulas.

Using the same example, we’ll copy and paste the Duplicate and Total columns:

Select both columns and then click Edit > Copy from the menu bar.

Select the new columns where you want to paste them and click Edit > Paste Formula Results from the menu bar.

Delete the columns with the formulas by selecting them again and right-clicking or clicking the column header arrow and choosing Delete Selected Columns.

Now you can sort by the Duplicate indicator column that you keep using the sorting instructions at the beginning of this tutorial. You should see all of your duplicates grouped together so you can delete those rows.

You can next also remove the original Quantity and Duplicate columns you used for the functions and formulas. This will leave you with no duplicates and merged data.

Note: Again, before you delete columns, rows, or other data from your spreadsheet, be sure that everything is correct and that you no longer need it.

Dealing With Duplicates in Numbers

It’s easy to have duplicates show up in your Numbers spreadsheets, whatever you’re calculating. Hopefully the steps we’ve outlined above help you quickly identify any duplicates, merge relevant data about them, and remove them for a cleaner sheet.

We also hope having this knowledge encourages you to use Numbers. It’s a great built-in Mac program that can do a lot, if you take the time to learn about it.

ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGirmp9fnby4edOoZJ%2Bhnpl6s7HMqK2eZZSqva21wpqrnqtdnrtuutSmmZ6qo2K6oq%2BO