Find duplicate or unique cells in a spreadsheet

Duplicates = complete matches

Only complete matches are treated as duplicates. Partial matches are not considered duplicates. And keep an eye out for excess spaces: they may interfere with results as well. Our Remove tool will help you get rid of them.

Google Sheets cell limit

Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.

Backup copies

We care about your data and suggest you always create backup copies of your spreadsheets. A special option of the add-on will do that for you if you select it.

Explore other deduplication tools

• Remove Duplicate Rows — find identical or unique rows within a table.
• Quick Dedupe — 1-step version of Remove Duplicate Rows.
• Compare Sheets for Duplicates — for when your task is to compare two or more columns, tables or sheets for duplicates or uniques.
• Compare Sheets for Differences — marks differences between multiple Google sheets cell by cell.
• Compare Two Columns — scans two columns within one sheet for duplicate or unique values.
• Combine Duplicate Rows — merges or consolidates unique records that refer to multiple duplicate values on different rows.

Start the add-on

Use the Google Sheets menu and go to Extensions > Remove Duplicates > Find duplicate or unique cells:

Tip. Or access the utility from the Dedupe & Compare group of the Power Tools collection:

Step 1: Select the range

Choose the range of cells you want to check for duplicates or uniques:

You'll see the entire used range highlighted by default.

Adjust it to your needs by clicking the Select range icon and entering other range manually or selecting it the spreadsheet of interest:

To preserve a copy of your spreadsheet intact just in case, decide in favor of the Create a backup copy of the sheet option.

Click Next to go on to the second step.

Step 2: Decide what to find

Choose the type of data you want to find in your spreadsheet:

• Pick Duplicates from the drop-down list in order to look for dupes only, without their first instances:
  
• Select Duplicates + 1st occurrences if you want to see all dupes including their first instances in the result:
  
• Go with Uniques to find only the values that doesn't have replicates in the selected range:
  
• Switch to Uniques + 1st occurrences to search for both uniques and the first appearances of duplicates:
  

Tick off the option to Skip empty cells and the add-on will exclude all empty cells from the search. Otherwise, they will be treated as uniques or duplicates, depending on their frequency within your data.

If you have the same records written in different cases, and you want to have them as different values, check the option to Match case as well.

You can return to step 1 by clicking Back.

Press Next to proceed to the final step.

Step 3: Tweak additional options

The add-on offers 4 ways to process the found duplicate or unique cells:

Pick the radio button next to the action you'd like to apply:

• Decide to Fill with color to paint the cells with found values. The arrow next to the option will show the hues to choose from.
• To work with the found cells in a new sheet, spreadsheet, or a place of your choice without losing the values from the original sheets, select the option Copy to another location.
• Alternatively, the records can be cut from the original sheet and pasted where you indicate if you choose to Move to another location.
• The Clear values option will eliminate the dupes or uniques from the spreadsheet completely while keeping other data as is.

Click Finish to find duplicate or unique values.

See the result

Once the add-on completes the search, you will see the summary of the result with the number of found values and the action that was applied: