Lanternhill Studio · Bank CSV Categorizer

How to Find Duplicates in Google Sheets

Google Sheets has three practical ways to deal with duplicates: highlight them, pull out a unique list, or delete them. The examples assume your data has a header in row 1 and the values to check are in column A.

1. Highlight duplicates with conditional formatting

Select the range to check, e.g. A2:A1000.
Go to Format → Conditional formatting.
Under "Format cells if…", choose Custom formula is and enter:

=COUNTIF($A$2:$A$1000,$A2)>1

Pick a fill colour and click Done. Every value that appears more than once is highlighted, including the first copy.

To highlight whole rows where columns A and B both repeat, select A2:D1000 (all your columns) and use:

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1

The $ before the column letter keeps every cell in the row looking at column A and B.

Flag only the second and later copies

In a helper column (say E2) enter this and fill down. It shows TRUE from the second occurrence on, so the first copy stays FALSE:

=COUNTIF($A$2:A2,A2)>1

COUNTIF ignores upper/lower case, so "ACME" and "acme" count as duplicates. It does not ignore extra spaces. Run Data → Data cleanup → Trim whitespace first if needed.

2. List unique rows with UNIQUE

In an empty sheet or column, enter:

=UNIQUE(A2:D1000)

This returns each distinct row of A:D once, in the order it first appears. If the range runs past your data, the blank rows show up as one empty row; filter them out with:

=UNIQUE(FILTER(A2:D1000, A2:A1000<>""))

UNIQUE treats "ACME" and "acme" as different values. The result is a live formula; to keep it, copy it and use Edit → Paste special → Values only.

3. Delete duplicates with the built-in cleanup

Select the data, including headers.
Go to Data → Data cleanup → Remove duplicates.
Tick "Data has header row", then choose which columns must match for a row to count as a duplicate.
Click Remove duplicates. Sheets keeps the first copy and tells you how many rows it removed.

Make a copy of the sheet first (right-click the tab → Duplicate). In bank data, two identical charges on the same day can both be real, so check your statement before deleting them.

Frequently asked questions

How do I highlight duplicates in Google Sheets?

Use Format → Conditional formatting with the custom formula =COUNTIF($A$2:$A$1000,$A2)>1 so every value that appears more than once is highlighted.

How do I get a list without duplicates?

Use the UNIQUE function on the range; it returns each distinct row once and leaves the original data untouched.

Can Google Sheets delete duplicate rows for me?

Yes. Select the data and use Data → Data cleanup → Remove duplicates, which deletes repeated rows based on the columns you tick.

Skip the formulas: the free Remove Duplicates tool does this in your browser. Nothing is uploaded.

Want more than a quick fix?

Small Business Sheets Kit ($14 on Gumroad): six spreadsheet templates for small service businesses.
Small Business Bookkeeping Spreadsheet ($12 on Gumroad) for logging income and expenses all year.
Rather hand it off? Our Year-End Books Cleanup on Fiverr ($70 package) cleans and categorizes up to 1,000 transactions into a tidy sheet for your accountant.

Just starting out? Grab the free Starter Expense Tracker (pay what you want, $0 is fine).