Find Duplicates in Google Sheets: Methods That Work

Yes. In Google Sheets, you can highlight duplicate values with conditional formatting, count or list them with formulas, or remove repeated records with Data > Data cleanup > Remove duplicates. Choose highlighting or a formula when you want to inspect results first; use removal only after checking which rows will be deleted.

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. The definition depends on what you compare: two identical email addresses in one column are duplicate values, while two rows may count as duplicates only when every selected field—or a chosen set of key fields—matches.

Sheets checks the underlying cell values, not simply what looks alike on screen. For instance, a date displayed as 3/4/2026 may be stored as a date value, while text that looks like the same date may be stored as characters. Differences in extra spaces can also make two names look identical to a person while remaining different text. Conversely, COUNTIF matching is not case-sensitive, so it treats “North” and “north” as a match.

Before looking for repeats, decide whether you need to find:

Repeated values in one column, such as customer IDs or email addresses.
Rows where every relevant field matches.
Rows that share a key, such as the same order number, even if other columns differ.
Near-matches that differ in spelling or formatting, which require additional cleanup or review.

Highlight Duplicate Values Without Deleting Them

Conditional formatting is a useful first step because it marks repeated entries in place and leaves the data intact. In the Google Sheets web browser, select the cells to check, open Format > Conditional formatting, and set the Apply to range field to the intended range. Under Format cells if, choose Custom formula is and enter a formula based on the first cell in that range.

For example, with values in A2:A20 and the conditional-formatting range set to A2:A20, use =COUNTIF($A$2:$A$20,A2)>1. Choose a fill or text color in the formatting style panel. The formula counts how often each row’s value occurs in the full range; when the count exceeds one, Sheets applies the chosen style. If A2:A6 contains Maple, Cedar, Maple, Pine, Cedar, both Maple cells and both Cedar cells are highlighted.

This rule highlights every occurrence of a repeated value, including its first appearance. To mark only the second and later occurrences, use =COUNTIF($A$2:A2,A2)>1 with A2:A20 as the apply-to range. The second reference expands as the rule evaluates down the column, so the first Maple is not marked, but the next Maple is. If blank cells should never be highlighted, use =AND(LEN(A2)>0,COUNTIF($A$2:$A$20,A2)>1) instead. Adjust the range and the relative cell reference if your list starts somewhere other than row 2.

Count or List Repeated Values with Formulas

A formula can show a repeat count beside each entry, which is handy when you want a sortable or filterable audit column. With values in A2:A20 and the first result going in B2, enter =COUNTIF($A$2:$A$20,A2) in B2, then fill it down. A result of 1 means the value occurs once; 2 or more means it repeats. In the Maple example, each Maple row returns 2. The formula counts all matches in the checked range, not just earlier rows.

To return a list containing each value once, Sheets also has the UNIQUE function. With the source list in A2:A20 and the formula placed in an empty cell such as C2, use =UNIQUE(A2:A20). Sheets expands the results into cells below the formula. UNIQUE is useful for making a distinct list, but it does not by itself label which source rows were duplicates or tell you how many times an item appeared.

For a quick check of whether a particular value repeats, COUNTIF is usually clearer than building a separate list. Remember that formula results may change when the source data changes. If you plan to sort or delete source rows, keep the formula range aligned with the data and check that the formulas still refer to the intended cells.

Check Duplicate Rows Across Multiple Columns

Sometimes a value is not unique on its own, but a combination of fields should be. For example, a customer may legitimately appear many times in a sales table, while a repeated combination of customer ID and invoice number may indicate a duplicate record.

With customer IDs in A2:A20 and invoice numbers in B2:B20, apply conditional formatting to the records and use =COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2)>1. COUNTIFS counts rows where both the ID and invoice number match the current row. A result above one means that pair occurs more than once. The formula only checks those two columns: if amounts, dates, or notes differ, the rows can still be marked as duplicates under this key-based definition.

For a complete-row comparison, include every column that matters in the criteria. Be deliberate about which fields define a true duplicate. Two rows with the same name but different order dates may be separate transactions; two rows with the same unique order ID may be duplicates even if a note was edited. If you have a header row, start the data range below it so the header is not treated as a record.

Remove Duplicates with the Data Cleanup Tool

When you are ready to delete repeated records, first make a copy of the sheet or preserve the original data in another tab. Then select the table range and choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the selection has a header row and choose the columns Sheets should use when deciding whether records repeat. Review the selection carefully before confirming.

