Google Sheets Duplicate Entries: Find, Highlight, Remove

To find duplicate entries in Google Sheets, use Format > Conditional formatting to highlight repeats, or choose Data > Data cleanup > Remove duplicates to delete them. For a non-destructive check, a COUNTIF formula can mark which values appear more than once.

What Google Sheets Counts as a Duplicate

A duplicate is a value or record that appears more than once in the data you are checking. What counts as a match depends on what you select and how you compare it. If a column contains customer email addresses, for example, you may want to find repeated emails. If a table contains orders, you may instead want to find rows where several fields—such as order number and product—match.

It helps to decide whether you want to identify every copy of a repeated value or only later occurrences. If “Ana” appears twice, highlighting every instance marks both cells. A formula that counts from the first row down can instead leave the first “Ana” alone and mark only the second one. These approaches answer different questions, so choose based on whether you are reviewing records or preparing a list with one entry per item.

Google Sheets is a browser-based spreadsheet, and the steps below use its web interface. The commands are not the same as Excel’s menu paths, even though both programs offer ways to find duplicates.

Highlight Duplicate Values with Conditional Formatting

Conditional formatting is a useful first step because it shows repeated values without changing or deleting the data. To highlight duplicates in column A, with a header in A1 and entries in A2:A100, select A2:A100 and open Format > Conditional formatting. Under “Format rules,” choose “Custom formula is,” enter the formula below, select a formatting style, and click Done.

With entries in A2:A100 and the rule applied to A2:A100, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). It highlights each nonblank value that occurs more than once in the range. For example, if Ana appears in A2 and A4, both cells are highlighted; a value that appears just once is not.

The dollar signs keep the range fixed as Sheets checks each row, while the reference to A2 changes for each cell in the applied range. The AND condition prevents empty cells from being treated as duplicates. If your data extends beyond row 100, adjust the range in both the rule and its “Apply to range” setting.

For duplicates across a different single column, replace A with that column’s letter in the range and cell reference.
For a list that starts on another row, use that first data row in the formula and the applied range.
To check more than one column, define what a duplicate means first. Comparing each column separately does not necessarily identify duplicate records.

Use a Formula to Label Repeats

A helper column gives you a sortable, filterable result instead of relying on color. Suppose names are in A2:A6. In B2, enter a formula, then fill it down through B6. It labels a value as a duplicate when it has appeared earlier in the list.

With names in A2:A6 and the formula starting in B2, use =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")). If A2:A6 contains Ana, Ben, Ana, Cara, Ben, the labels are First occurrence, First occurrence, Duplicate, First occurrence, Duplicate. This is useful when you want to preserve the first copy while identifying later ones.

Unlike the conditional-formatting rule that checks the whole list, this formula checks from the top of the list through the current row. The growing range is intentional: in row 4, for example, COUNTIF checks A2:A4. If you sort the data, the order changes and so can which copy is labeled “First occurrence.” If that matters, sort using a stable date or ID field, or make a copy before rearranging records.

For a compact list containing each distinct value once, Sheets also has the UNIQUE function. With source values in A2:A6, enter =UNIQUE(A2:A6) in an empty cell outside the source range. For the example list Ana, Ben, Ana, Cara, Ben, the result is Ana, Ben, Cara in order of first appearance. UNIQUE creates a result elsewhere; it does not remove or edit the original entries.

Remove Duplicate Rows with Data Cleanup

When you have reviewed the data and are ready to delete repeated records, use the built-in cleanup tool. Make a copy of the sheet or the relevant range first, especially if the rows contain information that would be difficult to recreate.

Select the range of data to clean, including all columns that belong to each record. Include the header row if present.
Choose Data > Data cleanup > Remove duplicates.
In the dialog, indicate whether the selected data has a header row and choose the columns Sheets should use to identify duplicates.
Review the selection carefully, then run the cleanup and check the confirmation message and remaining records.

The selected columns determine what constitutes a duplicate row. If you select only the email column, two rows with the same email may be treated as duplicates even if their names or dates differ. If you select every column, rows generally need to match across those selected fields to count as duplicate records. Choose columns that reflect the meaning of a unique record in your data—not simply whichever columns are easiest to select.

Removing duplicates changes the selected data, so it is different from highlighting or using UNIQUE. Sheets keeps a copy of matching data and removes duplicates from the selected range; it is not a general-purpose merge tool that combines information from separate rows. If one repeated row has a phone number and another has a newer address, clean or reconcile those fields before deleting either record. Undo may help immediately after an accidental cleanup, but a saved copy is a safer fallback.

