Find Duplicate Cells

Find Duplicate Cells finds cells with the same value in your selection, the current sheet, or every sheet. You can then highlight, clear, delete, or copy the results.

How to use Find Duplicate Cells#

1. Open the tool

Open the sidebar from Extensions > Text To Table Converter > ⚡ Launch Sidebar. On the home screen, under Sheets tools, choose Sheets Mega Tools, then choose Find Duplicate Cells.

You can also use Extensions > Text To Table Converter > Sheets Mega Tools to open the Sheets Mega Tools screen directly.

2. Choose the search scope
   • Selection
   • Current sheet (default)
   • All sheets: every sheet is scanned, but each sheet is checked on its own. Cells are never compared across sheets.

3. Set the options (optional)

Open the arrow next to Find duplicates:

• Trim whitespace: on by default
• Case sensitive: off by default
• Find: Duplicates (default) or Unique

4. Click Find duplicates

Scanning the whole current sheet or all sheets shows a warning first. You can continue or cancel.

5. Review the results

The list shows the number of matches. Each row has a checkbox, the cell address, and a type of Duplicate or Unique. Click a row to jump to that cell.

6. Choose an action

The actions work on the checked results.

Actions#

• Highlight selected: choose Single color or Multi-color (default). Multi-color gives each group of duplicates its own color between a min and max color.
• Delete: under Keep occurrence choose First (default), Last, or None. Under Delete mode choose Clear duplicate cell values (default), which empties the cells, or Delete rows containing duplicates. Create backup sheet saves a backup first. You must confirm, and the deletion cannot be undone.
• Copy result: copy the selected results to the clipboard.

How cells are compared#

• Cells are compared by the value shown in the cell. Formulas are compared by their result.
• Leading and trailing spaces are ignored while Trim whitespace is on.
• Comparison ignores capitalization unless Case sensitive is checked.

Limits#

• A run scans up to 100,000 cells and returns up to 2,000 results. Narrow the range if you go over.

Use Cases#

• Find entries that should be unique, such as IDs or emails
• Spot repeated values that may be data entry errors
• Clear or remove repeated values
• List the values that appear only once