Highlighting duplicates in Google Sheets uses conditional formatting with a custom formula. The built-in colour rules cannot find repeats on their own, but one short COUNTIF formula can. This page covers one column, several columns, two sheets, and how to remove the highlight again.
Select the range, for example A2:A100.
Open Format > Conditional formatting.
Under "Format rules", choose "Custom formula is".
Paste a formula from below and pick a fill colour. Click Done.
The formula is written for the first cell of your selection (A2). Sheets adjusts it for every other cell.
Sample data in A2:A7: Apple, Banana, apple, Cherry, Banana, Date.
Highlight every copy, including the first:
=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1)
Apple, Banana, apple and Banana are highlighted (A2, A3, A4, A6). COUNTIF ignores case, so "apple" counts as a repeat of "Apple".
Highlight only the repeats and leave the first occurrence alone:
=AND($A2<>"",COUNTIF($A$2:$A2,$A2)>1)
Only A4 and A6 are highlighted. The range grows as it goes down, so each cell counts only what is above it and itself.
If case matters, so "Apple" and "apple" are different, use EXACT:
=AND($A2<>"",SUMPRODUCT(--EXACT($A$2:$A$100,$A2))>1)
Now only the two Banana cells (A3 and A6) are highlighted.
There are two different questions here.
Select the whole block, for example A2:B4, and use a formula with no dollar signs on the first cell:
=AND(A2<>"",COUNTIF($A$2:$B$100,A2)>1)
Sample, row by row: Apple and Fig; Banana and apple; Fig and Date. Highlighted cells: A2 (Apple), B2 (Fig), B3 (apple), A4 (Fig). Banana and Date are unique.
To flag a row only when several columns all match another row, use COUNTIFS. Select A2:B100:
=AND($A2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1)
Sample rows 2 to 5: Priya and Sydney; Tom and Perth; Priya and Sydney; Priya and Melbourne. Rows 2 and 4 are highlighted. Row 5 is not, because the city differs. Add more column pairs to the COUNTIFS for more columns.
Conditional formatting rules cannot point at another sheet directly. The usual workaround is INDIRECT, which turns text into a reference:
=COUNTIF(INDIRECT("Sheet2!$A$2:$A$100"),$A2)>0
Apply it to A2:A100 on Sheet1. Say Sheet1 has Apple, Banana, Cherry, Date in A2:A5 and Sheet2 has banana, Fig, Date in A2:A4. Banana and Date are highlighted. If the sheet name has spaces, wrap it in single quotes inside the double quotes: INDIRECT("'Old List'!$A$2:$A$100").
If that is awkward, use a helper column instead. In B2 on Sheet1, type =COUNTIF(Sheet2!$A$2:$A$100,A2)>0 and fill down, then base your rule on column B.
Conditional formatting: select the range, open Format > Conditional formatting, hover over the rule and click the bin icon. The values stay put and only the colour goes.
A manual fill colour: select the cells and choose Fill colour > Reset.
Highlights from a rule come back whenever the rule matches. Deleting the data does not delete the rule, so remove the rule too if you are reusing the sheet.
Extra spaces. "Date " and "Date" are not equal. Use Data > Data cleanup > Trim whitespace first.
Numbers stored as text. A "1001" typed as text will not match the number 1001.
Range too short. If your data goes past row 100, widen both the "Apply to range" box and the formula.
Spelling differences. "Jon Smith" and "John Smith" are different strings, so no formula here flags them.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Conditional formatting colours exact repeats. If you need to find near-duplicates, one workable approach is to sort the list, add a helper column that scores each name against the one above, and flag high scores. Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. Its FUZZYSCORE(a, b) returns a similarity from 0 to 1, so a helper column such as FUZZYSCORE(A3, A2) can feed a rule or a filter. You paste the functions into your sheet once, so it works where a Workspace admin blocks add-ons. There is a 30-day refund if it does not work for you. For exact duplicates, the free formulas on this page are all you need.
Duplicate Remover: Remove Duplicates in Sheets, our free add-on, can highlight, mark or delete duplicates across chosen columns. It is coming soon to the Google Workspace Marketplace.
Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.