Find duplicate or unique rows in one Google sheet

Use the Remove Duplicates add-on for Google Sheets to deal with duplicated data in your table. Find, delete, move, copy, or clear duplicate or unique rows in your spreadsheet.
Find duplicate rows.

Video: How to remove duplicates in Google Sheets

Before you start

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

How to use the Remove Duplicates add-on

Start Remove Duplicates

Open the Extensions menu, find Remove Duplicates, and click on the Find duplicate or unique rows tool:
Find Remove Duplicates in the Google Sheets menu.

Tip. You will also find Remove Duplicate Rows in the Dedupe & Compare group of Power Tools:
The Remove Duplicates icon in Power Tools.

Step 1: Select your data

Here you can select the range with the data you want to search in:
Select the range to look for duplicates within.
By default, the add-on highlights the entire table in the sheet of your choice.

Note. If you want to delete duplicates in Google Sheets rather than just find and highlight them, it's best to select the whole table so that the add-on removes entire rows avoiding data corruption.

You can alter the range by entering the needed address in the Select the range with your data field or by clicking the Select range icon. The latter will open a dialog box and allow you to pick the necessary cells:
A dialog window for you to highlight the necessary cells.

Tip. Use the Auto select button to detect the used range (till the first blank row and column) with your data automatically.

You can choose to back up your original table by selecting the checkbox Create a backup copy of the sheet.

Click Next to proceed to the second step.

Step 2: Choose what to find

The add-on lets you check your table for 4 types of data:
The 1st radiobutton deletes duplicates from Google Sheets.

  • Pick the 1st one to delete duplicate rows in your Google Sheets table. The add-on will keep unique records and the first entries of any repeated records you have. Your dataset will become clear of any extra replicates.
    Note. Only complete matches are treated as duplicates. Partial matches are not considered duplicates.

    Find Duplicates.

  • Select Duplicates + 1st occurrences to identify the original entries as well as their copies:
    Find Duplicates and 1st occurrences.
  • Choose the Uniques option to find only those rows that don't have any duplicates in the sheet:
    Find Uniques.
  • The Uniques + 1st occurrences option lets you find everything except for the duplicates. The add-on will look for the first entries of duplicated data in addition to unique records:
    Find Uniques + 1st occurrences.

Make use of the last checkbox — Reverse dedupe order — when you need to dedupe your data from bottom to top. This way, your latest values (added to the bottom of your sheet) will be treated as the first occurrences while oldest records (at the top) will be treated as duplicates.
Dedupe from top to bottom or vice versa to treat the oldest or latest records as duplicates.
Click Next to go on, or return to step 1 by clicking Back.

Step 3: Pick the columns to check

Here you will see a list of columns in your table — tick off those you want to check for duplicates in Google Sheets:
Choose columns you want to search in.

Note. If you check more than one column, a row will be considered duplicate if the values in all the selected columns are the same:
How key columns work.
Tip. If you want to find complete row duplicates in Google Sheets, tick off all columns at once by using the top checkbox in the header row.
  • You can tick off the Skip empty cells option to ignore duplicated blanks in the selected columns. Otherwise, blank matches will be considered duplicates.
  • If you have header rows, make sure to check My table has headers to see your column titles. If you don't have headers, unselect this box to display column names.
    Tip. Take advantage of the 1st row content fields to see what kind of data you have in each column.
  • Use the Match case option to treat the records that differ only in the case as uniques.

Click Next to proceed to the last step.

Step 4: Decide what to do with the found values

Remove Duplicates lets you choose one of 6 ways to deal with the found records in Google Sheets:
Color, copy, or remove duplicates from your sheet.
Select the radio button next to the action you'd like to apply:

  • Highlight duplicate rows or other found entries by picking the Fill with color option. Click on the arrow next to it to pick the hue you'd like to use.
  • Choose to Add a status column to identify the rows of interest in a separate column.
    Add a status column.

    Tip. You can make use of this additional column later to sort the data.
  • The Copy to another location option lets you review search results in another sheet, spreadsheet, or a range of your choice.
  • Cut and paste the found records to another sheet, spreadsheet, or a specific place in the document with the Move to another location option.
  • Decide to Clear values to delete the found entries in the selected key columns and leave all other data intact.
  • You can also remove all lines with the detected values within your selection only...
  • Or Delete entire rows on the sheet including data outside the selected range.

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 results with the number of found values and the action that was applied:
See the summary with the number of found values.

