Keep Only Duplicates In Excel: A Step-By-Step Guide

Keeping only duplicates in Excel can be a useful task when you need to identify and analyze duplicate data in your spreadsheet. Whether you're working with a large dataset or a small one, removing unique values and keeping only duplicates can help you identify patterns, errors, or inconsistencies in your data. In this article, we'll show you a step-by-step guide on how to keep only duplicates in Excel.

Understanding the Problem

Before we dive into the solution, let's understand the problem. Imagine you have a large dataset with thousands of rows and columns. You want to identify the duplicate values in a specific column, but you don't want to manually scroll through the entire dataset to find them. You want to keep only the duplicates and remove the unique values. This is where Excel's formulas and functions come in handy.

Method 1: Using the COUNTIF Function

One of the easiest ways to keep only duplicates in Excel is by using the COUNTIF function. This function counts the number of cells in a range that meet a specific condition. In this case, we'll use it to count the number of times a value appears in a column.

Here's how to do it:

1. Select the column that contains the data you want to analyze.
2. In a new column, enter the formula: =COUNTIF(A:A, A2)>1
3. Press Enter to apply the formula to the entire column.
4. Select the entire column with the formula.
5. Go to the "Data" tab in the ribbon.
6. Click on "Filter" and select "Filter by Condition".
7. Choose "Custom Formula" and enter the formula: =COUNTIF(A:A, A2)>1
8. Click "OK" to apply the filter.

Method 2: Using the REMOVE DUPLICATES Feature

Another way to keep only duplicates in Excel is by using the REMOVE DUPLICATES feature. This feature is available in Excel 2010 and later versions.

Here's how to do it:

1. Select the column that contains the data you want to analyze.
2. Go to the "Data" tab in the ribbon.
3. Click on "Remove Duplicates".
4. In the "Remove Duplicates" dialog box, select the column that contains the data you want to analyze.
5. Uncheck the box next to "My data has headers".
6. Click "OK" to apply the filter.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and analyze data. You can use Power Query to keep only duplicates in Excel.

Here's how to do it:

1. Select the column that contains the data you want to analyze.
2. Go to the "Data" tab in the ribbon.
3. Click on "From Table/Range".
4. In the "Power Query Editor", select the column that contains the data you want to analyze.
5. Click on "Group By" and select "Count".
6. In the "Group By" dialog box, select the column that contains the data you want to analyze.
7. Click "OK" to apply the filter.

Method 4: Using VBA Macro

If you're comfortable with VBA macros, you can use a macro to keep only duplicates in Excel.

Here's how to do it:

1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic.
2. In the Visual Basic Editor, insert a new module by clicking Insert > Module.
3. Paste the following code: Sub KeepDuplicates() Dim ws As Worksheet Set ws = ActiveSheet ws.Cells.RemoveDuplicates Columns:=1, Header:=xlNo End Sub
4. Click "Run" to apply the macro.

Conclusion

Keeping only duplicates in Excel can be a useful task when you need to identify and analyze duplicate data in your spreadsheet. Whether you're using the COUNTIF function, the REMOVE DUPLICATES feature, Power Query, or a VBA macro, there are several ways to achieve this task. By following the steps outlined in this article, you can easily keep only duplicates in Excel and remove unique values.

We hope this article has been helpful in guiding you through the process of keeping only duplicates in Excel. If you have any questions or need further assistance, please don't hesitate to ask.

Excel Duplicates Image Gallery