How to Remove Duplicates and Replace With Blank Cells in Excel?


Excel worksheet duplicates can be a pain, confusing users and making data analysis more difficult. However, do not worry! This step-by-step tutorial will show you how to easily find duplicate entries in your data and delete them, replacing them with blank cells to create a cleaner, more organised spreadsheet.

Whether you've used Excel before or are just getting started, this video is made to be simple to follow and will give you useful tips and tricks to speed up your data management activities. By the end of this course, you'll have the knowledge and abilities necessary to deal with duplicate data efficiently, which will save you time and increase the precision of your studies.

Remove Duplicates and Replace With Blank Cells

Here we will first get any one of the values using the formula, then use the autofill handle to complete the task. So let us see a simple process to know how you can remove duplicates and replace them with blank cells in Excel to highlight a particular set of data.

Step 1

Consider an Excel sheet where you have list of items with duplicates.

First, click on an empty cell, in this case cell B2, and enter the formula as

=IF(A2="","",IF(COUNTIF($A2:A12,A2)=1,A2,"")) and click enter to get the first value.

Step 2

Then drag it down using the formula to complete the task.

This is how you can remove duplicates and replace them with blank cells in Excel.

Note

If you want to remove the cells if there are no duplicates in the range, then use the below formula as =IF(A2="", "", IF(COUNTIF(A$2:A$15,A2)>1,"",A2)).

The difference is that the first formula will remove cells if they are unique, and the second formula will remove cells if there are no duplicates.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove duplicates and replace them with blank cells in Excel to highlight a particular set of data.

Updated on: 07-Sep-2023

342 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements