Editor’s Note: Romain Vialard is a Google Apps Script Top Contributor. He has contributed interesting articles and blog posts about Apps Script. – Jan Kleinert
December 2011, updated March 2018
This tutorial shows how to avoid duplicates when you want to automate the process of copying data in Google Workspace and specifically how to remove duplicate rows in spreadsheet data.
Google Apps Script lets you copy email attachments from Gmail to a collection in Google Docs, sync spreadsheet data with a list page in Google Sites or with Google Calendar or with your contact list. The example shown below can be used to avoid duplicates in each of those cases.
Time to Complete
Approximately 5 minutes.
Before beginning this tutorial, you should feel comfortable using the Script Editor and have experience using the most basic Spreadsheet functions.
Running a simple example
Open a new spreadsheet in Google Docs or use an existing spreadsheet containing duplicates.
If the spreadsheet is empty, add a few rows of data (for example, a list of contacts, parts inventory, etc.) and duplicate some of them.
Choose the menu Tools > Script Editor.
Copy and paste the following script:
Save the script.
Select the function removeDuplicates in the function dropdown list and click Run.
Take a look at your spreadsheet. There should be no more duplicates.
How the script works
The first for loop iterates over each row in the data 2-dimensional array. For each row, the second loop tests if another row with matching data already exists in the newData array. If it is not a duplicate, the row is pushed into the newData array.
To finish, the script deletes the existing content of the sheet and inserts the content of the newData array.
In the example above, the script finds a duplicate when there are two identical rows, but you may also want to remove rows with matching data in just one or two of the columns. To do that, you can change the conditional statement.
This conditional statement finds duplicates each time two rows have the same data in the first and the second column of the sheet.
Reuse the method
Each time you need to copy data from one point to another you may want to check if this data has already been copied. For example, if you want to sync a label in Gmail to a collection in Google Docs to automatically retrieve important attachments, the solution shown above can be used.
- First, retrieve the data you want to copy (from Gmail, Calendar, a spreadsheet, etc.)
- Then, retrieve the data already stored in the targeted folder, spreadsheet, site, etc.
- For each item you want to copy:
- Make the assumption that you want to copy this item by the use of a boolean, for example: var toCopy = true; .
- For each item stored in the targeted folder, check if it looks similar to the item you want to copy.
- If it looks like the item has already been copied, then you don’t want to copy it again, so set toCopy = false; .
- Once you have read all items stored in the targeted folder, check if toCopy is equal to true or not. If it’s true, then copy the item.
There are other solutions to avoid duplicates. For example, you can tag each item as ‘already processed’ once they have been copied. An example can be found in this tutorial: Sending emails from a Spreadsheet. Or, you can remove the item from the list of items to copy.
Congratulations, you’ve completed this tutorial. You should now be able to avoid duplicates when manipulating data with Apps Script.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Updated on January 3, 2021 by Swayam Prakash
Data redundancy is a common problem in database applications like Excel and Google Sheets. During manual data entry in the worksheet, some info may get recorded more than once. This leads to data duplication or redundancy. In this guide, I will show you how to remove duplicate data from Google Sheets.
I have mentioned a few tricks to highlight and get rid of similar information from the Google Sheet. There is a function that is native to Sheets. People commonly use this to find and remove repetitive information. Also, there is a dedicated in-built add-on that helps to remove duplicate data in Sheets. Every option I have mentioned is free to use.
Table of Contents
Highlight and Remove Duplicates from Google Sheets
You can use any of the methods I have mentioned below.
Remove Duplicates Tool
This is very easy. Suppose I have a list of bikes and their engine displacement. If there is one bike and its displacement details repeated twice, then this tool will remove one of it
- Select the dataset in the Google Sheet. You may notice the data collection has two values from each data set repeated.
- Click on the option Data in the menu bar > scroll down to Remove Duplicates
- Then click the checkbox Data Has Header Row
- Also, click on the checkbox Select All
- Finally, click on Remove Duplicates. You will see the below message of the removal of duplicate data.
If you ask me, this above method is the easiest method to get the job done in removing duplicate entries in the Google Sheet. However, there is a catch. If the duplicate entry has a spelling error or you have up put any space, then the two duplicate values will not match. Hence, the Remove Duplicates tool will not work.
Remove Duplicates Using the UNIQUE Function
Google Sheets has another easy function that can help weed out the duplicate entries in the worksheet.
- Simply put the cursor on any blank cell
- Type this formula =UNIQUE(A2:B10)
- Press Enter
A2 and B10 are just examples. If you notice in the screenshot above, these cell numbers are the initial cell of one dataset and the final cell of the second dataset. You can accordingly mention the cell number in the formula as per your Google Sheet database.
Then after pressing enter the redundant value will be omitted and a new list will show up. This list won’t have any duplicate entries. See, how simple was that.?
Using Add-Ons to Remove Duplicates in Google Sheets
In Google sheets, you can also use add-ons to remove duplicate entries from the worksheet. First, you have to install the Add-on. It is free to download.
- Open Google Sheets
- In the menu bar, click on Add-ons
- Then select Get Add-ons
- Then in the search-box type Remove Duplicates
- In the results, click on the Remove Duplicates from AbleBits
- Then click Install and use the same Gmail account that you use to work on Google Sheets to give the add-on permissions.
Now, let’s see how to use the add-ons. It’s pretty straight-forward.
- Select the dataset in the sheet
- Then in the menu bar click on Add-On
- Under that expand to Remove duplicates > click Find Duplicate or Unique Rows
- Now you have to go through 4-steps.
- In the 1st, click on the checkbox that says Create A Backup of the sheet and check that the range of the dataset has been mentioned correctly.
- Then click Next
- In the 2nd step, click on the radio button Duplicates and click Next
- Next, in the 3rd step select the checkboxes Skip Empty Cells, My table has headers and Match case
- In the last and 4th step select the radio button Delete Rows Within Selection.
- Then click on Finish
- You will see a message that the duplicate row in the dataset has been removed. That’s it.
So, that’s how you remove duplicates from Google Sheets using various functions and add-ons. All the methods are quite easy to carry out. I hope that this guide was informative.
If you want to remove duplicates in Google Sheets, you need to use the UNIQUE function. This function extracts all the unique values and puts it in another place on the sheet.
This is very different than the way you can remove duplicates in MS Excel. In Excel, there is an option in the data tab to remove duplicates.
This Article Covers:
Remove Duplicates in Google Sheets
Suppose you have a dataset as shown below:
In this data, there are repetitions in names (Mark and Brenda).
Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.
Example 1: Remove Duplicates from a Single Column
Let’s say you only want to remove the duplicates from the Name column.
Here are the steps to do this:
- Select a cell where you want to get the list of unique items/names.
- Enter the following formula: = UNIQUE ( A2:A10 )
- Hit Enter.
This would instantly remove the duplicates and give you a list of unique names.
Note: If you want to remove the list, delete the first cell, or select the entire range and hit delete. Google Sheets will not let you delete individual cells (other than the first one – which would delete the entire list).
Since this is a formula, it would automatically update if you make any changes in the original list.
UNIQUE function returns a #REF! error if there is already some data in the cells that are supposed to be filled by the unique function.
If you hover the mouse over the cell with error, it will show a message stating the issue.
Handling Leading and Trailing Spaces
Another issue you may face is when there are leading or trailing spaces. For example, if the example below, there is a trailing space in Brenda (in A10). When the UNIQUE function is used on this data set, it considers both the names as unique and returns both the names in the result.
Here is how to handle this:
- Go to the cell where you want to get the list of unique items/names.
- Enter the following function: = UNIQUE ( TRIM ( A2:A10 ) )
- While in the eidt mode, press Ctrl + Shift + Enter. It would change the formula to = ArrayFormula ( UNIQUE ( TRIM ( A2:A10 ) ) ).
- Press Enter.
This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.
Example 2: Remove Duplicates from Multiple Columns
If you have more than one column, you can use the above method to remove duplicate rows and get unique data.
Suppose you have the data as shown below:
In this data, there are two duplicate rows (highlighted in orange and green).
Here are the steps to remove these duplicate rows:
- Select a cell where you want to get the list of unique items/names.
- Enter the following formula: = UNIQUE ( A2:B10 )
- Hit Enter.
If there are leading, trailing or double spaces, use the following formula:
I hope this helps you cleaning your data in Google Sheets. Do let me know your thoughts by leaving a comment.
You May Also Like the Following Google Sheets Tutorials:
1 thought on “How to Remove Duplicates in Google Sheets”
Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. I love excel tricks. Thanks!
17 Sep 2014
Google Spreadsheet (now called Sheets as part of the Google Drive productivity suite) is turning out to be a real MS Excel competitor. The amount of stuff Excel can do is truly mind-boggling. But Sheets is catching up. More importantly it is beating Excel by integrating add-ons and scripts in an easy to use manner. Plus there’s a long list of functions for quick calculations.
Using Google Sheets to do serious work means dealing with a serious amount of data. Data that’s often not sorted or organized. In such times you want an easy way to get rid of duplicate data entries. And let’s face it, searching for and deleting recurring data sets manually is inexact and time consuming.
Thanks to Sheet’s support for add-ons and functions, this process can be taken care of in mere seconds.
Remove Duplicates Add-on
We talked some cool Google Docs add-ons for writers last time. The Remove Duplicates add-on can be installed directly from the spreadsheet view. From the Add-ons menu select Get Add-ons. Search for Remove Duplicates and install it. In a couple of seconds, the add-on will be ready to work.
Once installed, select the cells in question and go to Add-ons -> Remove Duplicates. This will bring on a multi-step popup menu where your selection will already be accounted for.
In the next step you can choose if you want to find duplicates or uniques.
Now you can choose to highlight the duplicates in a color you like. From here you can also ask the add-on to directly delete such occurrences.
The add-on will take a couple of seconds and then present you with its findings.
While the add-on works great when you’re just using just one row, it gets wonky when you try using multiple rows.
Using The Unique Function
With this method, leave a couple of blank rows from where your data is and enter the function “=UNIQUE()”. After you’ve opened the parenthesis, select the cells you want to filter. You can also select rows. Close the parenthesis and press Enter.
You’ve just created a copy of the data you had, only no repeating values were included. Now delete the preceding rows/cells and you’re done.
Any other methods you know of removing duplicates quickly from a Google Sheet?
Last updated on 8 Feb, 2018
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
How to Restrict Editing Cells in a Google Docs Spreadsheet
Learn How to Restrict Editing Cells in a # Google Docs Spreadsheet.
How to Easily Create Graphs and Charts on Google Sheets
Are you terrified by the mere idea of creating graphs & charts on # Google Sheets? Explore this comprehensive guide to create charts on Google Sheets like a pro.
Top 11 Google Slides Animation Tips and Tricks to Use It Like a Pro
Excited about adding animations and transitions to # Google Slides? Check out 11 tips and tricks to add and edit animations in # Google Slides.
7 Best Fixes for Spotify Keeps Pausing on Windows and Mobile
A list of all the ways to fix # Spotify pausing on # Windows and # mobile devices. This is a device-specific issue that can be easily fixed using one of the solutions discussed.
3 Best Ways to Share Google Forms With Others
Do you want to send # Google Forms to others to fill or edit it? Check out three smart ways to share # Google Forms with others.
How to Listen to Spotify Offline on Mobile and Windows 10
Can’t find the # offline option in # Spotify app? Click here to learn how to listen to Spotify offline on all your desktop and # mobile devices.
3 Ways How Google Photos Will Count Against Google Drive Storage
Wondering how to manage # Google # Photos # storage with your # Google Drive? Here are the 3 ways that will illustrate how your # Google Photos will count against the Google Drive # Storage
Top 9 Fixes for YouTube Restricted Mode Won’t Turn off on Mobile and PC
Unable to turn off Restricted mode on # YouTube? Check out 9 fixes to solve the issue of cannot turn off YouTube Restricted mode.
Did You Know
Since its launch, Shazam has been used more than 30 billion times to identify songs.
While working in Google Sheets, you may run over huge bookkeeping pages where you need to manage many copy passages. We comprehend the torment of managing copies and how troublesome it would get if you somehow happened to feature and eliminate sections individually. Be that as it may, with the assistance of contingent designing, featuring and eliminating copies turns out to be pretty simple. Follow this guide as we disclose to you how to discover and eliminate copy passages in Google Sheets.
Google Sheets: How to feature copies in a solitary section
Prior to realizing how to eliminate copy sections from Google Sheets, how about we figure out how to feature copies in a solitary segment. Follow these means.
Open your accounting page in Google Sheets and select a segment.
For example, select section A > Format > Conditional designing.
Under Format rules, open the drop-down rundown and select Custom recipe is.
Enter the Value for the custom recipe, =countif(A1:A,A1)>1.
Underneath Format rules, you can discover Formatting styles, which lets you set an alternate tone for featured copies. To do that, hit the Fill shading symbol and select your favored shade.
Whenever you’re done, hit Done to feature the copies in a solitary section.
Essentially, in the event that you need to do it for segment C, the equation becomes, =countif(C1:C,C1)>1 and you do it so on for different sections also.
Moreover, there’s a method to discover copies in the sections as well. To learn, follow these means.
We should accept you wish to feature copies between cells C5 to C14.
All things considered, go to Format and choose Conditional Formatting.
Under Apply to go, enter the information range, C5:C14.
Next, under Format rules, open the drop-down rundown and select Custom recipe is.
Enter the Value for the custom recipe, =countif(C5:C,C5)>1.
Set an alternate tone for the featured copies by following the prior advances on the off chance that you wish to. Whenever you’re done, hit Done.
While Google Sheets doesn’t have quite the same depth of features as Microsoft Excel, it still has a number of useful tricks under the hood. One of its most useful features is conditional formatting.
This allows you to change the formatting of any cells that obey a specific set of conditions that you create. You could, for instance, highlight all of the cells that are above a certain value, or contain a specific word. Conditional formatting has a number of uses, such as highlighting errors or comparing data.
Another useful application of conditional formatting is highlighting duplicates. This allows you to quickly spot any duplicate text or values within a large spreadsheet. Here’s how it’s done.
Highlighting Duplicates From a Single Column
If you want to highlight duplicates from a single column in Google Sheets, you’ll need to first select the column where you want to find duplicates.
With your column selected, click Format > Conditional Formatting from the menu bar.
If you already have conditional formatting active in that column, click Add another rule. Otherwise, you can edit the rule that is currently showing.
Press the Format Cells If box and choose Custom formula is from the dropdown options.
Type =countif(A:A,A1)>1 into the Value or formula box or replace all instances of the letter A in the formula with your chosen column.
For example, to apply the formatting to column M, your formula would be =countif(M:M,M1)>1
You can set the formatting style yourself, by selecting the color and font options. You can also use one of the various preset formatting styles by clicking on the Default text under the Formatting style options, then selecting one of the presets.
Click Done to add your conditional formatting rule. Any duplicate cells should now appear with your chosen highlighting.
Highlighting Duplicates From Multiple Columns
It’s possible to apply this formatting to more than one column, highlighting any duplicates that appear across your selected cell range.
To begin, select the columns that you want to include, hold down the Ctrl key, then click on the letter at the top of each column to add individual columns.
You can also hold down the Shift key and click on the first and last cells in your range to select multiple columns at once.
With your cells selected, press Format > Conditional Formatting.
Edit the current rule or press Add another rule to add a new rule.
Press the Format Cells If… option and choose Custom formula is.
Type =COUNTIFS($A$1:Z,A1)>1 into the Value or formula box. Replace each A with the letter of the first column in your selection and Z with the last column in your selection.
For example, if your data was in columns M to Q, then =COUNTIFS($M$1:Q,M1)>1 would be the correct formula.
You can select your own formatting style in the Formatting style section or click on the Default text to apply one of several preset formatting options.
Click Done to confirm and save the rule—your duplicates will now be highlighted across all the columns that you selected.
Any columns that you missed out will be not be highlighted, but will still count towards the number of duplicates.
Using Powerful Google Sheets Features
Conditional formatting is a useful feature that allows you to easily spot duplicated information across a large spreadsheet, but there are plenty of other features that can be incredibly useful for Google Sheets beginners and regular users.
You may wish, for instance, to add in-cell validation to your spreadsheets to improve the quality of your data. If you’re looking to create automated tasks, you may want to look at using Google Sheets to send emails based on cell values.
Google Sheets is used by numerous people all around the globe as a reliable tool for data entry. No matter personal or official, one can create, edit and collaborate with others on spreadsheets from supported gadgets with the Google Sheets. You might have used the Google Sheets to get your work done. However, there might be a case when duplicates in Google Sheets entries ruin your whole venture. In case if you’re looking for nifty workarounds to fix the nuisance with the duplicates, let’s see how can it be avoided.
Thankfully, Google Sheets features some indirect mechanisms to eliminate the duplicates. Here, we’d be going through some of them.
Guide to Remove duplicates in Google Sheets
Using the Addon – Duplicate Remover
Google Sheets support a plethora of Add-Ons which can be installed based on your need and purpose. For removing Duplicates, Google Sheets has an add-on called Duplicate Remover. Using the add-on we can remove duplicates with few 3-4 steps.
- On Google Sheets, go to Tools>>Get the add-on
- Enter duplicate on the search bar to see all the available tools with duplicate as a keyword
- From the available options, choose Duplicate Remover from Adlebit
Once installed, Duplicate Remover will be featured on the Google Sheets. So, in order to remove duplicates, Go again to Tools and choose the add-on, Remove Duplicates.
The add-on will take you to 3-4 series of steps to get your preference. Moreover, options such as whether you want to highlight duplicates, delete them, create a new copy of your data and such are available.
2. Conditional Formatting
In order to remove duplicates using conditional formatting, we need to go through two steps. The numero uno is to highlight the duplicates, and then to remove the highlighted contents.
- Highlight the duplicates
- Remove the highlights
First of all, we can highlight the duplicates, for that select your dataset and open the conditional formatting sidebar (under the Format menu).
Under the “Format cells if…” option, choose custom formula is (the last option) and enter the following formula.
The duplicates in column A will be highlighted on your data set.
3. Using Pivot Tables
Pivot Tables are useful especially for exploratory data analysis. Pivot Tables can be used to search for duplicates in Google Sheets. They’re flexible and fast to use, also helps to phase out any duplicates in your data.
- Highlight your dataset and create a Pivot Table (under the Data menu).
- A new tab opens with the Pivot Table editor.
- Under ROWS, choose the column you want to check for duplicates (e.g. invoice number). Then in VALUES, choose another column (I often use the same one) and make sure it’s set to summarize by COUNT or COUNTA (if your column contains text), like this:
You can see that duplicates values (for example 196-X) will have a count greater than 1. From here you can look up these duplicate values in your original dataset and decide how to proceed.
Apart from the above mentioned three methods to remove duplicates, there do exist some other method such as by App Script. However, these are the simplest method which you can use to phase out the duplicates.
Removing duplicates in Google Sheets is the act of removing data that appears more than once or removing duplicate entries in your sheet.
This should be done before carrying out any data analysis to avoid errors and prevent unnecessary corrections.
Today in this post, I will show you how to remove duplicates in Google Sheets using any of the available methods.
Google sheet or spreadsheet is now serving the same purpose as MS Excel, which is being powered by the Google Drive Suite.
However, the Google sheet is becoming more common and advanced with the help of easy to use scripts and add-ons.
As well, providing the full list of various functions to help you in quick computing and calculations.
Making use of the Google sheets to sort and organize a vast amount of data can be tiring and stressful, especially getting rid of duplicate data entries.
Hence, if you know how to find duplicate data entries in the Google sheet, then removing them will be a great move.
But, if you don’t know how to achieve this, then, this guide will be of help to you anytime any day.
With the help of Google drive by integrating add-ons and functions, getting rid of duplicates in Google sheets can be done in a few minutes.
Let’s quickly see the available methods on how to remove duplicates in Google Sheets.
Table of Contents
How to Remove Duplicates in Google Sheets
There are so many methods on how to remove duplicates in Google sheets, which includes;
How to remove duplicates with an Add-On.
How to remove duplicates with Apps Script.
How to remove duplicates with Pivot Tables.
How to remove duplicates in Google sheets with the UNIQUE Function.
How to remove duplicates in Google sheets with Conditional Formatting.
How to remove duplicates in Google sheets with the Remove Duplicates tool.
Nevertheless, this guide will teach you how to remove duplicate cells in Google sheets using the UNIQUE Function.
This is the best and easiest method so far, and it works perfectly well for me.
So, let’s quickly see the steps to follow below here.
How to use the UNIQUE Function to Remove Duplicate Cells
Step 1: First, visit the site here on https://sheets.google.com in your internet browser.
If you are not signed in yet, then sign in to your Google account and a list of Google spreadsheets files will be displayed.
Step 2: Select the particular file you want to edit that has rows/columns containing duplicates data entries in the cells.
Step 3: Click on an empty cell away from the row/columns where your data is located at.
Then, you will now enter the Function in that cell.
Step 4: On the empty cell, type in “=UNIQUE(range),” and replace “range” with the cell range where the duplicates occur.
Example, as seen in the image below, to find the duplicates in column A (Boy Names) from row 2 to 8, we type =UNIQUE(A2:A8).
Step 5: After typing in the Function, quickly press the Enter key on your computer keyboard.
It will then create a new cell range containing the data entries but without the duplicates.
You can now copy and replace the original cell range with the new data cells having no duplicates.
If you no longer want the data in the original cells, highlight, and copy, then paste in the first column of the original cells.
You can achieve that using CTRL+C for copy and CTRL+V to paste.
That is all on how to remove duplicates in Google sheets and how to merge duplicates in Google sheets.
With the help of this UNIQUE Function , you can quickly sort and remove duplicates in Google sheets easily.
Therefore, take advantage of this today, and you will be glad you did.
Remember to share this article with your friends and on social media platforms.
How to quickly highlight cells with duplicate values in Google Sheets using conditional formatting. The duplicate cells can be easily removed from the spreadsheet with Google Script.
The Email Extractor app pulls emails addresses of your contacts from Gmail into a Google Sheet. You can then use Mail Merge or Document Studio to send personalized emails to your contacts from within the sheet itself.
That said, the imported mailing list may sometimes have duplicate email addresses and it is thus be a good idea to clean up the data in your Google Sheet by removing duplicates before sending out the emails.
Highlight Duplicates in Google Sheets
You can use Conditional Formatting in Google Sheets combined with the COUNTIF formula to quickly highlight cells that contain duplicate values.
Here’s how you can find duplicates in Google Sheets:
Open the Google Sheet containing your data and go to the Format menu.
Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.
In the Apply to Range input box, add the range of cells that contain possible duplicates. In our case, the email addresses are in column A so we can put A:A to specify the entire A column.
Under the ‘Format cells if’ section, choose “Custom formula is” from the dropdown list as set the formula as =COUNTIF(A:A, A1) > 1
Click the Done button and you’ll instantly notice that all duplicate cells are highlighted as shows in the screenshot below.
The COUNTIF Function
The COUNTIF function in Google sheets (and Microsoft Excel) essentially counts the number of cells in the range that meet a specific criteria. For instance =COUNTIF(A:A, “apple”) will count the number of cells that contain the word apple.
It can accept wildcard characters too so =COUNTIF(A:A, “apple?”) will count cells that contain the word apple or apples. Or say =COUNTIF(A:A, “*@gmail.com”) and it will highlight all email address that end with a gmail address.
Please note that the COUNTIF function is case-insensitive so values like gmail.com and Gmail.com are seen as duplicates.
Highlight Entire Row Containing Duplicates
If you’ve noticed in the previous screenshot, only specific cells that have duplicate values are highlighted through conditional formatting.
However, if you would like the Google Sheet to highlight the entire spreadsheet row that contains duplicate values, we need to slightly tweak the formatting rule.
Go to the Apply to Range input box and specify the entire spreadsheet range, not just the column that contains duplicates.
In the custom formula,use absolute reference for the range and also change criterion to use $A1 instead of A1 . When we use $A1 , we are telling Google Sheet to only change the row but lock the column.
The new duplicate detection formula reads =COUNTIF($A$1:$C$50, $A1)>1
Compare Multiple Columns for Duplicates
If you would like to determine duplicates by comparing data in multiple columns of the Google Sheet, use COUNTIFS instead of COUNTIF .
For instance, if column A is Email Address and column B is Company Name and you would like highlight duplicates cell only when the combination of values in Column A and B is identical, the new formula can be written as =COUNTIFS(A:A, A1, B:B, B1)>1
Remove Duplicate Rows in Google Sheets
Now that we have figured out a simple method to highlight duplicates in Google Sheets, the next task is to remove all duplicate rows.
There are two ways to go about it – either use Google Apps script or use the built-in feature of Google Sheets to remove duplicates.
First, highlight the entire column in Google Sheet that contains the duplicate data. Next, go to the Data menu and choose the Remove Duplicates option.
Select which columns to include and whether or not the selected range has any header row. Click Remove duplicates and your list is clean up in one go. Like with COUNTIF function, Google Sheets will ignore case and formatting when determining duplicates.
Remove Duplicates with Google Scripts
If you prefer automation, here’s a little snippet that will remove the duplicates in your active Google Sheet based on data in the first column.