June 29th, 2026
How to Remove Duplicates in Google Sheets: 6 Methods That Work
By Tyler Shibata · 14 min read
There are 6 ways to remove duplicates in Google Sheets, and the right one depends on how your data is structured and how often you need to do it. I've tested each method and broken them down by use case, so you can get straight to the one that fits.
What counts as a duplicate in Google Sheets?
In Google Sheets, a duplicate is any row or value that appears more than once in the part of the sheet you’re checking, based on the columns you select. Most tools and formulas compare the actual cell values, so 2 cells with the same number count as duplicates even if one is bold and the other isn't.
Most of the tools in this guide, including Remove duplicates and COUNTIF-based checks, are case‑insensitive, so “Apple” and “apple” count as the same value. One exception worth knowing is trailing spaces. A cell with "John Smith" and one with "John Smith " (with a space at the end) won't match, so it's worth cleaning up any extra spaces before you start.
Before you begin: Back up your data
Removing duplicates in Google Sheets is hard to undo with a plain Ctrl+Z once you've closed the file or made other edits since. That said, you're not stuck if something goes wrong: File > Version history lets you restore an earlier copy of the sheet even after you've closed it or made other changes.
I'd still recommend backing up your data before you start, just to be safe. The quickest way to do this is to right-click your sheet tab at the bottom of the screen and select "Duplicate." This creates an identical copy of your sheet within the same file, so your original data stays untouched while you work on the copy.
How to remove duplicates in Google Sheets: 6 methods
The right method for removing your duplicates depends on your data and how often you need to clean it.
Here are 6 ways to get it done, from the quickest option to the most automated:
Method 1: Use the built-in Remove duplicates tool
The built-in tool is the fastest way to remove duplicates in Google Sheets and requires no formulas or coding. It works best for a one-time cleanup of a straightforward dataset.
Here’s how to use it:
Step 1: Select the data range you want to check.
Step 2: Click Data in the top menu.
Step 3: Hover over Data cleanup.
Step 4: Click Remove duplicates.
Step 5: Choose which columns to check and tick the box if your data has a header row.
Step 6: Click Remove duplicates and Google Sheets will show you a summary of how many rows were removed.
Method 2: Use the UNIQUE formula
The UNIQUE formula outputs a clean version of your data in a separate area of your sheet, leaving your original data untouched. I find this method useful when I want to keep the original data as a reference while working with a deduplicated copy.
Here’s how to use it:
Step 1: Click on an empty cell where you want the clean data to appear.
Step 2: Type =UNIQUE( followed by your data range, for example =UNIQUE(A1:D11).
Step 3: Close the bracket and press Enter.
Step 4: Google Sheets outputs a new list with duplicate rows removed.
Method 3: Highlight duplicates with COUNTIFS
COUNTIFS is a good option when you want to identify duplicates before deleting them. It adds a helper column next to your data that marks each duplicate row as TRUE, so you can review them before taking action.
Here’s how to use COUNTIFS to spot duplicates:
Step 1: Add a new column next to your data and label it "Duplicates".
Step 2: Click on the first cell in that column next to your first data row, skipping the header.
Step 3: Type the following formula, replacing A, B, C, and D with the letters of your own columns: =COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,D2)>1
Step 4: Drag the formula down through the rest of your data.
Step 5: Any row showing TRUE is a duplicate.
Step 6: Select the rows marked TRUE and delete them manually.
💡Note: This formula checks every column at once, so a row only gets flagged as TRUE when all columns match a previous row. If you only need to check one column, like an email address, you can use =COUNTIF(A$2:A2,A2)>1 instead, replacing A with the letter of your column.
Method 4: Highlight duplicates with conditional formatting
Conditional formatting takes the COUNTIF approach one step further by applying a color to duplicate rows so you can spot them visually. I find this method useful when I'm working with a large dataset and want to scan for patterns before deleting anything.
Here’s how to highlight duplicates with conditional formatting:
Step 1: Select your data range. In this example, I’m selecting column 1, First name.
Step 2: Click Format in the top menu, then Conditional formatting.
Step 3: Under "Format cells if," choose Custom formula is.
Step 4: Enter =COUNTIF(A$1: A1,A1)>1, replacing A with your column.
Step 5: Choose a highlight color and click Done.
Step 6: Delete the highlighted rows manually once you've reviewed them.
Method 5: Use a pivot table to audit duplicates
Pivot tables don't remove duplicates directly, but they're one of the best ways to find and confirm where duplicates exist before taking action. This method works well for larger datasets where you want to understand the scope of the problem first.
Here’s how to use a pivot table to spot duplicates before removing them:
Step 1: Select your dataset.
Step 2: Click Insert in the top menu, then Pivot table.
Step 3: Choose to insert the pivot table in a new sheet.
Step 4: In the Pivot table editor, add the column you want to check to the Rows section.
Step 5: Add that same column to the Values section and set it to summarize by COUNT for numbers and COUNTA for text.
Step 6: Any value with a count above 1 is a duplicate.
Step 7: Go back to your original sheet and remove those rows manually.
Method 6: Automate duplicate removal with Apps Script
Apps Script is worth setting up if you work with data that gets updated regularly and needs deduplication on a recurring basis. This sample script removes duplicate rows from your active sheet and works from the bottom up to avoid row numbering issues during deletion.
Here’s how to use Apps Script to get rid of your duplicates:
Step 1: Open your Google Sheet and click Extensions in the top menu.
Step 2: Click Apps Script.
Step 3: Delete any existing code in the editor.
Step 4: Paste in the script below.
function deleteDuplicateRows() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = sheet.getDataRange().getValues(); var seen = {}; var rowsToDelete = []; // Start from row index 1 to skip the header row for (var i = 1; i < data.length; i++) { var rowKey = data[i].join('|'); if (seen[rowKey]) { rowsToDelete.push(i + 1); } else { seen[rowKey] = true; } } // Delete from bottom to top to keep row numbers accurate for (var j = rowsToDelete.length - 1; j >= 0; j--) { sheet.deleteRow(rowsToDelete[j]); } SpreadsheetApp.getUi().alert(rowsToDelete.length + ' duplicate rows removed.'); } |
Step 5: Click the save icon and give your project a name.
Step 6: Click Run and approve the permissions when prompted.
Step 7: Return to your sheet to see the duplicate rows removed.
Which method should you choose?
The right method depends on how comfortable you are with formulas and how often you need to clean your data. Choose:
The built-in Remove duplicates tool if: you need a fast, one-time cleanup and don't need to review the duplicates before deleting them.
The UNIQUE formula if: you want to keep your original data intact and work from a clean copy instead.
COUNTIFS and conditional formatting if: you want to see and review duplicates before deciding which rows to delete.
A pivot table if: you're working with a large dataset and want to understand how many duplicates exist before taking action.
Apps Script if: you work with data that gets updated regularly and want to automate the cleanup process.
Clean and analyze your data in one place
Cleaning duplicates in Google Sheets is a good start, but analyzing that data still takes time if you're building formulas and charts manually. Julius is an AI-powered data analysis tool that can handle both. Ask it a question in plain English and it cleans, analyzes, and visualizes your data in one place, so you go from a messy export to clear insights without switching tools.
Here's how Julius helps:
Data search: Julius can search the web for public datasets or pull structured financial data for 17,000+ companies via its Financial Datasets integration, so you can start from a question rather than an upload.
Direct connections: Link databases like PostgreSQL, Snowflake, and BigQuery, or integrate with Google Ads and other business tools. You can also upload CSV or Excel files. Your analysis can reflect live data, so you’re less likely to rely on outdated spreadsheets.
On-the-fly data cleaning: Remove duplicates, standardize date formats, fill or flag missing values, and reshape tables by describing the change you need. Julius runs the transformations in the background, so you don't have to manually write SQL or build nested spreadsheet formulas to fix messy exports.
Repeatable notebooks: Save an analysis as a notebook and run it again with fresh data whenever you need, with results sent automatically to email or Slack.
Ready to see what your clean data can tell you? Try Julius for free today.
