Check Duplicates in Google Sheets: Methods That Work

To check duplicates in Google Sheets, select the cells and choose Format > Conditional formatting, then use a custom formula such as =COUNTIF($A$2:$A,A2)>1 for values in column A. You can also use a formula to count repeats, create a unique list, or remove duplicates with Data > Data cleanup > Remove duplicates.

What Counts as a Duplicate?

A duplicate is a value or record that appears more than once in the data you are checking. The right method depends on what should count as “the same.” In a list of email addresses, you may only need to compare one column. In a contact table, a repeated first name is not enough to identify a duplicate; you might want to compare both the name and email address.

Google Sheets compares the selected cell values or columns, not the meaning of the information. For example, “Jordan Lee” and “Jordan Lee ” may look identical on screen but differ because the second value has a trailing space. Similarly, repeated customer names may represent separate people, while repeated order IDs may indicate an actual data problem. Decide what makes a record unique before highlighting or deleting anything.

Duplicate values: the same value appears more than once in one column.
Duplicate rows: the values in the chosen fields match another row.
Repeated records by key: a particular field, such as an email or ID, appears more than once even if other fields differ.

Highlight Duplicates with Conditional Formatting

Conditional formatting is a good first check because it marks repeated values without changing the data. For example, with values in A2:A, select that range, open Format > Conditional formatting, and choose Custom formula is in the formatting rules panel. Enter the formula below, choose a formatting style, and click Done.

With values in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:$A,A2)>1. The dollar signs keep the checked range fixed as the rule evaluates each row; A2 remains relative so the formula checks the current cell. This rule highlights every occurrence of a repeated value, including its first appearance.

If you want to highlight only the second and later occurrences, use a growing range instead. With values in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:A2,A2)>1. The first instance has a count of one, while each later instance makes the count greater than one. This can be useful when reviewing a list from top to bottom, but it does not flag the first row in each duplicate group.

Conditional formatting is dynamic: if you edit a value, the highlights update according to the rule. It does not delete or rearrange any data. If your sheet has a header in row 1, start the selected range at row 2 so the header is not treated as a value.

Count Repeated Values with a Formula

A helper column makes duplicate checks easy to sort, filter, or report. In B2, next to a list in A2:A, enter a count formula and fill it down. With the list in A2:A, use =COUNTIF($A$2:$A,A2). For a value that appears three times, each matching row returns 3; a value that appears once returns 1.

To label rows rather than show the count, use an IF formula. With values in A2:A, enter this in B2 and fill it down: =IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique"). Both copies of a repeated value will be labeled Duplicate. This is useful when a reviewer needs to filter the helper column and inspect all matching rows.

COUNTIF is not case-sensitive in Google Sheets, so entries such as “ACME” and “Acme” are generally counted together. Blank cells can also make checks confusing: a repeated blank is technically a repeated value, even though it may not be a meaningful duplicate. To avoid labeling empty rows, add a blank check. With values in A2:A, use =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique")) in B2 and fill down. It leaves the result blank when A2 is blank.

Check Duplicate Rows Across Multiple Columns

When a record is identified by more than one field, compare those fields together. For instance, a table might contain a first name in column A and an email address in column B. Comparing the name alone could wrongly flag two different people with the same name.

With names in A2:A and email addresses in B2:B, apply a conditional-formatting rule to the rows you want to review and use =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1. COUNTIFS counts rows where both the name and email match the current row. If the same name appears with a different email, the row is not counted as a match by this rule.

For a broader record, add more field-and-criteria pairs to COUNTIFS. Include only columns that define identity for your task: adding a date or notes column may cause otherwise repeated records to appear unique. Conversely, omitting a key field can flag legitimate separate records. Test the rule against a few known examples before relying on it across a large sheet.

Create a List of Unique Values

If you want a clean list without altering the source, use UNIQUE in an empty area or on a separate sheet. With source values in A2:A, enter =UNIQUE(A2:A). Google Sheets returns each distinct value once, keeping the order in which values first appear. The result expands into cells below the formula, so make sure those cells are empty.

UNIQUE is useful for a quick inventory of categories, names, or IDs, but it does not tell you how many times each value occurred. It also does not remove records from the original data. If you want the unique output in alphabetical order, with source values in A2:A, use =SORT(UNIQUE(A2:A)). That changes the displayed order of the result, not the source list.

