Guides

Sheets Mega Tools

Find Duplicate Cells

Learn how to locate cells with duplicate values within your selected range in Google Sheets™ using the Text To Table Converter add-on.

The Find Duplicate Cells tool allows you to locate cells that contain duplicate values within your selected range, current sheet, or across all sheets in Google Sheets™.

How to Use Find Duplicate Cells

  1. Open the tool

Open the add-on sidebar, expand Sheets Mega Tools, go to Cell Values, and click Find Duplicate Cells.

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

  1. Choose your range scope

Select where to search via radio buttons:

  • Selection (default) - Search only within your currently selected range
  • Current Sheet - Search the entire active sheet
  • All Sheets - Search across all sheets in your spreadsheet
  1. Configure options (optional)

Click the caret button to expand the options panel:

  • Ignore empty rows: Ignore rows where all compared columns are empty (default: on)
  • Trimming: Toggle to trim leading/trailing whitespace (default: on)
  • Case sensitivity: Toggle for case-sensitive comparison (default: case-insensitive)
  1. Find duplicates

Click the main "Find duplicates" button to search for duplicate cells.

  1. Review results

The results table appears with:

  • Checkbox column for selection
  • Location column showing cell reference (e.g., sheet!A1)
  • Type badge showing "Duplicate"
  • Tooltip showing display value on hover
  • Collapsible table with count in header
  1. Select duplicate cells

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

  1. Apply actions to selected cells

Action buttons enable when selection exists:

Highlight - Apply colors to selected duplicates:

  • Single color: All selected cells highlighted with one color (default: MT yellow #FFEB3B)
  • Multi-color by group: Different duplicate groups highlighted with different colors, interpolated between min and max colors (default: red #f44336 → green #4caf50 gradient)

Delete - Remove duplicate cells with options:

  • Keep occurrence: First (default), Last, or None
  • Create backup sheet: Checkbox (default: off) - Creates a backup before deletion

Copy result - Copy the range and display value to clipboard (same as Find Values feature)

Comparison Basis

The tool compares cells based on:

  • Display value of the cell (not the formula)
  • Ignores blanks by default
  • Ignore empty rows: Rows where all compared columns are empty are ignored (default: on, toggleable)
  • Trimming: Leading/trailing whitespace is trimmed (default: on, toggleable)
  • Case sensitivity: Case-insensitive by default, with toggle for case-sensitive comparison
  • Formulas: Compared by their displayed result (implicit; no toggle needed)

Tip: Results and your selection persist until you run a new find operation. The main button runs find-only; actions are separate and only enabled when you have selected results.

Large Ranges: For very large ranges, a warning will appear before running the search. You can choose to proceed or cancel the operation.

Use Cases

  • Data validation - Find duplicate entries that shouldn't exist
  • Error detection - Identify cells with identical values that may indicate data entry errors
  • Data cleaning - Locate and remove duplicate cell values
  • Quality assurance - Verify data uniqueness in specific columns
  • Pattern analysis - Understand how often values repeat in your data

Note: This tool is part of Sheets Mega Tools, available only in Google Sheets™. Clicking a result navigates to that cell in your spreadsheet.