How to Remove Everything but Duplicate Rows in Excel?

You've come to the right place if you've ever had to work with enormous datasets that had duplicate items and wished to clear up your spreadsheet by keeping only those duplicates. Duplicate rows can complicate data analysis, clog up your data, and make it difficult to reach reliable findings. In this tutorial, we'll show you how to locate and keep duplicate rows while removing all other unique entries in Excel, which has a variety of strong tools and functions to handle and modify data effectively.

You'll have the skills essential to organise your data, save time, and improve the effectiveness of your Excel work by the end of the tutorial. You'll find this article to be simple to follow, giving you the abilities to handle duplicate data like a pro, whether you're an experienced Excel user or are just getting started with spreadsheets. So, let's get started and discover how to simplify your data and make it as helpful as possible by identifying, isolating, and removing all but duplicate rows from Excel!

Remove Everything But Duplicate Rows

Here we will first add a helper column, then filter cells, and finally use the Find and Select function to complete the task. So let us see a simple process to see how you can remove everything but duplicate rows in Excel.

Step 1

Consider an Excel sheet where you have a list of items with duplicate rows.

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

=COUNTIF($A$2:$A$13, A2)=1 and click enter. Then drag down using the autofill handle.

Empty Cell > Formula > Enter > Drag.

Step 2

Then select the range of cells and click on filter under data. Then click on the arrow and select only true, then click OK.

Select Cells > Data > Filter > Arrow > True > Ok.

Step 3

Then click on Find and select Home, then select Visible Cells, and click OK.

Find And Select > Visible Cells > Ok.

Step 4

Then right-click on the selected cells, select Delete Rows, and finally remove the filter.

Right-click > Delete Rows > Data > Filter.

This is how you can remove everything but duplicate rows in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove everything but duplicate rows in Excel to highlight a particular set of data.