How to delete duplicate data in batches in Excel? Two methods are recommended!

Duplicate data may become a problem when working in Excel. They impact data accuracy and hinder analysis and decision-making. In order to solve this common problem, PHP editor Xiaoxin will introduce several efficient PHP methods in this detailed guide to help you easily identify and delete duplicate data. This article will cover a variety of techniques, from built-in functions to custom scripts, to provide you with the information you need to keep your Excel data tidy and free of duplicates.

This article will introduce two methods to delete **duplicate** data in Excel in batches, and further explain how to protect the organized **data** from being modified at will.

Method 1: Use the "Delete Duplicates" function

This method is simple and straightforward and suitable for most situations. Let's take the following picture as an example to see how to operate.

1.Select the data range

After opening the Excel file, click and drag the mouse to select the area containing repeated data, or click the button in the upper left corner of the table to select the entire worksheet.

2. Turn on the "Delete Duplicates" function

In the menu bar, click the "Data" tab, then find and click the "Remove Duplicates" button in the "Data Tools" group.

3.Select the columns to check

After the "Remove Duplicates" dialog box pops up, we can select the columns to check for duplicate data. If the data contains header rows, you need to check the "Data contains headers" option.

4. Confirm to delete duplicates

After selecting the columns you want to check, click "OK". Excel automatically checks for duplicate data in the selected range and removes duplicates.

After the deletion is completed, Excel will pop up a dialog box showing how many duplicate values ​​have been deleted and the number of unique values ​​remaining.

Method 2: Use advanced filtering function

This method is suitable for situations where duplicate data needs to be processed more flexibly. Let’s take the following picture as an example to see how to operate.

1. Turn on advanced filtering

After opening Excel, select the entire range containing the data, and then in the "Data" tab, click the "Advanced" button in the "Sort & Filter" group.

2.Set filter conditions

In the pop-up dialog box, select "Copy filtered results to other locations", then set the target range and the save location of the filtered data (i.e. "Copy to"), and finally remember to check "Select non-duplicate records".

3. Apply filters

After the settings are completed, click "OK" and Excel will copy the unique records to the specified location.

Protect your data:

After cleaning your data and removing duplicates, the next step is to protect this data from unauthorized modifications. We can achieve this by setting a "restriction password".

First, select the worksheet that needs to be protected, then click the "Review" tab in the menu bar, and then click the "Protect Worksheet" button.

In the pop-up dialog box, enter the protection password and select the operations you want to allow users to perform, such as selecting cells, formatting cells, etc. After the settings are completed, click "OK". The Excel table will be locked and cannot be edited or modified.

If you need to modify the table again later, you only need to click the "Review" tab in the menu bar, and then click the "Unprotect Worksheet" button. When the dialog box pops up, enter the password originally set, and the "restricted password" of Excel will be lifted. .

Notes:

1. Back up data

Before deleting duplicates, it is recommended to back up the data to prevent accidental deletion of important information.

2. About password

After setting a password for the Excel table, remember to remember it or save it, because if you forget the password, you cannot lift the restrictions in Excel.

In this case, you need to use other tools to lift Excel restrictions.

For example, Pepsi Niu Excel password recovery tool can directly remove the "restriction password" of Excel without using a password. You only need to select the "Unrestriction" module in the tool, and then import the protected Excel to remove it with one click.

That’s it for today’s sharing, I hope these tips will be helpful to everyone!

The above is the detailed content of How to delete duplicate data in batches in Excel? Two methods are recommended!. For more information, please follow other related articles on the PHP Chinese website!