Tip. If you'd like to run the add-on again, click on the Start over button.

To save these settings and use them in the future, click Save scenario.

How to work with scenarios

If you work with the add-on often, most likely you have to deal with going over the steps and choosing the same options over and over again. Scenarios will help you reduce the number of mouse clicks drastically.

Video: Remove Duplicates for Google Sheets – Automation

What is a scenario

Once the data is processed and you get the final message, you will have an opportunity to save the options the add-on has just used into a new scenario. In the future, run this scenario and it will initiate the search with the same settings right away.

You can also make the scenario run and find duplicates automatically at a certain time by itself.

Isn't this marvellous? :)

Save the scenario

To save the settings into a scenario, click the corresponding option under the result message:
Save the settings you've just used into the new scenario.
You will see the window with the summary of settings. Look through it to make some adjustments for the future scenario if necessary:

Brush the scenario before saving it.

  1. Give your scenario a name that will help you understand what it does at a glance.
  2. Schedule your scenario so it runs daily automatically at a certain time period. Find all the details about this option below.
  3. Identify a spreadsheet where your scenario will work:
    • Pick [Selected spreadsheet] and this scenario will process records in the file where you start it.
      Note. This option is not available whenever you choose to schedule the scenario. In that case, the scenario has to be tied to a certain file.
    • Specify a certain file for the add-on to handle it each time no matter what spreadsheet you run the scenario from.
    • Select any other sheet from Drive to connect the scenario to that file and process it on each scenario run.
  4. Set the sheet for the scenario to process:
    • Choose [Selected sheet] and the add-on will work with the tab that is active when running the scenario.
      Note. This option is not available whenever you choose to schedule the scenario. In that case, the scenario has to be tied to a certain sheet.

      It is also unavailable when you connect your scenario to a certain file since the tool can handle it even when it's closed. And in that case, there's no selected sheet.
    • Pick any sheet from the list and the add-on will always deal with it, no matter the tab you start the scenario from.
      Note. This doesn't support the [Selected range] option from below. For this range, you will have to start the scenario exactly from the sheet you want to process.
  5. Specify what data range should be scanned:
    • If you choose [Selected range], the scenario will be applied to the range you select manually.
      Note. For this range to be processed, make sure to run the scenario from the very sheet you want to modify.
    • [All data] signals the scenario to use the entire used range for work.
    • You can also enter an exact range for the add-on to always handle the same cells.
  6. Check if other options you've just selected on steps 2-4 are correct.
    Note. These options cannot be modified. If you'd like to change something here, please restart the add-on to pick the correct settings.

When your scenario is ready, hit Save.

Run your scenario

To start the scenario, go to Extensions > Remove Duplicates > Scenarios, find the necessary name and click Start.

Note. If you don't see Scenarios in the menu, just click Find duplicate or unique rows to open the add-on itself once. This is necessary for new spreadsheets and those where the add-on hasn't been run yet. Your scenario (and all future ones) will then appear in the menu automatically.

All scenarios can be run from the menu.
This will initiate the work of the Remove Duplicates add-on with all the necessary settings.

Once it's finished, you will see the result message saying what scenario has been used and what has been found:
The result for your scenario.

Edit or delete scenarios

You can view and manage your scenarios anytime from the Google Sheets menu.

Just go to Extensions > Remove Duplicates > Scenarios, pick the scenario you want to review, and click Edit:
Manage scenarios.
You will see the same window with the scenario summary and will be able to rename it, change the spreadsheet, sheet & range, and turn its autostart on & off:
Change the name of your scenario.
Press Save to keep the changes or Delete to get rid of the scenario permanently.

Share scenarios

You can export individual or all scenarios and share them with teammates or across your Google accounts.

Share certain scenarios

To share a specific scenario, go to Extensions > Remove Duplicates > Scenarios > select scenario > Export & share:
Export specific scenario from Remove Duplicates.
You’ll be asked to save the scenario to your computer:
Save the selected scenario to your computer.
Click Save, and your browser will open a dialog to select where to save the file. Once the download completes, close the add-on window.

Send this file to any spreadsheet collaborator who uses Remove Duplicates so they can import it to their tool.

Share all scenarios

To export all saved scenarios, head to Extensions > Remove Duplicates > Scenarios > Export & share all scenarios:
Share all saved scenarios in Remove Duplicates.
The tool will confirm that you’re about to save all scenarios to your computer:
Save all scenarios to your local storage.
Hit Save, and your browser will prompt you to choose a location for the file. After saving, you can close the add-on.

Share this file with other users of Remove Duplicates so they can easily import and use all scenarios.

Import scenarios

To import scenarios someone has shared with you, go to Remove Duplicates > Scenarios > Import scenarios:
Import scenarios in Remove Duplicates.
The add-on will prompt you to browse and select the file containing scenarios. Click Browse:
Select the scenario file from your device.
Once the scenarios are imported, you’ll see a confirmation:
Import completed successfully.

Tip. By default, all scenarios in the file will be imported. If any scenario already exists, the tool will inform you.

The imported scenarios will be available under Remove Duplicates > Scenarios, where you can use them or modify them as needed:
Find all imported scenarios in the add-on menu.

Run scenarios on schedule

Note. At the moment, you can schedule scenarios only in the standalone Remove Duplicates add-on. You won't find the feature in Power Tools for now.

When you create a scenario, you can make it run automatically once or twice a day. You don't have to keep the required spreadsheets open or your computer turned on. The scenario will be executed on schedule on the Google servers even when you're offline.

How to schedule your scenario

To make Remove Duplicates run scenarios automatically, tick off the Run on schedule checkbox:
The option to run the scenario on schedule.
Then scroll the scenario outline down. You'll see all the related settings available under the Schedule autostart section:
Schedule your scenario to run daily at a certain time.

  1. Pick the 1st time period from the 1st drop-down to kick-start your scenario automatically at least once a day.
    Note. Due to some Google Sheets peculiarities regarding time-driven triggers in add-ons, scenarios cannot be run at a certain time on the dot. They can be completed only within scheduled 1-hour intervals.
  2. To execute the same settings twice a day, tick off the 2nd run checkbox and choose an additional time interval.
    Note. At the moment, you can schedule only one run per hour since all add-ons in Google Sheets can use a time-driven trigger once per hour at most.

    You'll immediately see what time frames are already taken (either by the 1st run of the same scenario or by any other scenario):
    See if the time frame is already taken.
  3. Check the last box if you want to keep the log of the automated scenario performance.

    With this setting on, a new sheet called Scenarios schedule log will be added to your file. It'll show you when each scenario was executed, whether it finished successfully (and what's the result), and when the next start is. If the scenario fails, you'll see a short description of what happened so you could fix the problem or email us for help.

When you schedule the scenario, hit Save.

Manage scenarios timetable

You can see & easily manage schedules for all your scenarios from one place.

Go to Extensions > Remove Duplicates > Schedule:
Open the scenario timetable.
The summary timetable will appear featuring all your scenarios. You'll see which ones are already on the schedule and which are not:
See each scenario schedule + scenarios that are not set for autostart.

  • Click on the Schedule link next to the scenario you want to add to the timetable. You will see the scenario outline and be able to make it run automatically.
    Note. If the time interval is unavailable, it means it's already taken by another scenario:
    Another scenario is set to open at this time.
    At the moment, you can schedule only one run per hour since all add-ons in Google Sheets can use a time-driven trigger once per hour at most.
  • To change the set time frames or remove the scenario from the timetable, click on that scheduled scenario. The same outline will all the settings will appear letting you adjust what you need.

