5 Ways to Remove Duplicates in Google Sheets

Duplicate rows can quietly wreck a clean spreadsheet before you even notice. Whether you're managing a client list, a sales report, or form responses, repeated data throws off your numbers and wastes your time. Thankfully, Google Sheets makes it simple to remove duplicates in Google Sheets using a handful of proven methods. You can rely on the Remove Duplicates tool for one-time cleanups, use the UNIQUE function for a formula-based approach, or highlight duplicate values with Conditional Formatting before deleting anything. This guide walks through five practical ways to identify duplicates, clean your dataset, and keep your spreadsheet data accurate going forward, so every report you build stays trustworthy from the start.

If you're tackling other everyday tech clutter too, our digital decluttering guide covers the same clean-data mindset for files beyond your spreadsheets. You can also browse more practical walkthroughs on the blog, or head back to the ytcliptune.com homepage to see everything else we cover.

How to Find and Highlight Duplicates Before You Remove Them

Before you delete anything, it helps to see what you are dealing with. Find duplicates first, then decide what to do with them. This step matters more than most people think. Deleting the wrong row can quietly wreck a good data set.

The quickest route is Conditional Formatting with a custom formula. You highlight a cell range, apply a formatting rule, and Sheets colors every repeated value automatically. Gemini can also handle this now. Type a plain request like "highlight every duplicate value," and the built-in AI writes the formula on its own.

Highlight Duplicates in a Single Column

Select the column you want to check, then open Format and choose Conditional Formatting. Pick "Custom formula is" and enter =COUNTIF($B$2:$B$15,B2)>1. Every cell that shows up more than once gets colored, based on your chosen fill color. This is a clean way to identify duplicates without touching your original data yet.

Highlight Duplicates Across Multiple Columns

Checking several columns at once needs a wider formula. Something like =COUNTIF($A:$Z,INDIRECT(ADDRESS(ROW(),COLUMN())))>1 scans the entire block. You can also narrow the data range under "Apply range" if you only care about a few columns instead of the full sheet.

How to Delete Duplicate Rows in Google Sheets With the Built-In Tool

This is the method most people reach for first when they need to remove duplicates in Google Sheets quickly. Select your data range, click Data, then run Data > Data cleanup > Remove duplicates. Sheets asks whether your range has a header row. Then it lets you choose which columns to check. Click the button. It reports exactly how many duplicate rows were deleted.

The Remove Duplicates tool is fast and needs zero formulas. It edits your original data set directly though, so there is no undo once you close the file. It also will not update itself. Add new rows later, and you will need to run it again by hand.

Remove Duplicates in One Column

If you only care about one field, like an email address, select just that column before opening the tool. Sheets checks only that data. It ignores the rest of the row while hunting for repeat records.

Remove Duplicates Across Two or More Columns

Select the full block instead of one column. Check every box under "Columns to analyze." Now a row only counts as a duplicate if every chosen column matches exactly. This keeps close-but-different entries safe from deletion.

Remove Duplicates But Keep Their Original Position

The built-in tool always keeps the first occurrence and deletes anything that comes after it. If order matters to you, that is actually good news. Just sort your sheet the way you want it before you run the cleanup.

Removing Duplicate Data in Google Sheets With Formulas

Formulas give you more control than the built-in button. They can also automate spreadsheet workflows, so you stop repeating manual work every single week. Three functions carry most of the load here: UNIQUE, QUERY, and COUNTIF function.

Formulas also leave your original data set untouched. That matters if you need an audit trail. It also matters if someone else owns the source data, and you are just building a clean copy beside it. Overall, formulas are the smarter path to remove duplicates in Google Sheets without ever touching what came before.

The UNIQUE Function

The UNIQUE function is the simplest option for deduplication. Type =UNIQUE(A2:D50), and Sheets returns only the distinct rows from that block. It checks every column together. Two rows only match if they are identical across the whole row, not just one field.

The QUERY Function

The QUERY function gives you sharper precision than UNIQUE. A formula like =QUERY(A2:I,"Select A,min(B),H,I group by A,H,I offset 1") lets you pick exact columns, group values, and skip blank rows. It takes longer to learn. It also rewards you with tighter control over the final output.

Merging Columns to Catch Duplicates Across Multiple Fields

Sometimes you only need to check two or three fields, not the whole row. Build a unique identifier first with CONCATENATE or ARRAYFORMULA, like =ARRAYFORMULA(CONCAT(F2:F,J2:J)). That merged string becomes your test for duplicates. A SORTN and FILTER function combo then pulls back only the first match for each ID.

Deduplicating a Dataset That Refreshes Automatically

If your sheet pulls in new rows on a schedule, say from an ETL tool like Coupler.io pulling HubSpot leads, static formulas can quietly break. Pair your unique-ID formula with SORTN and FILTER in a second tab instead. The clean, deduplicated version will refresh data automatically every time the source updates.

Finding Duplicates in Google Sheets With Pivot Tables

A Pivot Table will not delete anything. It is still one of the fastest ways to spot duplicate data before you act on it. Go to Insert, then Pivot Table, and choose your data range. Drop the column you are checking into Rows. Add that same column to Values, and summarize it by COUNTA.

