Maruf Niaz
When you have a large dataset, it is possible to have duplicate entries in the dataset. The duplicates may sometimes lead to unwanted errors in calculation. Removing duplicates even from a small dataset may become very tiring and time-consuming. To combat that, Google Sheets provides some easy solutions. In this article, we will try to show you how to remove duplicates in Google Sheets without shifting cells.
A Sample of Practice Spreadsheet
Download this spreadsheet to practice yourself.
3 Ways to Remove Duplicates in Google Sheets Without Shifting Cells
1. Using COUNTIF Function
Duplicates can be removed without shifting or moving the cells they are in by applying the IF function with the COUNTIF function in Google Sheets. We have a dataset with some Product Names and their Price in it. We will remove the duplicates and only show the unique entries in a separate column.
Steps:
- Go to the first cell of the column where you want to show the unique names. We go to cell D5 of our example.
- Then, insert the IF function.
- Now, insert the COUNTIF function.
- After that, type the range that you want the COUNTIF function to count the number of occurrences. We type $B$5:B5.
- Next, type in the criterion based on which you want the COUNTIF function to count. We type B5.
- Then type =1 as you want the data to show that it occurs only once.
- Now, type the value or data that you want the IF function to show if it matches the count of 1. We type B5 as we want to show the text in cell B5 to show.
- After that, type the value or data that you want the IF function to show if the count is not equal to 1. We type “” as we want the formula to return a blank cell if the count is not equal to 1.
- The final formula is:
=IF(COUNTIF($B$5:B5,B5)=1,B5,"")
- Finally, press ENTER and drag the autofill icon at the bottom of the cell to copy the formula to the cells below with the fill handle and it’ll show a list of unique names.
Read More: How to Remove Duplicates in a Column in Google Sheets (5 Ways)
2. Applying Filter Feature
The Filter feature can also be used to remove duplicates from datasets in Google Sheets. We use the following dataset for this.
Steps:
- First, go to the first cell of a new column named the Formula Column of the dataset. For our example, we go to cell D5.
- Then, type the formula:
=B4=B5
- After that, drag the autofill handle down to copy this to the other cells of the column. This will show a TRUE or FALSE result.
- Now, select the entire data range including the newly created Formula Column, and go to Menu bar > Data > Create a filter. We select range B4:D16.
- You will see the filter icon next to the column headings.
- Click on the filter icon on the formula column and check TRUE from the filtering options and click OK.
- Now, select the names in the Product names column.
- Then, delete these names by pressing the DELETE button.
- After that, click on the filter icon on the formula column and check both TRUE and FALSE, and then click OK.
- Now, you will see that only the unique Product names remain.
Read More: Google Sheets Use Filter to Remove Duplicates in Column
3. Utilizing Conditional Formatting
Conditional Formatting is a great tool that you can utilize to remove duplicates from your dataset.
Steps:
- First, select the data table and go to Menu bar > Format > Conditional formatting.
- You will see a side panel appear on the right named Conditional format rules.
- Then, select Custom formula is from the Format cells if drop-down menu.
- Now, type the following formula in the Value or formula box:
=COUNTIF($B$5:B5,B5)>1
Formula Explanation:
- B5:B5 is the cell range of the COUNTIF function.
- B5 is the criterion of the COUNTIF function based on which the Conditional formatting will apply the formatting.
- >1 implies that Conditional formatting will be applied to any cells that are greater than 1.
- Next, select white font color as well as white fill color from the Formatting style menu.
- Finally, press Done to remove the duplicates without shifting cells.
- This is how the final table looks:
Read More: How to Remove Duplicates in Google Sheets Using Formula
Conclusion
In this article, we showed you how to remove duplicates in Google Sheets without shifting cells in 3 different methods. Keep practicing the examples that we have shown here for a better understanding of the concept. We hope this article was useful to you.
Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.
