Find duplicate or unique cells in a spreadsheet

This additional tool to the Remove Duplicates will help you track down all duplicate or unique cells in your Google Sheets. Find duplicate cells.

Before you start

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.

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

Tip. Use this add-on to find duplicate or unique cells in one spreadsheet.

In case you need to look for identical rows rather than single cells, we have other tools for that: Find duplicate or unique rows and it's one-step version Quick Dedupe.

To compare two sheets or tables for duplicates or uniques, take advantage of the Compare columns or sheets tool instead.

To merge or consolidate unique records that refer to multiple duplicate values on different rows, turn to Combine Duplicate Rows.

How to use Remove Duplicate Cells

Start the add-on

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

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

Step 1: Select the range

Choose the range of cells you want to check for duplicates or uniques: Pick the range to look for duplicate cells in.

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: Pick the necessary range of cells in the pop-up window.

Tip. Use the Auto select button to pick the entire used range (before the first blank column and row) automatically.

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: Find Duplicates and their 1<sup>st</sup> occurrences.

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

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: Color, copy, or remove duplicates from your sheet.

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: The values have been found and colored.

Tip. Press the Start over button to use the add-on again.

Responses

Alan Howard says:
June 22, 2022 at 7:17 pm

I'm typing in a long list of names and details .. there's a strong chance that I would have typed in that same persons name earlier and already added details. Is it possible that as I type in a name, if it has occured beforehand, google sheets would jump to that earlier row so that I can add to the info I already have rather than starting a new row?

Shhhhhh says:
June 17, 2022 at 10:22 am

Nah, google remove this extension because of privacy lol

Robert says:
July 19, 2019 at 1:40 pm

I have 1000 rows with multiple columns - I want to remove a row if row P is the same -

Col a . Col b . col c
robert Ward 27
robert stewart 27
robert . john . 27
robert . ward . 27

Ho can i delete all duplicates in col c apart from the first row. Dedupe thinks on the last row is a duplicate - i want to delete if a specific col is duplicates - in e.g above - it should remove 3 rows and keep the first

Thanks
Robert

Natalia Sharashova (Ablebits Team) says:
July 22, 2019 at 6:28 am

Hello Robert,

If I understand you correctly, you need to run Remove duplicate rows instead. Select only column C on Step 3 to check for duplicates based on that column contents.

If you still have any difficulties, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that email for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.
We'll look into your task. Thank you.

julie says:
July 19, 2019 at 5:00 am

no matter how many times i've tired to do this add-in, i got nothing
i tired to select the cells, it only showed me 'incorrected cells'
plz tell me how to figure this out.

Kwon Mi Rim says:
April 2, 2019 at 2:08 am

Rather than seeing results in a single operation.
I want it to be applied in real time when I am writing on the sheet.
Is there any way I do not know?

Natalia Sharashova (Ablebits Team) says:
April 2, 2019 at 8:12 am

Thank you for your interest in our product, Kwon.

Currently, it's impossible to run the add-on automatically. You need to run it each time you want to remove duplicate cells.
I should mention that our Remove Duplicate Rows contains scenarios that let you save the options you select most often and run them in a click. Depending on your task, you may find this feature helpful.

Kirsten McCrea says:
December 19, 2018 at 2:35 am

How can I remove duplicates but still keep the row blank? I have an email list with a lot of duplicate emails, but only some of the entries have a name associated in the First Name column. When I try to remove duplicates from the Email column, the Email column ends up a lot shorter than the First Name column, and the correct names are no longer associated with the corresponding emails.

Thank you for your help.

Natalia Sharashova (Ablebits Team) says:
December 19, 2018 at 7:55 am

Thank you for your question, Kirsten.

If I understand your task correctly, you need to use Remove Duplicate Rows, not the tool described above. However, neither of add-ons shifts cells of one column when clearing the values.

For us to be able to assist you better, please share your spreadsheet with us (gapps.ablebits@gmail.com) for testing. Also, please send us the screenshots of the options you choose on each step of the add-on.
We'll look into the problem and do our best to make the tool work for you.

Thank you.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.