Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
359 Views
