How To Remove Duplicates But Keep Blank Rows In Excel – Guide

Microsoft Excel is a spreadsheet application developed by Microsoft for Windows, macOS, Android and iOS. It has calculation or calculation capabilities, graphical tools, pivot tables and a macro programming language called Visual Basic for Applications (VBA). Excel is part of the Microsoft Office software suite. Excel has the basics features of all worksheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations such as arithmetic operations. It has a battery of functions provided to respond to statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and graphs with a very limited three-dimensional graphical display. This allows the data to be sliced ​​to see the dependencies of different factors for different perspectives (using pivot tables and scenario managers). A PivotTable is a tool for data analysis. ..

PivotTable is a data analysis tool that allows users to simplify large datasets through the use of PivotTable fields. PivotTable is a programming tool that allows users to use a wide variety of numerical methods, for example, to solve differential equations in mathematical physics. Additionally, PivotTable has several interactive features enabling a user interface that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as an application, or decision support system (DSS), a custom design through the user interface provided by, for example, a stock analyst, or in general, as a design tool that asks the user questions and provides answers and reports.

How to Remove Duplicates but Keep Blank Rows in Excel

Remove duplicates Keep blanks with formula and remove duplicates

Remove Duplicates can be used to remove duplicates from a table, by adding an auxiliary column to identify blank rows first and then applying the Remove Duplicates function to remove duplicates.

In the adjacent cell, type this formula =IF(LEN(TRIM(A1))=0,ROW(),””), A1 is the first data in the list you want to remove duplicates, drag the autocomplete handle over the cells to get all blank line numbers.

Remove Duplicates

A dialog box appears to remind you of the number of lines removed. You can close it by clicking OK. ..

After the duplicates are removed, but keep the lines blank, you can remove the auxiliary column.

Select and highlight duplicates with duplicate and unique cells selected

The Kutools for Excel Select Duplicate & Unique Cells utility can help you select duplicate rows first and at the same time highlight them with background and font colors.

  1. Open Kutools for Excel and click on the “New” button.
  2. Enter a name for the new Kutools for Excel window and click on the “Create” button.
  3. In the “Name” field, enter “Kutools for Excel.”
  4. Click on the “OK” button to create the Kutools for Excel window.

Select the data, click Kutools > Select > Duplicate & Unique Cells.

In the Select Duplicate and Unique Cells dialog box, check the Duplicates (except 1st) option in the Rule section, select the Fill Background Color and Fill Font Color checkboxes, select the desired colors from the drop-down lists.

If you have selected more than one duplicate line, a dialog box will appear to remind you of the number of duplicate lines selected, click OK to close it. ..

The duplicate cells were selected and highlighted. ..

Final note

If you have any question about this guide, please do not hesitate to ask us. Additionally, please share your love by sharing this article with your friends.