How to Remove Duplicates in Google Sheets: The Complete Guide

Duplicate entries have a way of quietly sneaking into spreadsheets, whether from merging two contact lists, importing form responses, or simply copying and pasting data one too many times. Left unchecked, duplicates can throw off totals, inflate counts, and make your data look messier than it actually needs to be. Fortunately, Google Sheets includes several reliable built-in tools for finding and removing duplicate entries, along with formula-based approaches for more advanced control.

In this guide, we’ll walk through every major method for removing duplicates in Google Sheets, starting with the built-in Remove Duplicates tool, then moving into formula-based approaches using UNIQUE and COUNTIF, conditional formatting to visually spot duplicates before deleting anything, and the Google Sheets mobile app. We’ll also cover how to handle duplicates based on specific conditions, along with common mistakes to avoid along the way.

Why Duplicate Data Is Worth Cleaning Up

Before jumping into the methods, it’s worth understanding why this matters beyond simple tidiness. Duplicate rows can inflate SUM totals, cause COUNT functions to overstate how many unique entries you actually have, and create confusion in charts or pivot tables where the same value appears to be represented multiple times. In contact lists, duplicates often mean sending the same email or message twice, which can look unprofessional. In financial tracking, a duplicated transaction row can make your numbers appear larger than they actually are, leading to inaccurate conclusions if left uncorrected.

Because of this, cleaning duplicate data is an important step in ensuring your spreadsheet accurately reflects the information you’re actually working with, rather than an inflated or distorted version of it.

Method 1: Using the Built-In Remove Duplicates Tool

This is the fastest and most direct way to permanently delete duplicate rows in Google Sheets, built right into the Data menu.

1. If you want to check the entire dataset, you can simply click anywhere inside it, since Google Sheets will typically detect the full range automatically.
2. Click Data in the top menu.
3. Click Data cleanup, then click Remove duplicates.
4. A dialog box will appear showing your selected range and the columns within it. Check the boxes for the columns you want Google Sheets to compare when identifying duplicates. If you want an entire row to count as a duplicate only when every column matches exactly, leave all columns checked.
5. If your data has a header row, make sure the Data has header row checkbox is checked so Google Sheets doesn’t mistakenly treat your column titles as data.
6. Click Remove duplicates.
7. Google Sheets will display a summary message showing how many duplicate rows were found and removed, along with how many unique rows remain.

This method is permanent, meaning the identified duplicate rows are deleted immediately once you confirm, so it’s worth keeping a backup copy or working in a duplicate of your original sheet if you’re not entirely sure yet whether everything flagged is genuinely a duplicate you want removed.

Method 2: Highlighting Duplicates with Conditional Formatting First

If you’d rather visually review duplicates before permanently deleting anything, conditional formatting offers a safer first step.

1. Click Format in the top menu, then click Conditional formatting.
2. In the panel that appears on the right, set the format rule to Custom formula is.
3. Enter a formula like: =COUNTIF(A:A, A1) > 1 (adjusting the column reference to match your actual data).
4. Choose a formatting style, such as a red background, to make duplicates visually obvious.
5. Click Done.

Google Sheets will now highlight every duplicate value in your selected range, allowing you to manually review, sort, or filter your data before deciding how to handle the flagged entries.

Method 3: Using the UNIQUE Function

The UNIQUE function is one of the most powerful tools for handling duplicates in Google Sheets, since it doesn’t delete anything from your original data, but instead generates a clean, duplicate-free list in a separate location.

1. Click on an empty cell where you want your duplicate-free list to begin.
2. Enter the formula: =UNIQUE(A1:A100) (adjusting the range to match your actual data).
3. Press Enter, and Google Sheets will automatically generate a list containing only the unique values from your specified range, leaving your original data completely untouched.

This method is particularly useful when you want to preserve your original dataset exactly as it is, while still being able to reference or work with a clean, duplicate-free version elsewhere in your spreadsheet.

Method 4: Using COUNTIF to Flag Duplicates

For more granular control over identifying duplicates without deleting or generating a new list, COUNTIF formulas let you flag duplicate entries directly within your existing data.

Basic single-column duplicate flag:

1. In a new column next to your data, enter the formula: =COUNTIF(A:A, A1)
2. This counts how many times the value in cell A1 appears within column A.
3. Copy the formula down for all your rows, then filter or sort the column to isolate values greater than 1.

