Google Sheets: Check for Duplicates in a Column

To check a Google Sheets column for duplicates, enter a COUNTIF formula beside the first data row: if your values are in A2:A1000, put =COUNTIF($A$2:$A$1000,A2)>1 in B2 and fill it down. Rows containing a value that appears more than once will show TRUE; you can also highlight those values with conditional formatting instead of adding a helper column.

What the duplicate-check formula does

COUNTIF counts how many cells in a range meet a condition. In this example, the range is A2:A1000 and the condition is the value in the current row, A2. The dollar signs lock the range when you copy the formula down, while A2 changes to A3, A4, and so on. The greater-than comparison returns TRUE when the count is above one and FALSE when the value appears only once.

For example, if A2:A6 contains Cedar, Maple, Cedar, Birch, and Maple, the results beside those rows are TRUE, TRUE, TRUE, FALSE, and TRUE. Both occurrences of each repeated word are marked. The formula checks the entire specified range, so it can identify the first occurrence as a duplicate too.

To set it up, leave your header out of the range and use a bounded range that covers the rows you expect to use:

Click B2, or the first empty cell beside your data.
Enter the formula, changing the column letter and range to match your sheet.
Press Enter, then copy or drag the formula down alongside the data.
Filter the helper column for TRUE if you want to view only repeated entries.

A bounded range such as A2:A1000 is easy to understand and works in both Google Sheets and Excel. If your list grows beyond the last row in the range, expand the formula’s range so new entries are included.

Highlight duplicates without a helper column

Conditional formatting colors matching cells automatically as data changes. Select the data cells, then choose Format > Conditional formatting. In the Conditional format rules panel, set “Apply to range” to A2:A1000, open the “Format cells if” menu, and choose “Custom formula is.” Enter the formula below, choose a formatting style, and click Done.

With values in A2:A1000 and the formatting range starting at A2, use this custom formula to highlight all nonblank values that occur more than once: =AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1).

The first A2 is intentionally relative: as Sheets applies the rule to each row, it evaluates A3, A4, and later cells. The range in COUNTIF stays fixed. The AND check excludes empty cells, which matters because COUNTIF can otherwise count other blank cells as matches. Conditional formatting changes only how cells look; it does not delete data or change the underlying values.

Mark only the second and later occurrences

Sometimes you want to keep the first occurrence as the original and flag only later repeats. This is useful when reviewing an import or deciding which entries may be safe to remove. Use a running range that begins at the first data row and ends at the current row.

With data in A2:A1000, put =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. The first Cedar returns FALSE because it has appeared once so far; a later Cedar returns TRUE. The range expands as the formula moves down, so each row is compared only with entries above it and itself.

This version answers a different question from the whole-column check. It identifies subsequent occurrences, not every member of a duplicated group. If the first appearance is later deleted or the rows are reordered, which instance is treated as “first” can change. For an audit, keep the original data and use a helper column or a copy of the sheet before removing anything.

Choose a method that fits the task

The right method depends on whether you need a quick visual scan, a filterable list, a cleaned dataset, or a separate list of distinct values. These approaches are related, but they do not produce the same result.

COUNTIF helper column: Best when you need TRUE/FALSE results you can filter, sort, or use in further calculations.
Conditional formatting: Best for a quick visual review that updates when values change. It leaves the data intact but is less convenient as a filter criterion.
Data cleanup: Best when you have decided to remove repeated records. It changes the selected data, so review the selection and keep a copy first.
UNIQUE: Best when you want a separate output containing one copy of each value, rather than a report identifying which source rows are duplicates.

To produce a distinct list from A2:A1000, enter =UNIQUE(A2:A1000) in an empty cell. The formula returns the distinct entries in a separate output area; it does not remove duplicates from the source column. Make sure there is room for the results to expand, because existing content below or beside the output may prevent the list from displaying fully.

Remove duplicates carefully

Google Sheets includes a built-in removal tool. Select the range or table you intend to clean, then choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the selection has a header row and choose which columns should be checked. Click Remove duplicates only after checking that the selected columns match your definition of a duplicate.

If you select just column A, Sheets compares values in that column. If you select a multi-column table and include several columns in the comparison, a repeated value in A alone may remain when the corresponding values in other columns differ. Conversely, treating a whole row as a duplicate usually means comparing all relevant fields, not just a name or identifier. The tool removes records from the selected range; it is not merely a highlight or a report.

For a safer workflow, duplicate the tab or copy the original data to another location before cleanup. Then check how many records were removed and spot-check important fields. This is particularly valuable when duplicate-looking names may refer to different people or when two transactions have the same amount but different dates.

What counts as a duplicate?

A formula can only apply the comparison you intend if the values are consistent. COUNTIF is case-insensitive for ordinary text, so “cedar” and “Cedar” are generally treated as matches. Extra spaces can make values that look alike on screen behave differently: “Maple” and “Maple ” may not compare as the same text. Hidden characters copied from websites or exports can cause similar confusion.

Before flagging or deleting records, decide whether differences in capitalization, punctuation, spacing, or formatting matter. If they should not matter, create a cleaned helper column first. For example, TRIM removes leading and trailing spaces and reduces repeated spaces between words. If a source mixes capitalization, LOWER can standardize it. Compare the cleaned values rather than overwriting the original until you have confirmed the result.

Also decide whether “duplicate” means a repeated cell value or a repeated record. Two rows may share an email address but have different status information; that is a duplicate under an email-only rule, but not necessarily a duplicate under a whole-row rule. State the key field clearly before using the removal tool.

Common problems and how to fix them

Blank rows are highlighted: Add the nonblank check shown in the conditional-formatting formula, or limit the applied range to rows containing data.
The header is marked: Start the range at row 2 if row 1 contains headings. For conditional formatting, make sure the applied range also starts on the first data row.
New entries are missed: Extend a bounded range such as A2:A1000, or choose a range large enough for expected growth. Confirm that both the formula range and conditional-formatting range cover the new rows.
Similar-looking entries are not matched: Check for leading, trailing, or nonprinting characters. A cleaned comparison column can help reveal these differences.
Every row seems to match: Check that the formula’s COUNTIF range uses dollar signs to stay fixed and that the cell reference for the current row is correct.
Conditional formatting colors the wrong cells: Confirm the applied range’s first row matches the row number in the custom formula. A formula written for A2 will be offset if the applied range begins at A1 or A3.

One frequent misconception is that highlighting duplicates removes them. It does not: conditional formatting only applies a visual style. Similarly, a TRUE/FALSE helper formula reports a match but does not alter the source values. Removal requires an explicit cleanup action or editing the data yourself.

Frequently Asked Questions

How do I find duplicates in one Google Sheets column?
Use COUNTIF in a neighboring column and fill it down, or apply a custom conditional-formatting rule to the column. A COUNTIF result above one means the value appears multiple times within the range.

How do I highlight duplicates but ignore blank cells?
Use conditional formatting with a custom formula that checks the current cell is not blank and that COUNTIF finds more than one match. Set the applied range to start on the same row referenced in the formula.

Can Google Sheets show only the second occurrence?
Yes. Use a running COUNTIF range from the first data row through the current row, then test whether its count is greater than one. The first instance is not flagged; later instances are.

Does Google Sheets treat uppercase and lowercase as different?
COUNTIF is generally case-insensitive for text, so capitalization alone does not distinguish values. If case differences are meaningful in your data, verify the comparison method before relying on COUNTIF.

How do I remove duplicate rows in Google Sheets?
Select the relevant range and use Data > Data cleanup > Remove duplicates. Choose the header setting and comparison columns carefully, and make a copy before removing records you may need to restore.

Google Sheets and Excel differences

The COUNTIF formulas in this guide use standard comma-separated syntax and also work in current Excel versions for the stated bounded ranges. The interface paths do differ: Google Sheets uses Format > Conditional formatting and Data > Data cleanup > Remove duplicates. Excel for Microsoft 365 on Windows has its own Conditional Formatting and Remove Duplicates commands, so do not assume the menu names or dialog steps are identical. In Google Sheets, conditional formatting rules are managed in the side panel, and a custom formula is entered there rather than in a worksheet cell.

For a quick check, start with COUNTIF. Choose conditional formatting when you want an at-a-glance view, a running count when only later occurrences matter, and the cleanup tool only when you intend to change the data. Taking a moment to define what counts as a duplicate—and whether blanks or near-matches should count—prevents many incorrect flags and accidental deletions.

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.