Find Duplicate Rows in Google Sheets™

The Find Duplicate Rows tool is a unified solution to find, highlight, and manage duplicate rows in your Google Sheets™ spreadsheet. It uses a find-first pattern where you first identify duplicate rows, review them in a results window, then apply actions.

How to Use Find Duplicate Rows

1. Open the tool

Open the add-on sidebar, expand Sheets Mega Tools, go to Rows & Columns, and click Find Duplicate Rows.

You can also use the Sheets Mega Tools menu shortcut to open the same sidebar section.

2. Select your range

Choose where to search for duplicates:

• Selection - Search only within your currently selected range
• Current Sheet - Search the entire active sheet
• All Sheets - Search across all sheets in your spreadsheet

3. Select scope

Choose what to find:

• Find duplicates (default) - Find all duplicate rows
• Find duplicates without first instance - Find duplicates excluding the first occurrence
• Find uniques - Find rows that appear only once
• Find uniques without first instance - Find unique rows excluding the first occurrence

4. Configure options (optional)

Click the caret button to expand the options panel:

• Direction: Toggle button to start from top (default) or bottom
• Columns to check: Select specific columns to compare (default: all columns)
• Data has headers: Checkbox to indicate if the first row contains headers (default: checked)
• Ignore empty rows: Checkbox to ignore rows where all compared columns are empty (default: checked)

5. Find duplicates

Click the main "Find Duplicates" button to search for duplicate rows.

6. Review results

The results window displays:

• Count of duplicate rows found
• Table showing: Checkbox | Row location (sheet!A5:Z5) | Type badge ("Duplicate Row")
• Results are clickable to navigate to the row (selects full row)
• Collapsible results section with toggle button
• Tooltips showing row content preview on hover

7. Select duplicate rows

Use checkboxes to select specific duplicate rows you want to manage.

8. Apply actions to selected rows

Choose an action to apply:

Highlight - Highlights selected rows with color options:

• Single color mode: All selected rows highlighted with one color (default: yellow)
• Multi-color mode: Different duplicate groups highlighted with different colors, interpolated between min and max colors (default: red to green gradient)
• Color pickers available for customization
• Note: Highlighting finds the leftmost and rightmost cells with data in each row, then applies formatting to the full range from left to right

Delete - Deletes selected rows with options:

• Keep first result row (checkbox)
• Create backup (checkbox) - Creates a backup sheet before deletion

Copy to new sheet - Copies selected rows to a new sheet with options:

• Sheet name input (default: auto-generated based on scope and timestamp)
• Include headers checkbox (default: checked if data has headers)

Move to new sheet - Moves selected rows to a new sheet (removes from original) with options:

• Sheet name input (default: auto-generated based on scope and timestamp)
• Include headers checkbox (default: checked if data has headers)

Mark status - Adds a status column and marks selected rows as "Duplicate Row"

9. View result message

After applying an action, a status message confirms what was done.

Options and Defaults

• Scope selection:
• Find duplicates (default)
• Find duplicates without first instance
• Find uniques
• Find uniques without first instance
• Direction: Start from top (default) or bottom
• Columns to check: All columns (default) or specific column selection
• Data has headers: Checked by default
• Ignore empty rows: Checked by default - rows where all compared columns are empty are ignored
• Highlight scheme:
• Single color mode (default: yellow #FFEB3B)
• Multi-color mode (default: red #f44336 → green #4caf50 gradient)
• Highlighting applies to the range from leftmost to rightmost cell with data in each row (full horizontal span of data)

Use Cases

• Data cleaning - Identify and remove duplicate entries from your dataset
• Quality control - Find duplicate records that shouldn't exist
• Data analysis - Identify patterns in duplicate data
• Database preparation - Clean data before importing into databases
• Report generation - Ensure unique records in reports