Multi-column duplicate checks using COUNTIFS:

If you need to check for duplicates based on a combination of two or more columns, such as matching both a first name and last name together, use COUNTIFS instead:

=COUNTIFS(A:A, A1, B:B, B1)

This checks whether the specific combination of values across columns A and B appears more than once, which is especially useful for datasets where a single column alone wouldn’t reliably indicate a true duplicate.

Creating a clear label with IF:

=IF(COUNTIF(A:A, A1)>1, "Duplicate", "Unique")

This produces a clean, readable label directly next to each row, making it easy to filter based on the word “Duplicate” rather than interpreting raw numeric output.

Method 5: Removing Duplicates While Keeping the Most Recent Entry

Sometimes you don’t want to delete every duplicate indiscriminately, you specifically want to keep the most recent or most relevant version of a repeated entry.

1. Sort your data first by a relevant column, such as a date column, so the most recent entry for each duplicate group appears at the top (or bottom, depending on your sort direction).
2. Run the standard Remove Duplicates tool as described in Method 1.
3. Since Google Sheets’ Remove Duplicates tool keeps the first occurrence of each duplicate group by default, sorting beforehand ensures the version you actually want to keep is the one that gets retained.

Method 6: Using QUERY to Filter Out Duplicates

For users comfortable with more advanced formulas, the QUERY function offers another flexible way to generate a duplicate-free view of your data, particularly useful when combined with other filtering criteria.

1. In an empty cell, enter a formula structured like: =QUERY(A1:C100, "SELECT A, B, C WHERE A IS NOT NULL GROUP BY A, B, C")
2. Adjust the range and column letters to match your actual dataset.
3. This groups your data by the specified columns, effectively collapsing duplicate combinations into a single row within the generated results.

While more complex than UNIQUE, QUERY offers additional flexibility if you need to combine duplicate removal with other filtering or sorting logic within the same formula.

How to Remove Duplicates on the Google Sheets Mobile App

If you’re working from your phone or tablet rather than a desktop browser, the process is slightly different but still straightforward.

1. Select the range of data you want to clean by tapping and dragging across the relevant cells.
2. Tap the plus icon or menu icon (depending on your device), then look for Data cleanup or Remove duplicates within the available options.
3. Follow the on-screen prompts to confirm which columns to check and complete the removal process.

The mobile app’s interface for this feature has become increasingly similar to the desktop version in recent updates, though older app versions may require navigating through a slightly different menu structure to locate the same functionality.

Removing Duplicates Based on Specific Conditions

Beyond straightforward exact-match duplicates, you may occasionally need to handle more nuanced situations, such as keeping only the duplicate with the highest value in another column.

Using MAXIFS alongside a helper column:

1. Create a helper column that calculates the maximum value for each duplicate group using a formula like: =MAXIFS(C:C, A:A, A1) (where column C contains the value you want to prioritize, and column A contains your duplicate-checking criteria).
2. Compare this maximum value against the actual value in that row using a formula like: =IF(C1=D1, "Keep", "Remove"), where D1 references your helper column.
3. Filter for rows marked “Remove” and delete them, leaving only the highest-value entry from each duplicate group intact.

This approach requires a few more steps than the standard Remove Duplicates tool, but offers precise control for situations where simply keeping the first occurrence isn’t sufficient for your specific needs.

Pro Tips for Handling Duplicates More Effectively

• Always work from a duplicate copy when testing removal methods for the first time. Use File > Make a copy to create a backup before running Remove Duplicates on an important dataset, since the deletion is immediate and Google Sheets’ version history, while helpful, adds an extra layer of recovery complexity compared to simply having an untouched backup ready.
• Use TRIM to catch duplicates hidden by extra spaces. If Google Sheets isn’t flagging entries you expect to be duplicates, hidden trailing or leading spaces are a common culprit. Wrapping your comparison values in =TRIM(A1) before comparing can resolve this.
• Standardize text case before checking for duplicates, since COUNTIF and the Remove Duplicates tool are not case-sensitive by default, which can occasionally cause unexpected matches if you specifically need case-sensitive comparison (in which case, the EXACT function offers more precise control).
• Use UNIQUE for a non-destructive approach whenever possible. Since it doesn’t alter your original data at all, UNIQUE is often the safer choice when you’re not entirely certain you want to permanently delete flagged duplicates.
• Leverage Google Sheets’ Version History as a safety net.
• Combine Data Validation with your cleaned data to help prevent future duplicates from being entered manually, by setting validation rules on your input columns going forward.

