Excel Tutorial: How To Keep Only Duplicates In Excel


Have you ever found yourself dealing with a long list of data in Excel and needed to remove all the duplicate entries? Keeping only duplicates in Excel can be a crucial step in data analysis and organization. In this tutorial, we will explore the importance of removing duplicates in Excel and provide an overview of the steps to achieve this efficiently.

Key Takeaways

  • Removing duplicate entries in Excel is crucial for data analysis and organization
  • The Remove Duplicates tool in Excel allows for efficient removal of duplicate entries
  • Customizing the duplicate removal process can help in keeping specific columns and values
  • Reviewing the results and utilizing additional tips can enhance the management of duplicates
  • Practicing and exploring other Excel tools is encouraged for further proficiency

Understanding the data

Before keeping only duplicates in Excel, it's important to understand the data and ensure that it's clean and accurate.

A. Identifying the data set

First, identify the data set that you'll be working with. This could be a single column or multiple columns containing the data that you want to analyze for duplicates.

B. Checking for duplicates

After identifying the data set, check for duplicates within the dataset. This can be done by using the "Conditional Formatting" feature in Excel to highlight duplicate values.

C. Reviewing the data for blank rows

It's also important to review the data for any blank rows that may affect the analysis. Use the "Go To Special" feature in Excel to quickly identify and delete any blank rows in the dataset.

Using the Remove Duplicates tool

When working with large datasets in Excel, it's often necessary to identify and manage duplicate entries. One way to achieve this is by using the Remove Duplicates tool.

Navigating to the Data tab

  • First, open your Excel workbook and navigate to the Data tab at the top of the screen. This is where you'll find the tools for managing and manipulating your dataset.

Selecting the data range

  • Once you're on the Data tab, select the range of cells that you want to work with. This could be a single column, multiple columns, or even the entire dataset.

Accessing the Remove Duplicates tool

  • With your data range selected, look for the Remove Duplicates tool in the Data Tools section of the Data tab. This tool can help you quickly identify and remove any duplicate entries within the selected range.

Customizing the duplicate removal process

When it comes to removing duplicates in Excel, there are various ways to customize the process to suit your specific needs. Here are a few key aspects to consider:

A. Choosing specific columns for comparison

Excel allows you to select specific columns for comparison when removing duplicates. This is particularly useful when you only want to consider certain data fields for identifying duplicate entries. By choosing the relevant columns, you can narrow down the comparison to focus on the specific data that matters to you.

B. Selecting unique or duplicate values to keep

Another important aspect of customizing the duplicate removal process is the option to choose whether to keep unique values or duplicate values. Depending on your analysis goals, you may want to retain either the unique entries or the duplicate entries. Excel provides the flexibility to make this selection based on your requirements.

C. Previewing the results before removing duplicates

Before finalizing the removal of duplicates, Excel offers a preview of the results. This preview allows you to review the entries that will be affected by the duplicate removal process. It enables you to double-check the outcome and ensure that it aligns with your expectations before making any changes to the data.

Reviewing the results

After keeping only duplicates in Excel, it is important to thoroughly review the results to ensure that the intended data is retained and no unintended deletions have occurred.

A. Analyzing the remaining data
  • Check for accuracy: Take a close look at the remaining data to ensure that only the duplicate entries have been retained and no unique values have been mistakenly deleted.
  • Compare with original data: Compare the remaining data with the original dataset to verify that all duplicate entries have been kept accurately.

B. Verifying that only duplicates are kept
  • Utilize Excel's filtering: Use Excel's filtering tools to isolate and verify that only the duplicate entries are present in the dataset.
  • Run duplicate analysis: Perform a duplicate analysis within Excel to confirm that all instances of duplicate entries have been retained.

C. Checking for any unintended deletions
  • Track unintended changes: Carefully review the data to track any unintended deletions or alterations that may have occurred during the process of keeping only duplicates.
  • Compare with backups: If available, compare the current dataset with backups or previous versions to identify any unintended deletions.

Additional tips for managing duplicates

When working with Excel, it's important to have the tools and knowledge to effectively manage and identify duplicate values in your data. In addition to the basic methods of finding and removing duplicates, there are several additional tips and techniques that can help you efficiently handle duplicate data.

Using conditional formatting to identify duplicates

Conditional formatting is a powerful feature in Excel that allows you to apply formatting rules to cells based on their content. This can be particularly useful for identifying duplicate values in a dataset.

  • Step 1: First, select the range of cells that you want to check for duplicates.
  • Step 2: Then, go to the "Home" tab and click on the "Conditional Formatting" option in the "Styles" group.
  • Step 3: Choose "Highlight Cells Rules" and then "Duplicate Values" from the dropdown menu.
  • Step 4: Select the formatting style you want to apply to the duplicate values, and click "OK".

Utilizing formulas to highlight duplicate values

In addition to conditional formatting, you can also use formulas to identify and highlight duplicate values in Excel.

  • Step 1: Use the =COUNTIF() formula to count the occurrences of each value in the range.
  • Step 2: Apply conditional formatting to the cells based on the results of the =COUNTIF() formula, highlighting the duplicate values.

Exploring advanced filtering options

Excel offers advanced filtering options that can help you manage duplicate values in your data more effectively.

  • Step 1: Select the range of data you want to filter.
  • Step 2: Go to the "Data" tab and click on the "Filter" option in the "Sort & Filter" group.
  • Step 3: Click on the dropdown arrow in the header of the column you want to filter, and select "Filter by Color" to filter and display only the duplicate values in the dataset.


Recap of the importance of removing duplicates: Keeping your data free from duplicates is crucial for accurate analysis and decision-making. Whether you're managing financial records, customer information, or any other type of data, eliminating duplicates ensures that your information is reliable and consistent.

Summary of the key steps in keeping only duplicates: In this tutorial, we've learned how to easily keep only duplicates in Excel using the conditional formatting and filtering tools. By following the simple steps outlined, you can efficiently identify and isolate duplicate data within your spreadsheets.

Encouragement to practice and explore other Excel tools: Excel is a powerful tool with a wide range of features beyond just removing duplicates. I encourage you to continue practicing and exploring other tools and functions that Excel has to offer. The more you familiarize yourself with its capabilities, the more efficient and effective you'll become in managing and analyzing your data.

Excel Dashboard

ONLY $99

    Immediate Download

    MAC & PC Compatible

    Free Email Support

Related aticles