Free tool

Find duplicates in a list

Paste names, e-mails, order numbers or anything else, one per line. Each repeated value is flagged next to the list, and the summary shows which values repeat.

fx=COUNTA(UNIQUE(FILTER(A2:A501,A2:A501<>"")))
ABCD
1ValuesDuplicate?Summary
2AnnaDuplicateValues10
3BenDuplicateDistinct values7
4ChloeDuplicateExtra copies3
5BenDuplicateValues that repeatAnna, Ben, Chloe
6David
7Emma
8annaDuplicate
9Frank
10ChloeDuplicate
11Gina

Blue cells are inputs: type a new value and the sheet recomputes. Click a result to see its formula.

The formulas

How it works, step by step

=IF(COUNTIF(A$2:A$501,A2)>1,"Duplicate","")
Flag each duplicate. COUNTIF counts how often the value in the row appears in the whole list. More than once means it is a duplicate. Fill the formula down next to your list, or use one formula for the whole column as in the sheet above, which spills down by itself.
=COUNTA(UNIQUE(FILTER(A2:A501,A2:A501<>"")))
Count the distinct values. FILTER drops the empty cells, UNIQUE keeps one of each value and COUNTA counts them. The list minus the distinct values is the number of extra copies you would remove.
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(A2:A501,COUNTIF(A2:A501,A2:A501)>1)))
List the values that repeat. Keep only the values whose count is above 1, take each of them once and join them into one line. Leave out TEXTJOIN to get them one per row instead.
=UNIQUE(FILTER(A2:A501,A2:A501<>""))
Keep only the first of each. To remove duplicates without deleting anything, let UNIQUE write the clean list next to the original, in the order the values first appear.
On live data

The same formula on your Sheets data

On a store's live orders, the number of orders minus the number of distinct customers counts the repeat orders: every order after a customer's first.

Sheets for Sheets

=data.orders.count(id)-data.orders.countd(customer_id)
Sheets reads the table live from the connected account: no export, no copy-paste, and the result updates when the data does.

Functions used here

COUNTIFCount where one criterion matchesUNIQUEDistinct rows or columns of an arrayFILTERRows or columns that meet a conditionCOUNTACount non-emptyTEXTJOINJoin texts with a delimiterEXACTWhether two texts are identical

More free tools

Percentage calculatorWork out a percent of a number, what percent one number is of another, and the percent change between two values, with the spreadsheet formula for each.Credit card interest calculatorSee the interest on a credit card balance this month, how many months your payment takes to clear it, and the total interest, with the formulas behind each number.Cubic feet calculatorCalculate cubic feet from length, width and height in feet or inches, convert to cubic yards and cubic meters, and get the volume of a cylinder, with the formulas.Box and whisker plot makerPaste your numbers to draw a box and whisker plot with the minimum, quartiles, median, maximum, IQR and outliers, each worked out by a spreadsheet formula you can copy.Multiply columns and add them upMultiply quantity by price on each row, sum the column, and get the same total in one SUMPRODUCT formula.Profit margin calculatorCalculate gross profit margin and markup from cost and price, or the selling price for a target margin or markup, with the spreadsheet formula for each.CAGR calculatorWork out the compound annual growth rate between a start and end value over any number of years, check it with RRI, and project future values with the same rate.
FAQ

Questions

How do I find duplicates in a spreadsheet?

Put =COUNTIF(A:A,A2)>1 next to the first value and fill it down: TRUE marks a value that appears more than once. Wrap it in IF to show a word instead, as in the sheet above. The same formula works in every common spreadsheet app.

Does it treat upper and lower case as the same?

Yes. COUNTIF and UNIQUE ignore case, so "Anna" and "anna" count as the same value. For a case-sensitive check, compare with EXACT: =SUMPRODUCT(--EXACT(A$2:A$501,A2))>1.

How do I count how many duplicates there are?

Count all values with COUNTA and the distinct ones with COUNTA(UNIQUE(...)). The difference is the number of extra copies: 10 names with 7 distinct means 3 extra copies.

How do I remove duplicates but keep the first one?

=UNIQUE(range) returns each value once, in the order it first appears. Copy its result and paste it as values if you want to replace the original list.

Do spaces matter?

Yes: "Ben" and "Ben " (with a trailing space) are different values. In a sheet, clean the list first with =TRIM(A2). The tool above trims the start and end of each pasted line the same way.

Run these formulas on live data

Sheets is a spreadsheet with 491 functions that reads your store, accounting or project data live, builds dashboards and e-mails reports on a schedule.

See the integrationsAll free tools