Common Mistakes to Avoid

• Forgetting to check the “Data has header row” box. If Google Sheets doesn’t recognize your header row correctly, it may either flag your column titles as duplicate data or fail to properly compare the values beneath them.
• Not selecting the full relevant range before running Remove Duplicates. If you only select part of your data, rows may become misaligned, since only the selected columns and rows are affected, not your entire sheet automatically.
• Relying on Remove Duplicates without sorting first, when you specifically need to preserve a certain version of a duplicate row, such as the most recent entry, rather than whichever happens to appear first in your current sheet order.
• Ignoring hidden characters or inconsistent formatting. Entries that look identical on screen can sometimes be treated as unique values due to invisible formatting differences, extra spaces, or inconsistent capitalization that isn’t immediately obvious just by looking at the cells.
• Applying Remove Duplicates across unrelated columns. If your selected columns don’t accurately represent what should actually define a “duplicate” for your specific dataset, you risk accidentally merging or deleting rows that represented genuinely different records.

Conclusion

Removing duplicates in Google Sheets is a task you can approach in several different ways depending on exactly what you need: a quick, permanent cleanup using the built-in Remove Duplicates tool, a non-destructive list using UNIQUE, precise flagging using COUNTIF and COUNTIFS, or more advanced filtering using QUERY for complex conditional scenarios. Each method has its place depending on how confident you are that a flagged “duplicate” genuinely needs to be removed, and whether you’re working with a one-time cleanup or an ongoing dataset that continues to be updated regularly.

The most valuable habit to build is reviewing before deleting. A quick pass with conditional formatting or a COUNTIF helper column, or simply working from UNIQUE’s non-destructive output, can save you from accidentally removing data you actually needed, particularly in datasets where similar-looking entries might represent genuinely different records rather than true duplicates. With the methods covered in this guide, you should be well equipped to handle duplicate data cleanly and confidently in Google Sheets, entirely free of charge, regardless of dataset size or complexity.

Frequently Asked Questions

1. Does Remove Duplicates in Google Sheets delete the entire row or just the duplicate value? By default, it deletes the entire row once a duplicate is identified based on your selected columns, not just the individual cell containing the repeated value.

2. Can I undo Remove Duplicates if I accidentally delete something I needed? Yes, immediately after running it, you can press Ctrl+Z (or Cmd+Z on Mac) to undo the action.

3. Is Google Sheets’ Remove Duplicates tool case-sensitive? No, by default it treats “Apple” and “apple” as identical values. For case-sensitive duplicate detection, you’ll need a formula-based approach using the EXACT function instead.

4. What’s the difference between using UNIQUE and using Remove Duplicates? Remove Duplicates permanently deletes matching rows from your original data immediately, while UNIQUE generates a separate, duplicate-free list elsewhere in your spreadsheet without altering your original dataset at all.

5. Can I remove duplicates based on only one column while ignoring differences in other columns? Yes, in the Remove Duplicates dialog box, you can uncheck columns you don’t want considered. However, be aware that Google Sheets will still delete the entire row even if unchecked columns contain different data, so use this option with care.

6. Does Google Sheets’ Remove Duplicates tool work the same way on mobile as it does on desktop? Yes, the core functionality is essentially identical, allowing you to select which columns to compare and confirm your header row settings, though the exact menu navigation may differ slightly depending on your specific mobile app version.

7. How do I find duplicates without deleting or altering anything at all? Use conditional formatting to visually highlight duplicates, or add a helper column with a formula like =COUNTIF(A:A, A1)>1 to flag duplicates as TRUE or FALSE without touching your original data in any way.

8. Is Google Sheets completely free to use for duplicate removal and all the formulas mentioned in this guide? Yes, Google Sheets is entirely free with a standard Google account, and every method covered in this guide, including Remove Duplicates, UNIQUE, COUNTIF, COUNTIFS, and QUERY, is available at no cost, with no premium tier required to access any of this core functionality.