Keep the source range and output area separate. If existing content blocks the expanding result, Sheets may show an error instead of overwriting those cells. A unique list is a good non-destructive review step before deciding whether to clean the original table.

Remove Duplicates from the Source Data

To delete repeated records, select the data range and choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the selected data has a header row, then choose which columns should be checked. Click Remove duplicates to proceed. Google Sheets reports how many duplicate rows it removed and how many unique rows remain.

When you select several columns, Sheets treats rows as duplicates when the values match across the selected columns. If only one column is selected, repeated values in that column can be removed even when other columns contain different details. That may leave you with an arbitrary-looking record for each value, so choose the range and columns with care. The tool changes the selected data; it is not just a highlight or a temporary view.

Before removing anything important, make a copy of the sheet or duplicate the tab. Check whether the first row is a header and whether the selected columns correctly represent a unique record. If you need to preserve every row for auditing, use conditional formatting or a helper formula instead. You can usually undo a recent change with Ctrl+Z on Windows or Command+Z on Mac, but a separate copy is a safer backup for consequential cleanup.

Common Problems and Ways to Avoid Them

Leading or trailing spaces: “North Shop” and “North Shop ” can be treated as different text. A helper column using TRIM can clean ordinary extra spaces. With source text in A2, enter =TRIM(A2) in B2 and fill down. Review the cleaned output before replacing source data, since TRIM also reduces repeated spaces within text.
Wrong starting row: If your data has a header, use row 2 as the first data row in both the applied range and formula references. A mismatch can highlight the wrong cells or include the heading in the comparison.
Blank rows: A formula may count blanks as repeated values. Add a check for an empty cell, or exclude empty rows from the range if blanks should not count.
Only one column checked: A repeated surname or product name may be legitimate. Use a combination of relevant columns when a whole record, rather than one field, defines a duplicate.
Similar-looking values: Dates, numbers stored as text, punctuation, and invisible spaces can make values behave differently than expected. Inspect the underlying cells and standardize formats when appropriate.

Google Sheets’ usual COUNTIF comparison is case-insensitive. If capitalization must distinguish values, do not assume a COUNTIF rule will enforce that distinction; use a case-sensitive approach based on EXACT and verify it against representative data. Also note that a formatted number can look different from its stored value: for example, a date may display in a chosen style while the cell contains a date value. Matching should be based on the data rules your work actually requires.

Which Duplicate-Checking Method Should You Use?

Choose the method according to whether you need to inspect, summarize, or change the data:

Use conditional formatting when you want to spot duplicates visually while retaining every row.
Use COUNTIF or COUNTIFS when you need counts, labels, filtering, or a repeatable audit column.
Use UNIQUE when you need a separate list of distinct values and want to leave the source untouched.
Use Remove duplicates when you have confirmed the matching criteria and want duplicate rows deleted from the selected data.

These ideas also exist in Excel, but menu paths and some details differ. In Google Sheets, conditional formatting is under Format, and duplicate removal is under Data > Data cleanup. Excel for Microsoft 365 has its own Conditional Formatting and Remove Duplicates controls on the ribbon. A formula may be similar across the programs, but instructions for one should not be assumed to describe the other’s interface exactly.

Frequently Asked Questions

How do I highlight duplicates in Google Sheets?
Select the data range, choose Format > Conditional formatting, select Custom formula is, and enter a COUNTIF rule such as =COUNTIF($A$2:$A,A2)>1 for column A starting at row 2. Pick a style and click Done.

How do I find duplicates in two columns?
Use COUNTIFS to compare both fields on the same row. For names in A and email addresses in B beginning in row 2, a custom rule can use =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1. Change the columns to match the fields that define your record.

Does Remove duplicates delete the first occurrence too?
The tool keeps one instance of a repeated record and removes the other matching rows from the selected data. Make a copy first if you need a recoverable original or want to inspect which records match before deleting them.

Can I check duplicates without deleting them?
Yes. Conditional formatting, COUNTIF or COUNTIFS helper columns, and UNIQUE results let you review repeated or distinct values without using the Remove duplicates command on the source range.

Why does Google Sheets not recognize two values as duplicates?
They may differ because of extra spaces, punctuation, cell contents, or the fields included in your comparison. Check the values directly, standardize them if appropriate, and confirm that the formula’s range and starting row match the data.

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.