The tool removes duplicate rows from the selected range and keeps one occurrence. If you select only the email column as the comparison key, two rows with the same email can be considered duplicates even when their names, dates, or other details differ. Removing duplicates based on that selection can therefore discard information from a row. Select all columns when you mean to remove only records whose complete selected contents repeat; select a smaller set only when that set is deliberately your unique key.

This is a destructive cleanup of the data in the selected range, unlike conditional formatting or a helper formula. Do not assume that visually similar values will be treated as duplicates under every possible definition, especially when there are extra spaces, different data types, or inconsistent formatting. If the outcome matters, test the method on a copied range first and compare the row count and surviving records afterward.

Clean Inconsistent Text Before Comparing

Extra spaces are a common reason a duplicate check misses a match. A cell containing “Maple” and one containing “Maple ” may look the same, but the second has a trailing space. A helper column can standardize ordinary spacing before you compare the results. With the original text in A2, place =TRIM(A2) in B2 and fill it down. TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words.

If you want to ignore capitalization as well, use =LOWER(TRIM(A2)) in a helper column, again assuming the source value is in A2. Then run your duplicate check against the cleaned helper values. Keep the original column until you have verified the normalized results: cleaning can make entries match that were intentionally distinct, such as names where capitalization carries meaning in a particular dataset.

TRIM does not solve every text-cleaning issue. Nonbreaking spaces copied from web pages and other unusual characters may remain, and punctuation differences such as a hyphen versus a space are still differences. If values come from imports or copied documents, inspect suspect cells and standardize the source consistently. Sheets does not infer that two differently spelled names refer to the same person; those are near-duplicates and may need human review or a separate matching approach.

Choose the Method That Fits Your Goal

There is no single best duplicate tool for every sheet. Match the method to whether you need a reversible inspection, a count, a unique list, or a permanent cleanup:

Use conditional formatting to scan a column or table visually while preserving all rows.
Use COUNTIF or COUNTIFS when you need repeat counts or a rule based on one or more fields.
Use UNIQUE when you need a separate list with one copy of each value, not a report of which source rows repeat.
Use Remove duplicates when you have settled the comparison criteria and are ready to delete repeated rows.

These are built-in Google Sheets features and formulas, so a basic duplicate check does not require an add-on. If you use a third-party add-on or script for more complex matching, review its requested access and the data it will process, particularly for customer or employee records. For sensitive data, use only tools and accounts approved for that information.

Common Problems and Practical Checks

If nothing is highlighted, verify that the custom formula references the first cell in the Apply to range. For a range beginning at A2, use A2 in the formula; a formula written for A1 can shift the test and produce confusing results. Check that the absolute range markers are in place around the full data range, and make sure the rule applies to the rows you expect.

If too many cells are highlighted, check whether blanks are included, whether the comparison range includes headers or unrelated data, and whether your intended definition is a repeated value or a repeated row. If too few appear, inspect trailing spaces, nonbreaking spaces, and differences between numbers stored as numbers and digits stored as text. Sorting can help reveal patterns, but it does not by itself prove that two records should be merged.

Finally, distinguish finding duplicates from deciding what to do about them. A repeated phone number may be valid for a family or shared business line. A repeated transaction ID may be a serious data-quality problem. The spreadsheet can show that a value matches according to your rule; it cannot determine whether the match is an error without context.

Frequently Asked Questions

Can Google Sheets automatically highlight duplicates?
Yes. Select the range, go to Format > Conditional formatting, choose Custom formula is, and use a COUNTIF-based rule such as =COUNTIF($A$2:$A$20,A2)>1 for values in A2:A20. The formatting updates as the cells change.

Does Remove duplicates keep the first occurrence?
The tool removes repeated rows from the selected range and retains one occurrence. Check the selected columns and make a copy first, especially if rows with the same key contain other information you need to keep.

Can I find duplicate rows using two columns?
Yes. Use COUNTIFS with both columns as criteria, or select those columns in the Remove duplicates dialog if that pair defines a duplicate for your task. A match on two fields does not mean every other field in the rows also matches.

Are duplicate checks case-sensitive?
COUNTIF matching is not case-sensitive, so text such as “Maple” and “maple” is counted as a match. If capitalization is meaningful in your data, do not rely on that check alone; review the records and choose a comparison method appropriate to the values.

Can I find duplicates without deleting anything?
Yes. Conditional formatting, a COUNTIF or COUNTIFS helper column, and a UNIQUE result list all let you inspect data without using Remove duplicates. Keep the source range unchanged until you are confident about any cleanup decision.

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.