Hit Save to apply the changes, Cancel to go back to the summary timetable without saving the latest edits, or Delete to completely remove this scenario from your saved ones.

Run will call for the add-on and initiate this scenario right away.

Responses

Mario says:
October 26, 2020 at 2:11 am

Hello,
Is there any way to delete duplicates from several Google Sheets at once?

Mary Trifuntova (Ablebits Team) says:
October 26, 2020 at 7:29 am

Hello Mario,

Thank you for your interest in our product. I'm afraid it's currently impossible to process multiple sheets at a time. You need to run the add-on in each sheet you want to remove duplicates from.

However, scenarios are available for our Remove Duplicate Rows. You can save the settings you use most often and give this set a name – this is your scenario. Once you run it, your sheet will be processed right away, and you won't need to go over the same steps again. You'll only need to change the ranges and sheets to process as the scenario gets saved for the particular set of data.

If this workaround seems interesting to you, feel free to check the detailed instructions on how to work with scenarios over here:
https://www.ablebits.com/docs/google-sheets-find-duplicate-rows/#use-scenarios

Alternatively, there is a Quick Dedupe tool that removes your duplicates in just one step, check it out: https://www.ablebits.com/docs/google-sheets-find-duplicates/

Patrick Turner says:
June 15, 2020 at 1:39 pm

After duplicates are highlighted, how do you un-highlight them without the "undo" function?
Thank you.

Katerina Bespalaya (Ablebits Team) says:
June 15, 2020 at 2:44 pm

Hello Patrick,

Thank you for your question.

Please note that we always recommend creating a back-up copy of your original table by selecting the corresponding checkbox on step 1 of the add-on before processing your data. If this option doesn't suit you, then you can try to use the standard Fill color feature in Google Sheets to change the background of the highlighted rows or use the spreadsheet Version history feature (File -> Version history -> See version history) instead of the Undo function. If you have any other questions, please do not hesitate to ask.

Theresa says:
August 21, 2019 at 9:14 pm

Hello,

In June I purchase a 1 year subscription, and it's prompting me to enter my license number again. I can't seem to find my license number anywhere, and there doesn't seem to be a way to login to retrieve it.

Pablo says:
July 26, 2019 at 6:56 pm

Hi, i am user of remove duplicates, and have 2 accounts in google, can i use in both or get a discount for 2nd account?

Mary Trifuntova (Ablebits Team) says:
July 26, 2019 at 7:03 pm

Hello Pablo,
Thank you for your questions.

Ablebits subscription is account-based, meaning it can be activated under one Google account at a time. If you have several accounts, you need to acquire several subscriptions so there's one for each account.

If you purchase 2 subscriptions at a time, you automatically get a 20% volume discount. You can always make the purchase right from the add-on.

Please let us know if you have other questions or need any assistance.

yuriy says:
March 7, 2019 at 11:06 am

Hello!

If I buy license, I have the follow questions.

How many users can use the key?
How many google sheets docs can use the key per a person?
If I access for edit other google sheets (not owner). Can I add the software and use my key?

Natalia Sharashova (Ablebits Team) says:
March 7, 2019 at 11:16 am

Hello Yuriy,
Thank you for your questions.

Our subscription is account-based - one key can be used under one Google account at a time.
If the product is going to be used under different accounts at the same time, you need to have several subscriptions, so that there is one for each account.
You can use the add-on in both your spreadsheets and shared ones.

You will find these and other answers regarding our subscriptions on these pages:
Licensing: https://www.ablebits.com/docs/gsuite-add-ons-licensing/
Purchasing: https://www.ablebits.com/docs/gsuite-add-ons-purchasing/

Esther says:
February 13, 2019 at 4:45 am

How do you hide formulas in cells on shared google sheet without using importrange?

Irina Goroshko (Ablebits Team) says:
February 13, 2019 at 10:01 am

