How to remove duplicates but keep blank rows in Excel?
In Excel, the Remove Duplicate function can help you remove duplicate values from a list quickly. However, if the list contains some blanks, the Remove Duplicate function will remove the blanks as well. How can you remove duplicates but keep the blank rows in Excel?
Remove duplicates keep blanks with formula and Remove Duplicates
Select and highlight duplicates with Select Duplicate & Unique Cells
Remove duplicates keep blanks with formula and Remove Duplicates
To remove duplicates keep blank rows, you need to add a helper column to identify the blank rows firstly, then apply Remove Duplicates function to remove the duplicates.
1. In the adjacent cell, type this formula =IF(LEN(TRIM(A1))=0,ROW(),""), A1 is the first data of the list you want to remove duplicates, drag the auto fill handle over cells to get all blank rows numbers.
2. Then select the data including the formula column, click Data > Remove Duplicates, then click OK in Remove Duplicates dialog.
3. A dialog pops out to remind you the number of removing rows. Click OK to close it.
Now the duplicates have been removed but keep blank rows. You can remove the helper column.
Select and highlight duplicates with Select Duplicate & Unique Cells
The Select Duplicate & Unique Cells utility of Kutools for Excel can help you select the duplicate rows first and at the same time, highlight them with background and font colors.| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select data, click Kutools > Select > Select Duplicate & Unique Cells.
2. In the Select Duplicate & Unique Cells dialog, check Duplicates (Except 1st one) option in Rule section, then check Fill backcolor and Fill font color checkboxes, select the colors you want from the drop down lists.
3. Click Ok, a dialog pops out to remind you the number of selected duplicate rows, click OK to close it.
Then the duplicate cells have been selected and highlighted.
Select duplicates & unique cells
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10! |
|
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 30-day trial of Office Tab! |
 |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
