There are five reasonable ways to remove duplicates in Google Sheets, and they suit different jobs. Pick by whether you want to delete the extra rows, keep the original and show a clean copy, or only mark the repeats. This page is the overview, with links to the detailed guides.
Names in A2:A7, row 1 holds a header:
Acme
Globex
Acme
Initech
Globex
acme
Note the last row is lower-case. Whether "acme" is a duplicate of "Acme" depends on the method, as covered below.
Select the range, then choose Data > Data cleanup > Remove duplicates. A dialog asks whether your data has a header row and which columns to compare. Confirm and the extra rows are deleted from the sheet itself, which is permanent apart from Undo. Work on a copy of the tab the first time. Try it on a few differently capitalised rows before relying on it for case. For several columns at once, see https://www.snakbacks.com/remove-duplicates-google-sheets-multiple-columns
=UNIQUE(A2:A7)
This returns Acme, Globex, Initech, acme, a clean copy that leaves the original untouched and updates when the source changes. UNIQUE is case-sensitive, so "acme" is kept as its own value. COUNTUNIQUE(A2:A7) also returns 4. To ignore case, lowercase first:
=UNIQUE(ARRAYFORMULA(LOWER(A2:A7)))
This returns acme, globex, initech. For more on case, see https://www.snakbacks.com/case-insensitive-match-google-sheets
If you use a range that runs past your data, such as =UNIQUE(A2:A100), the output includes an extra blank row for the empty cells. Filter the blanks out first:
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
In B2, filled down to B7:
=COUNTIF($A$2:A2,A2)
This gives 1, 1, 2, 1, 2, 3. A value of 1 is the first appearance, so anything above 1 is a repeat. COUNTIF ignores case, so "acme" counts as the third Acme. To keep only the first of each:
=FILTER(A2:A7,B2:B7=1)
This returns Acme, Globex, Initech. Or select the data and use Data > Create a filter to hide the rows where B is above 1, then delete the visible rows you want gone. Counting rather than removing is covered at https://www.snakbacks.com/count-duplicates-google-sheets
If you only need to see the duplicates, use Format > Conditional formatting, choose Custom formula is, and enter:
=COUNTIF($A$2:$A$7,A2)>1
Every value that appears more than once is shaded, including its first appearance. Nothing is deleted. The full walkthrough, including multiple columns, is at https://www.snakbacks.com/highlight-duplicates-google-sheets
All four methods above need identical text (with case varying as noted). "Acme Pty Ltd", "ACME Pty. Ltd" and "Acme Pty Limited" are three different values to every method above. Cleaning helps with some: TRIM removes extra spaces, and LOWER removes capital-letter differences. Punctuation and word differences need similarity matching, not formulas. A practical order is to clean first with TRIM and LOWER in a helper column, remove exact duplicates from that column, and only then review what is left by eye or with a similarity score.
Duplicates defined by several columns together: https://www.snakbacks.com/remove-duplicates-google-sheets-multiple-columns
Keep the most recent row, not the first: https://www.snakbacks.com/remove-duplicates-keep-latest-google-sheets
Count how many times each value repeats: https://www.snakbacks.com/count-duplicates-google-sheets
Merge duplicate rows and add up their amounts: https://www.snakbacks.com/merge-duplicate-rows-sum-google-sheets
Highlight instead of delete: https://www.snakbacks.com/highlight-duplicates-google-sheets
One-off cleanup of a copy: Way 1.
A live, clean list that updates: Way 2.
You need to review before deleting: Way 3 or Way 4.
Different spellings of the same thing: Way 5.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
For near-duplicates such as spelling variants and typos, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas adds FUZZYSCORE(a, b) to score how similar two values are, pasted into your sheet once, with a 30-day refund if it does not help. Ways 1 to 4 need nothing but Google Sheets itself.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are 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.