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 But Keep Blank Rows in Excel?
Millions of users throughout the world use Excel, a strong tool, to efficiently organise and analyse data. Duplicate items can frequently lead to confusion and mistakes in your research when working with huge datasets. Even though Excel has a useful option to reduce duplicate rows, it often does so by removing all duplicated rows, even those that might include important blank cells.
This tutorial will walk you through the process of eliminating duplicate rows from your Excel spreadsheet while preserving blank rows. You will be able to effectively clean up your data by the end of this tutorial without losing any crucial data. This course is made to be user-friendly for beginners regardless of your level of experience with Excel. So let's get started and discover how to eliminate duplicate rows in Excel while keeping empty ones. For more accurate and effective work, purge your data and streamline your analysis!
Remove Duplicates But Keep Blank Rows
Here we will first number the blank rows, then remove the duplicate rows to complete the task. So let us see a simple process to know how you can remove duplicates but keep blank rows in Excel.
Step 1
Consider an Excel sheet where you have a list of duplicate values with blank cells, similar to the below image.
First, click on an empty cell, in this case cell B12 and enter the formula as
=IF(LEN(TRIM(A2))=0,ROW(),""), and click enter to get the first value. Then drag down using the autofill handle.
Empty Cell > Formula > Enter > Drag.
Step 2
Then select the range of cells, click on Remove Duplicates under Data, and click OK to complete the task.
Select Cells > Data > Remove Duplicates > Ok.
This is how you can remove duplicates but keep blank rows in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove duplicates but keep blank rows in Excel to highlight a particular set of data.
2K+ Views