Hi Esther,
Thank you for the question.
For us to be able to assist you, please clarify, what do you mean by saying 'hide formulas'? Do you want to make them invisible? Or the task is to prevent other users from editing the formulas? Or maybe something else?

Thank you.

Devin McDonnell says:
December 14, 2018 at 8:48 pm

Hi, moths ago we paid for a lifetime license and were prompted to pay again today. Because we didn't have time to investigate the issue, we paid for another lifetime license in order to get up and running as quickly as possible. However, even after purchasing this second one, its still prompting us to purchase a lifetime license. As of now, we've paid for your service twice and still don't have access

Natalia Sharashova (Ablebits Team) says:
December 17, 2018 at 7:05 am

Hi, Devin,
Thank you for contacting us. I'm really sorry you're having this difficulty with our add-on.

I'll reply to you with all the details via email since the message will contain some personal info about your licenses.
Please check your Inbox for a message from Ablebits support team.

Thank you for your understanding.

Brian says:
December 8, 2018 at 10:56 am

Is there any way of setting the app so that I will run once each day or each hour?

thank you,

Brian

Natalia Sharashova (Ablebits Team) says:
December 10, 2018 at 8:04 am

Thank you for your question, Brian.
 
I'm afraid it's technically impossible to run our add-ons automatically at the moment.
However, since this is a common request, we are already working on a solution that will cut down the number of clicks on the same tasks. 
I can't tell you the exact timing yet but I can contact you back when the functionality is supported.
 
Please let me know if you have any other question.

James says:
December 18, 2021 at 6:03 am

Hello, just wondering if you have successfully implemented this enhancement yet? Am desperately in need of it!

Thanks

James

Natalia Sharashova (Ablebits Team) says:
January 10, 2019 at 9:54 am

Brian,

I'm happy to let you know that recently we've introduced scenarios to our add-on. Now you can automate repetitive tasks and check for duplicates in a click.
Just save the settings you use most often and give this set a name – this is your scenario. Once you run it, your sheet will be processed right away, and you won't need to go over the same steps again. You can view, edit, and delete scenarios quickly anytime.

Feel free to check the detailed instructions on how to work with scenarios in this part of the article:
https://www.ablebits.com/docs/google-sheets-find-duplicate-rows/#use-scenarios

It's a pilot feature, so your feedback is eagerly anticipated and much appreciated. :)
If you have any question, please contact us at support@ablebits.com.

Alex Maldonado says:
September 9, 2018 at 7:56 pm

How can I select duplicates across three [3] or more columns?

I am using "remove duplicates" to select duplicates across two columns and copy them to another column.

I am trying to do the same function but across 3 columns.

Does anybody know how to do this?

Thank you for your help!

Irina Goroshko (Ablebits Team) says:
September 10, 2018 at 2:43 pm

Hi Alex,
Thank you for using our product and for your comment.
If I understood you correctly, you need to compare three or more columns and select the values that are found in all the columns. For such a task better use the Compare Columns or Sheets tool.
Unfortunately, there is no way to compare more than two tables at once. Currently, there is only one option that you can use: compare two columns and copy duplicates, and then compare the result with the third column.
There is a workaround for your case, however. You can copy all columns into a single sheet and use the Find Duplicate or Unique Cells tool.

Garry says:
August 28, 2018 at 6:04 am

Hello - I want to buy this.... but what I need to do is to find duplicate words in a column in Google Sheets and export those found duplicate words to another Google sheet without removing them from the original. How do I do this and will this tool do that? Hope so :)

Thanks very much,

Garry

Natalia Sharashova (Ablebits Team) says:
August 28, 2018 at 6:38 am

Hi, Garry,

Thank you for your interest in our product!

If I understand your task correctly, our Find Duplicate Cells tool from Remove Duplicates will help you better:
https://www.ablebits.com/docs/google-sheets-find-duplicate-cells/

You'll be able to select your column as a range to search in, and then copy the found dupes to another sheet/spreadsheet.

You can test Remove Duplicates for free for 30 days to make sure it works as you need.
You can also share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task. We'll check how your data is stored and see if our software can help.

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.