Any entry showing a count above one is a repeat. This trick is popular partly because Ben Collins, a well-known Google Developer Expert, often points to it as the fastest way to check whether a messy dataset even has a duplicate problem, before you commit to fixing anything.

Highlighting and Removing Duplicate Values With Conditional Formatting

Conditional Formatting deserves its own closer look. People often assume it deletes duplicates by itself. It does not. It only highlights duplicate cells. You still need to remove them by hand, or hand the job off to the built-in tool afterward.

The upside is control. You choose the exact cell range, the formatting rule, and the fill color applied. That makes this the safest option when you want to review every duplicate personally before anything actually gets deleted.

Conditional Formatting for a Single Column

Use =COUNTIF($A$1:$A1,A1)>1 on one column, and every repeat lights up. Copy the highlighted rows out. Sort by color, then delete the block in one pass instead of hunting row by row.

Conditional Formatting for Multiple Columns

Chain several COUNTIF checks together with multiplication, like =(COUNTIF($A:$A,$A1)>1)*(COUNTIF($B:$B,$B1)>1). Only rows that repeat across every listed column get flagged. That cuts down on false positives compared to checking just one field alone.

Automating Duplicate Removal in Google Sheets With Apps Script

Apps Script is the right call when you need this task to run again and again without opening a menu each time. A short script using SpreadsheetApp, getActiveSheet, and getRange can scan a sheet, compare rows, and wipe out anything that repeats, then rewrite the clean data back with setValues. This is the method to reach for when you want to remove duplicates in Google Sheets automatically, every single time new data lands.

You can trigger the script from a custom menu using onOpen. A teammate with zero coding background can then click "Remove Duplicates" and get the same clean result you would get from the script editor itself.

Customizing the Script for a Specific Range

Swap sheet.getDataRange() for a fixed range, like sheet.getRange("B1:F"). Now the script only checks that block instead of the whole sheet. This trick is handy when a header, notes column, or timestamp field should never count toward a match.

Bonus: How to Find Unique Values in Google Sheets

Sometimes you do not want duplicates gone. You just want a list of what is actually distinct. The UNIQUE function handles this in a single line. Gemini can do the same thing conversationally, if you simply ask it for "only unique values" from a range.

Doing this before a big cleanup pays off. A quick list of unique values shows you how much overlap you are really dealing with. That tells you whether five minutes with the Remove Duplicates tool will be enough, or whether you actually need a formula-based approach.

Which Method Actually Works Best?

Every method above works, and the right one depends on your dataset, your comfort with formulas, and how often the data changes. Zapier's own writers describe repeat records as data doppelgängers, and that is honestly the right way to picture them. They look real until you check closely.

Here is a quick side-by-side comparison so you can decide fast:

If you also work in Excel, the underlying logic is nearly identical, though the exact menu paths differ. And if you manage data at a much bigger scale, tools that push information into BigQuery offer their own deduplication layer before anything ever lands in your sheet.

Frequently Asked Questions

Does Remove Duplicates delete the first or last occurrence?

It keeps the first occurrence in your data range and deletes everything that repeats after it. Sort your sheet first if row order matters to you.

How to remove duplicates in Google Sheets but keep their position?

Sort your data the way you want it first, then run Remove Duplicates, since the tool always keeps the first occurrence and drops the rest.

How to find out duplicates in Google Sheets?

Use Conditional Formatting with a COUNTIF formula, or build a quick Pivot Table, to spot repeat values before deleting anything.

How to find duplicates in Google Sheets without deleting?

Highlight them with Conditional Formatting instead of running Remove Duplicates, since highlighting only marks repeats without touching your data.

How do I delete duplicates in Google?

In Google Sheets, select your data, then go to Data, Data cleanup, and Remove duplicates to clear repeat rows in a few clicks.

Can I undo removing duplicates in Google Sheets?

Yes, but only right away. Press Ctrl+Z, or Cmd+Z on a Mac, immediately after running the tool. Once you close the file or make more edits, the deleted rows are gone for good.

Do I need a header row before I clean up duplicates?

Not technically, but it helps a lot. Sheets asks whether your data range includes a header row. Checking that box stops it from treating your column titles as data to compare.

What's the difference between removing and highlighting duplicates?

Highlighting, usually through Conditional Formatting, only marks repeat values so you can review them first. Removing actually deletes the rows for good. Most careful workflows highlight first, then remove second.

Can I remove duplicates in Google Sheets automatically?

Yes. Pair a formula like UNIQUE or SORTN with FILTER function in a second tab, or write an Apps Script function instead. Your sheet will deduplicate new entries without you lifting a finger.

Is there an add-on that removes duplicates for me?

Several exist on the Google Workspace Marketplace, including tools built by Ablebits. They add extra buttons and wizards. The native Remove Duplicates tool already covers most everyday cases for free though.

Final Thoughts

Cleaning up duplicate entries does not need to eat your afternoon. Start with the built-in tool for a quick one-time fix. Lean on UNIQUE or QUERY once your dataset keeps growing on its own. Bring in Apps Script when you are finally ready to automate the whole thing.

Whichever method you choose, the goal never changes. Learning how to remove duplicates in Google Sheets properly gives you a clean, trustworthy spreadsheet data set you can actually build decisions on. Try one method from this guide today, and check out more practical walkthroughs on the ytcliptune.com blog while you're at it.