How to Easily Find Duplicated Data in Google Sheets Using Dynamic Formula

You can follow a formula based approach to find duplicates in Google Sheets. There are three different approaches to find duplicate words/text strings or duplicate rows in Google Sheets.
I will try to explain to you all the three different approaches for finding duplicates in Sheets with examples below. Some of them are for extracting uniques means for removing duplicates.

The Three Formula Approaches to Find Duplicates in Google Sheets

  1. Using the UNIQUE function – Extracting unique values.
  2. With the help of the FILTER function – For testing duplicates.
  3. Using CONCATENATE and IF logical (dynamic formula) in combined form – This is for finding/marking a single value in multiple rows.
    1. COUNTIFS – This is for finding/marking multiple values in multiple rows (a killer formula).
Before going to the examples, let me explain to you how can we use these three approaches to find duplicate words, text or rows in Google Sheets.
In the above three methods, each one of them produces different outputs.

UNIQUE Formula – For Extracting Unique Values

If you follow the UNIQUE function method to find duplicates, you will be a little bit dissatisfied. The reason, this formula is not actually finding duplicates. It returns the unique rows after removing duplicates.

Filter Formula – For Filtering Specific Duplicates

This approach for finding duplicate values is entirely different. Here you can apply a filter to a range by providing certain conditions.
The formula will return the content matching your specified condition. For example, you can filter a string “sports” to get the rows containing the string “sports”.

Dynamic Formula for Finding Duplicates in Google Sheets

A Combination of CONCATENATE, ARRAYFORMULA and IF Logical Function

Mark the Duplicates of a Single Value in all the Rows
This may probably new to you. With this combination formula, you can easily check a string in a cell or strings in a row and mark all the rows that contain that string/strings. With this formula, you can check or verify any rows for the duplicate.
You can rewrite the above combination with CONCATENATE, ARRAYFORMULA and SWITCH Function.

Countifs for Finding/Marking All Duplicates

Mark the Duplicates of Multiple Values in all the Rows: You can read more about this at the example section below.

Examples – Find Duplicates in Google Sheets Using Formulas

Below is the sample data for the examples. In this sample data set, you can clearly see that there are multiple rows with the same content.
sample data for finding duplicate with formula

Find Duplicates in Google Sheets Using UNIQUE Formula

Find duplicate words, text strings, or duplicate rows in spreadsheets using the UNIQUE formula. This is a straightforward approach.
As told above, it’s not for finding duplicates, instead, it’s for removing duplicates and returning the unique content.
In any cell other than the above data range (use cell D15), apply the UNIQUE formula as below. Make sure that there are sufficient rows and columns to expand the result.
=UNIQUE(D1:F11)
Here D1:F11 is the entire data range. The result will be as follows after omitting the duplicates.
unique formula to remove duplicates in Google Doc
When you check the sample data, you can see that one book of Agatha Christie, “A Pocket Full of Rye”, repeats five times.
So the UNIQUE formula will return only one row from that. It’s applicable to all other books.
The UNIQUE formula checks entires rows in the range for duplicates. Not a single column in the range. For example, the values in D2:F2 is matching in any other rows.
You can limit the UNIQUE formula to any single column. But the result will also from that column only.
Example:
=unique(E2:E11)
This formula will return the unique books. When you want to unique only a single column like E2:E11 in Unique but want the result from all the columns D2:F11, you can use SORTN.
Replace the above Unique formula with the following SORTN. To know more read this – SORTN Tie Modes in Google Sheets.
=sortn(D2:F11,9^9,2,E2:E11,false)
SORTN and UNIQUE approaches in finding duplicates

Filter Formula to Find Duplicates in Google Sheets

This is a different approach. With FILTER formula, you can check the appearance of duplicate words, text, or even an entire row for duplicates.
In the below formula, D2:F11 is the entire data range. The formula checks the presence of “Agatha Christie” under column D, i.e. the range D2:D11, and returns matching rows.
=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE")
Filter Formula to Find Duplicates
The following FILTER formula will check Column D, i.e., range D2:D11, for “Agatha Christie” and Column E, i.e., range E2:E11, for “A Pocket Full of Rye” and will return the matching rows.
=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE",E2:E11="A POCKET FULL OF RYE")
Another example of Filter to Find Duplicates
You can further narrow down the result as below for bringing more uniqueness.
=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE",E2:E11="A POCKET FULL OF RYE",F2:F11=1953)

How to Find Duplicates in Google Sheets Using Dynamic Formula

CONCATENATE, ARRAYFORMULA and IF Logical Function – To Mark One Duplicate in Entire Rows

Here the approach to find duplicate words, text, numeric values or entire rows is different. See the below image.
How to Find Duplicates in Google Sheets Using Dynamic Formula
See column G where you can see duplicate rows corresponding to the first row (D2:F2) marked as “Yes”. How?
I’ve applied one formula in Cell G2 as below.
=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE(D2,E2,F2),"YES",""),""))
or
=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE("Agatha Christie","A Pocket Full of Rye",1953),"YES",""),""))
I will explain the formula part later. Now let us see what this formula does.
This dynamic formula checks Column D2, E2, and F2 and finds similar content in below rows.
That means this formula checks the repetition of rows with the content; author “Agatha Christie”, Book Name “A Pocket Full of Rye” and year 1953. If found, it returns “Yes” against that row.
When you want to check another author for duplicate, for example, the row D4:F4, modify the cell reference inside the Concatenate as below.
=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE(D4,E4,F4),"YES",""),""))
or
=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE("Bram Stocker","Dracula",1897),"YES",""),""))
How to Create the above Dynamic Formula to Find Duplicates in Google Sheets?
The above formula is actually a modified form of the simple formula below. This formula you can use in cell G2 and copy down.
=IF((D2&E2&F2)=CONCATENATE($D$2,$E$2,$F$2),"YES","")
With this simple formula, I’ve added ARRAYFORMULA to expand the result to below rows. So I can avoid copying down the formula. Also changed D2&E2&F2 to D2:D&E2:E&F2:F.
Additionally, I’ve used the LEN Google Sheet function to limit the array expansion.
The formula CONCATENATE(D2,E2,F2) is the same as D2&E2&F2. Both will return “Agatha ChristieA Pocket Full of Rye1953”.
Of course, I can use D2&E2&F2 = D2&E2&F2 instead of D2&E2&F2 = CONCATENATE(D2,E2,F2). But it may conflict with the ARRAYFORMULA. That’s the reason I’ve followed two different methods to obtain the same result.

CONCATENATE, ARRAYFORMULA and SWITCH Function – To Mark One Duplicate in Entire Rows

You can use Google Sheets SWITCH function to find duplicates in Google Doc Spreadsheet.
This is just similar to the above CONCATENATE, ARRAYFORMULA and IF Logical Function method.
The difference here is the use of Sheets SWITCH function instead of IF Logical.
Here is that magical formula to find duplicates. It’s very simple to understand if you know the use of SWITCH formula.
=ARRAYFORMULA(SWITCH((D2:D11&E2:E11&F2:F11),"AGATHA CHRISTIEA POCKET FULL OF RYE1953","YES"," "))
Switch Formula to Find Duplicates

Countifs – The Killer Formula to Mark Multiple Duplicates in Entire Rows

Countifs is the simplest way and seems the most favorite function of all Google Sheets users to identify duplicates.
=ARRAYFORMULA(COUNTIFS(D2:D11&E2:E11&F2:F11,D2:D11&E2:E11&F2:F11,ROW(A2:A11),"<="&ROW(A2:A11)))
The above formula has one resemblance with the =unique(D2:F11) formula. What’s that?
Unique and running count resemblance in Sheets
Wherever the Countifs return the number 1 in column H, that is the unique rows. Other rows are duplicates.
As a side note, the above formula is actually a running count formula.
The =unique(D2:D11) formula output can be marked using the running count=ARRAYFORMULA(COUNTIFS(D2:D11,D2:D11,ROW(A2:A11),"<="&ROW(A2:A11))) formula.
Hope you enjoyed this ultimate finding/marking/removing duplicates and data clean-up tips.