Duplicates & uniqueness

Find duplicates, remove duplicates, build sorted-unique lists, flag repeats, compare two lists for overlap or differences.

9 pages · 9 how-to

How-to guides

Remove duplicate values with one formula using UNIQUE, or strip them in place with the built-in tool. Works in Excel 365 and Google Sheets.

Check whether two columns match row by row, or find values in one column missing from another, using IF and COUNTIF.

Extract a sorted, deduplicated list from a column using SORT+UNIQUE in modern Excel and Google Sheets, or array formulas in older versions.

Learn when to use the UNIQUE formula versus the Remove Duplicates tool in Excel and Google Sheets to eliminate or surface distinct values.

Use COUNTIF to flag duplicate values in a list with a simple formula that marks any entry appearing more than once.

Identify which values appear more than once in a list using COUNTIF to add a Duplicate/Unique label to each row.

Find values that exist in one list but not the other using COUNTIF to label each item as Matched or Unmatched.

Identify values present in both lists using COUNTIF — any item with a count greater than zero exists in both lists.

Identify items in one list that are absent from another using COUNTIF — a count of zero means the value is missing.