Друкарня від WE.UA

How to Find and Delete Duplicate Rows in Excel?

To find and delete duplicate rows in Excel, use Conditional Formatting to highlight repeated records and the Remove Duplicates feature to remove them. You can also use formulas to identify duplicates before deleting them. If you need to compare multiple Excel files or remove duplicate rows across several workbooks.

Understanding how to identify and remove duplicates rows in Excel helps maintain accurate spreadsheets, prevent repeated records, and improve data quality. Whether you are managing customer lists, invoices, employee records, or sales reports, duplicate rows can affect calculations and reporting results. This guide explains different ways to identify and remove duplicate rows in Excel.

Why Do Duplicate Rows Appear in Excel?

Duplicate rows can occur for several reasons, especially when data is collected from multiple sources.

  • Repeated Data Entry: The same record may be entered more than once.

  • Combining Worksheets: Copying information from multiple sheets can introduce repeated rows.

  • Importing External Data: Exported reports and downloaded datasets may contain duplicate records.

  • Merging Files: Combining workbooks without checking existing entries can create duplicates.

  • Manual Copying: Repeatedly copying and pasting data may add identical records.

Identifying the cause can help prevent duplicates from returning after cleanup.

Method 1: Find Duplicate Rows Using Conditional Formatting

Excel's Conditional Formatting feature helps highlight repeated values so you can review them before deleting anything. For complete duplicate rows, you should check the combination of values across the relevant columns rather than highlighting just one column.

Steps to follow:

  1. Select your dataset, including the columns you want to compare.

  2. Go to Home > Conditional Formatting > New Rule.

  3. Select Use a formula to determine which cells to format.

  4. Enter a formula that checks whether the combination of columns repeats.

  5. Choose a formatting style and click OK.

  6. Review the highlighted records and confirm which rows are duplicates.

For example, if columns A to C contain a name, email address, and city, use the following formula starting from row 2:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1

This highlights rows where the same combination appears more than once. Adjust the ranges to match your dataset.

Best for: Reviewing duplicate records before removing them.

Method 2: Delete Duplicate Rows Using Remove Duplicates

Excel includes a built-in Remove Duplicates feature that removes repeated records based on the columns you select.

Steps to follow:

  1. Open your Excel worksheet.

  2. Select the entire dataset or click a cell within the table.

  3. Go to Data > Remove Duplicates.

  4. Select the columns Excel should use to identify duplicates.

  5. Keep all relevant columns selected if you want to remove only completely identical rows.

  6. Click OK to remove duplicates and review the results.

Excel keeps the first occurrence of a duplicate record and removes subsequent matching records based on the selected columns.

Important: If you select only the Name column, Excel may remove records that share a name but have different email addresses or other details. Create a backup before proceeding because this operation deletes rows from the selected dataset.

Best for: Removing duplicates from a single worksheet quickly.

Method 3: Identify Duplicate Rows Using an Excel Formula

If you want to identify repeated records before deleting them, a formula can help you mark duplicate rows.

Suppose your data contains values in columns A, B, and C, starting in row 2.

  1. Add a new column named Duplicate Status.

  2. Enter this formula in cell D2:

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1,"Duplicate","Unique")
  1. Press Enter.

  2. Fill the formula down the column.

  3. Filter the Duplicate Status column to show only Duplicate entries.

  4. Review the marked rows and delete the confirmed duplicates if needed.

This formula labels subsequent occurrences of the same combination as duplicates while marking the first occurrence as unique. You can change the column references according to your worksheet.

Best for: Auditing data and deciding which repeated records to remove.

Method 4: Find and Remove Duplicate Rows Across Multiple Excel Files

The manual methods above are useful for individual worksheets. However, when duplicate rows exist across multiple workbooks, checking each file separately can take considerable time.

For this situation, you can use SysTools Excel Duplicate Remover, a dedicated utility for identifying and removing duplicate data across Excel files.

The tool can help users compare Excel data and remove duplicate records across supported workbooks without relying entirely on repeated manual checks. It is particularly relevant for business datasets, customer records, invoices, and consolidated reports.

How to use SysTools Excel Duplicate Remover:

  1. Download and install SysTools Excel Duplicate Remover from the official website.

  2. Launch the software and add the Excel files you want to process.

  3. Select the appropriate comparison options for your data.

  4. Configure the duplicate identification criteria according to the available features.

  5. Run the comparison and review the identified duplicate records.

  6. Follow the software's options to remove duplicates and save the resulting data.

Check the current product documentation for supported formats, comparison settings, and output options before processing important files.

Why consider an automated solution?

  • Helps manage duplicate data across multiple Excel files.

  • Reduces repetitive manual checking when working with larger datasets.

  • Supports a more consistent approach to duplicate identification.

  • Can be useful for teams managing recurring reports and consolidated workbooks.

Keep a backup of your original files before removing duplicate records, especially when working with business-critical information.

Comparison Between Manual Methods vs. Automated Tool

Feature

Manual Excel methods

SysTools Excel Duplicate Remover

Highlight duplicates

Conditional Formatting

Depends on supported comparison features

Remove duplicates in one sheet

Remove Duplicates

Supported workflow depends on product options

Review records first

Formulas and filters

Review available comparison results

Compare multiple workbooks

Requires additional manual work

Designed for duplicate management across Excel files

Best suited for

Small, individual datasets

Multi-file duplicate cleanup

Conclusion

Knowing how to find and delete duplicate rows in Excel helps keep spreadsheets accurate and organized. Conditional Formatting is useful for highlighting repeated records, Remove Duplicates quickly deletes matching rows, and formulas help identify duplicates before you make changes.

If you regularly handle large datasets or need to identify duplicate records across multiple workbooks, The professional solution offers a dedicated alternative to manual comparison. Choose the method that matches your data volume and always verify the results before saving the cleaned files.

Статті про вітчизняний бізнес та цікавих людей:

Поділись своїми ідеями в новій публікації.
Ми чекаємо саме на твій довгочит!
mishti
mishti@Ipp6i8gDKueIaSx

10Довгочити
184Перегляди
На Друкарні з 30 вересня 2025

Більше від автора

  • How to Convert ICS to HTML? Top Solutions Explained

    Learn how to convert ICS to HTML using manual and automated methods. Step-by-step guide to export ICS file to HTML securely and efficiently.

    Теми цього довгочиту:

    Technology
  • Convert MSG File to JSON: 3 Easy Methods

    Discover easy ways to convert MSG to JSON, preserve important email details, and handle attachments using manual techniques or an automated solutions.

    Теми цього довгочиту:

    Technology
  • Convert ICS to TEXT File Seamlessly Through Easy Ways

    Find the easy ways to convert ICS to TEXT file. Learn simple methods to make your calendar data accessible, editable without much time.

    Теми цього довгочиту:

    Technology

Це також може зацікавити:

Коментарі (0)

Підтримайте автора першим.
Напишіть коментар!

Це також може зацікавити: