We can use two types of formulas (array or non-array) to remove duplicate values without deleting the corresponding rows in Google Sheets.

For better clarity, we may read the titles as follows.

• Remove duplicates but keep the rows.
• Remove duplicates and remain in position.

If you want to categorize the topic of this tutorial, you can consider it under data formatting, not data cleaning.

In Google Sheets, Clean, Unique, and Sortn are some of the functions that are useful for cleaning data.

Regarding menu commands, go to Data > Data validation and Data clean-up.

For data analysis, clean your data.

Our formula to remove duplicates without deleting rows has nothing to do with data cleaning. It’s all about formatting.

How to Remove Duplicate Values without Deleting Rows

To remove duplicate values without deleting the corresponding rows in Google Sheets, as I have mentioned at the very beginning, we can use a non-array or array formula.

Here our data is in cell range B2:B14, in which we will apply the non-array formula first.

You can use the formula in a sorted or unsorted list.

But in real life, you may find removing duplicates and keeping the rows worthy in a sorted list.

1. Remove Duplicates and Remain Position: Non-Array Formula

Please follow the below steps to remove duplicate values without deleting rows.

1. In cell C2, use =countif($B$2:B2, B2) to get the count of the first item in the range $B$2:B2.
2. Modify it to =if(countif($B$2:B2,B2)=1,B2,""). The IF logical test evaluates whether the COUNTIF result is 1 or greater than 1. If the value is 1, it returns the name from cell B2, else a blank.
3. Drag the C2 formula down.

That’s all that you want to do to remove duplicates and remain in position in Google Sheets.

2. Remove Duplicates and Remain Position: Array Formula

We should find a way to expand the above COUNTIF and IF parts of the formula.

We can take the help of a LAMBDA function for the former to spill down. But the latter requires only the ARRAYFORMULA support.

Instead of Lambda, we can use a much simpler COUNTIFS formula. We will use that with ARRAYFORMULA to remove duplicates without deleting the rows.

Please follow the steps below.

1. Empty the range C2:C14 and insert one running count array formula in cell C2.

=ArrayFormula(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14)))

2. Now let’s modify the above array formula to include IF to remove the duplicate values and remain position.

=ArrayFormula(if(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14))=1,B2:B14,""))

3. Add one more IF statement if you want the formula to include future values in the B2:B range.

=ArrayFormula(if(len(B2:B),if(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=1,B2:B,""),))

Removing Duplicate Values Without Deleting the Rows: Conditional Formatting

Here is another option we can consider as an alternative to removing duplicate rows without deleting the rows in Google Sheets.

It’s by using conditional formatting, which has two advantages over the above, and here are them.

1. It avoids the helper column usage.
2. We are making the duplicates invisible by changing the cell background and Text color. So the values are physically there. It helps with data manipulation.

Here are the steps involved.

1. Select the range B2:B14.
2. Go to Format > Conditional formatting to open the sidebar ‘Conditional format rules’ panel.
3. Under the sidebar panel tab ‘Single color’, scroll down to see ‘Format rules”. Select ‘Custom formula is’.
4. Insert the formula =countif($B$2:B2,B2)>1.
5. Just below under ‘Formatting style’ select the text color and the fill color to white.

That’s all about how to remove duplicate values without deleting the rows in Google Sheets.

Thanks for the stay. Enjoy!