If you need Google Sheets how to remove duplicates, the fastest fix is built into Sheets itself, and it works on a selected range or on an entire table. The key is knowing whether you want to delete duplicate rows, hide duplicates, count duplicates, or keep one copy and remove the rest. Once you pick the right method, cleanup takes minutes instead of manual sorting and guesswork.
Remove duplicate rows with the built-in tool
Use this when you want to delete duplicate rows in Google Sheets and keep one copy of each repeated record. This is the safest option for a normal list, such as contacts, orders, or inventory entries.
- Select the range that contains the data, including headers if you have them.
- Click Data, then choose Data cleanup, then Remove duplicates.
- If your range has headers, check Data has header row.
- Choose the columns Sheets should compare. Leave all columns checked to remove full duplicate rows, or select only one or two columns if those define uniqueness.
- Click Remove duplicates.
Sheets shows how many duplicates it removed and how many unique rows remain. That is the direct answer to how to delete duplicates in Google Sheets when you want the rows gone, not just hidden.
When to compare all columns versus only some columns
Compare all columns when an entire row must match before it counts as a duplicate. Compare only the identifying columns when you want to remove repeated names, emails, product IDs, or dates even if other cells differ. For example, if two rows have the same email address but different notes, checking only the email column removes the repeat based on that field.
Delete duplicate data by sorting first
If your sheet is messy and duplicates are scattered, sort the data before deleting duplicate data in Google Sheets. Sorting groups the matching rows together so the duplicates are easier to see and easier to remove one at a time.
- Select the full dataset.
- Click Data, then Sort range.
- Choose the column that identifies duplicates, such as email, SKU, or customer name.
- Sort A to Z or Z to A.
- Scan the grouped entries and delete the extra rows you do not need.
This method is useful when you want more control than the automatic tool gives you. It also helps when the duplicates are not exact row matches, because you can judge which version to keep before you delete duplicate rows in Google Sheets.
Find duplicates with filters or conditional formatting
If your goal is to filter duplicates in Google Sheets, or to format duplicates in Google Sheets before removing them, use highlighting first. This is better when you want to review duplicates without deleting anything immediately.
Highlight duplicate values with conditional formatting
Use conditional formatting when you want duplicates to stand out visually. It works well for a single column such as emails, phone numbers, or IDs.
- Select the column or range.
- Click Format, then Conditional formatting.
- Under Format rules, choose Custom formula is.
- Enter a duplicate-check formula for the first cell in your range, for example =COUNTIF(A:A,A1)>1.
- Pick a fill color and click Done.
Any repeated value gets highlighted. This is the cleanest way to hide duplicates in Google Sheets only in the sense that you can visually isolate them first, then decide what to remove.
Filter duplicate rows for review
A filter lets you narrow the sheet to the values you want to inspect. If you need to see only repeated entries, first add a helper column with a duplicate formula, then filter that column to show the duplicate markers.
A simple setup is to use a formula like =COUNTIF(A:A,A2) in a helper column. Any number greater than 1 indicates a repeated value. You can then filter that helper column to show only rows with counts above 1.
Count duplicates in Google Sheets
If you want Google Sheets count duplicates instead of deleting them, use COUNTIF. This is the right approach when you need a duplicate count for auditing, reporting, or cleanup planning.
For a single column, enter this in a helper column:
=COUNTIF(A:A,A2)
If the result is 1, the value appears once. If the result is 2 or more, the value repeats. To count only the duplicate instances beyond the first copy, use:
=COUNTIF(A:A,A2)-1
That returns how many extra copies exist for each repeated value. This is useful before you delete duplicates in Google Sheets, because it tells you how many rows will disappear if you clean the sheet.
Remove duplicate rows with formulas instead of deleting manually
If you want a clean list without touching the original data, build a deduplicated output with a formula. This is the best answer when you want to remove duplicate rows in Google Sheets for reporting, but keep the source table intact.
Use UNIQUE in a blank area or on a new sheet:
=UNIQUE(A2:D)
This returns only the distinct rows from the range. If your data has headers, start below the header row. You can also use it with one column, such as =UNIQUE(A2:A), when you want only unique values from a list.
This method does not delete anything from the original range. It is ideal when people ask how to remove duplicate data in Google Sheets without risking the source table.
Use SORT with UNIQUE for a cleaner output
If you want unique values in a specific order, wrap UNIQUE inside SORT. For example, =SORT(UNIQUE(A2:D)) returns unique rows and sorts them. This is useful when you are preparing a clean export, a mailing list, or a report view.
Prevent duplicates before they get into the sheet
If you need to prevent duplicates in Google Sheets, stop them at data entry. The cleanest method is data validation on a key column like email, ID, or product code.
- Select the input column.
- Click Data, then Data validation.
- Set a custom formula rule that checks for repeats, such as =COUNTIF(A:A,A1)=1.
- Choose to reject invalid input.
- Save the rule.
This blocks a duplicate entry from being added in that column. It does not clean existing records, but it stops new duplicate data from building up. If your team shares a sheet, this saves time because you do not need to keep deleting duplicate rows in Google Sheets later.
Merge duplicates without losing information
If your question is how to merge duplicates in Google Sheets, the answer is not the built-in Remove duplicates tool. That tool deletes extra rows and keeps only one record. Merging duplicates means combining values from several rows into one row before you remove the extras.
Use this approach when duplicate records contain different useful details, such as notes, phone numbers, or dates. First decide which column is the key, then collect the extra fields into the master row. For example, if two rows share the same customer name, you can copy the unique phone number or note into the row you want to keep, then delete the duplicate row after the merge.
If you need a formula-based merge, build a summary sheet with unique keys and pull related data into it with lookup formulas. That is a different task from simply deleting duplicates in Google Sheets, but it preserves more information.
Frequently Asked Questions
How do you delete duplicates in Google Sheets without removing the first copy?
Use Data, then Data cleanup, then Remove duplicates. Sheets keeps one copy of each matching row and deletes the extras. If you only want to compare one column, uncheck the others in the column picker before you run the tool.
How do you delete duplicate rows in Google Sheets based on one column only?
Select the range, open Remove duplicates, and check only the column that should define duplication. For example, if email addresses must be unique, compare only the email column. Sheets will remove rows that repeat that value, even if other cells differ.
How do you delete duplicate in Google Sheets if the data is not an exact match?
Use sorting or a helper column first. Exact duplicate removal only works when the selected columns match the same way. If rows differ in one cell but represent the same record, decide which column is the real key, then remove duplicates based on that column or merge the rows first.
How do you hide duplicates in Google Sheets?
Use conditional formatting to highlight them, then apply a filter to the helper column or the highlighted range. Sheets does not have a one-click hide duplicates feature, so the practical method is to mark duplicates with a formula and filter them out of view.
How do you filter out duplicates in Google Sheets?
Create a helper column with =COUNTIF(A:A,A2), then turn on a filter and show only rows where the count is greater than 1. That gives you a filtered duplicate list you can review before deleting anything.
How do you count duplicates in Google Sheets?
Use =COUNTIF(range,cell) in a helper column. The result shows how many times each value appears. For the number of extra copies only, subtract 1 from that result.
How do you format duplicates in Google Sheets?
Use conditional formatting with a custom COUNTIF formula. Choose a fill color, text color, or bold style to make repeated values stand out. This is the fastest way to format duplicates before you decide whether to delete them.
Related Posts:
Google Sheets
- How to Make a Graph in Google Sheets
- How to Remove a Drop-Down in Google Sheets
- How to Calculate Totals in Google Sheets
- How to Merge Cells in Google Sheets
- How to Use VLOOKUP and XLOOKUP in Google Sheets
- How to Add Cells and Columns in Google Sheets (SUM)
- How to Change Cell Color With a Formula in Google Sheets
- How to Lock a Formula or Cell in Google Sheets
- How to Delete or Remove a Filter View in Google Sheets
- How to Hide Formulas in Google Sheets
- How to Add a Header Row in Google Sheets
- How to Insert an Image Into a Cell in Google Sheets
- How to Use COUNTIF in Google Sheets
- How to Insert a Hyperlink in Google Sheets
- How to Insert Multiple Columns in Google Sheets
- How to Sort Numbers in Order in Google Sheets
- How to Edit a Pivot Table in Google Sheets
- How to Insert a Checkbox in Google Sheets
- How to Delete Columns in Google Sheets
- How to Sort by Multiple Columns in Google Sheets
- Google Sheets Not Showing Tabs: Fixes
- How to Protect Cells and Sheets in Google Sheets
- How to Convert Google Sheets to Excel
- How to Convert Google Sheets to PDF
- How to Resize Cells to Fit Text in Google Sheets
- How to Insert or Remove Page Breaks in Google Sheets
- How to Link Google Sheets to Google Calendar
- How to Calculate Days Between Dates in Google Sheets
- How to Insert Multiple Rows in Google Sheets
- How to Round Numbers in Google Sheets