Check Duplicate Records Across Multiple Columns

Two rows can share one field without being the same record. For example, a customer may place several orders, so repeated customer names do not necessarily mean duplicate orders. A better key might be an order ID, or a combination such as email address plus order date and product.

For a quick review of duplicates based on a combination of fields, create a helper key in an unused column. With first name in A2:A6 and last name in B2:B6, enter a combined value in C2 and fill down: =A2&"|"&B2. The separator makes the joined text easier to inspect. You can then apply the single-column conditional-formatting rule to the helper values in C2:C6, or use the cleanup tool with the appropriate original columns selected.

Be cautious about using a simple joined key if field values themselves can contain the separator, or if formatting differences matter. For important records, a dedicated unique ID is more reliable than a person’s name. Names can be shared by different people, and one person may be entered in several ways. A combination of fields is only as dependable as the fields and rules used to create it.

Why Apparent Duplicates May Not Match

Repeated-looking entries can differ because of spaces, punctuation, spelling, or data type. “alex@example.com” and “ alex@example.com ” may look alike on screen but contain extra spaces. A number stored as text may also behave differently from a numeric value in some checks. Before deleting records, inspect examples and decide whether to standardize the data.

Google Sheets’ COUNTIF comparison is not case-sensitive for ordinary text matching, so values such as “North” and “north” are generally counted as matches by that approach. If capitalization carries meaning in your data, do not assume a standard COUNTIF rule will distinguish it. Conversely, a difference in spaces or punctuation can prevent values from matching even when a person considers them equivalent.

Use Edit > Find and replace to locate a known extra space or inconsistent spelling, but review replacements before applying them broadly.
Use TRIM in a helper column to remove leading and trailing spaces and reduce repeated spaces between words. It does not resolve every possible invisible character.
Compare dates and numbers using consistent formats, and check whether imported values are stored as text.
Inspect a sample of flagged records before deleting anything, particularly when the data came from a form, export, or multiple contributors.

Cleaning the source data can reveal duplicates that were previously hidden by inconsistent formatting. It can also cause distinct values to become identical after standardization. Keep the original values or a backup if you need to audit those changes later.

Choose the Right Method for the Job

Use highlighting when you want to scan a list visually and decide case by case. Use a helper formula when you need a clear label for filtering, sorting, or review. Use Remove duplicates when you have established the matching fields and are ready to change the selected data. Use UNIQUE when you need a separate list of distinct values while retaining the source.

These options are not interchangeable. Conditional formatting displays a visual warning but leaves records in place. A helper formula adds information but does not remove rows. UNIQUE returns a distinct list without cleaning the input. Remove duplicates alters the selected range, making it the option that calls for the most care.

For shared spreadsheets, agree on the definition of a duplicate before cleaning a team’s data. A short note such as “one row per order ID” prevents someone from removing legitimate repeat purchases simply because the same customer appears several times. If the sheet is used for ongoing data entry, consider adding validation or a process that checks unique IDs as records arrive; a one-time cleanup will not stop new duplicates from being entered later.

Frequently Asked Questions

How do I highlight duplicates in Google Sheets?

Select the data range, open Format > Conditional formatting, choose “Custom formula is,” and use a COUNTIF rule such as =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) for entries in A2:A100. Adjust the references to match your range, choose a formatting style, and click Done.

Does Remove duplicates delete the first entry too?

The cleanup command removes duplicate entries from the selected data while retaining a copy of matching data. Do not treat it as a way to merge records or choose the most complete version automatically. Review the rows and make a backup before running it.

Can Google Sheets find duplicate rows, not just duplicate cells?

Yes. Select the full table and, in the Remove duplicates dialog, choose the columns that define a matching record. For a non-destructive check, build a helper key from the relevant fields and look for repeated keys. A match in one column alone may not mean the whole row is a duplicate.

How can I keep the first entry and mark only later duplicates?

Use a helper formula that counts from the first data row through the current one. For entries in A2:A100, a formula such as =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")) labels later appearances as duplicates when filled down.

What is the difference between UNIQUE and Remove duplicates?

UNIQUE returns a separate list with repeated values omitted and leaves the source data unchanged. Remove duplicates cleans the selected range itself. Choose UNIQUE to create a distinct output list; choose the cleanup command only when you intend to remove records from the data you selected.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.