How to find and mark duplicate data using Excel

Everyone will encounter various problems when using excel. Some friends come to this site to ask the editor how to filter duplicate content in excel. In order to help everyone, the editor has collected relevant information online. , I finally found a method on how to filter and mark duplicate data in an excel table. The details are in the article below. If you need it, please come to this site to have a look.

How to filter duplicate items in excel?

Method 1,

1. This method can help us highlight and display duplicate items Color, that is, use conditional formatting to filter. Select the column that needs to be filtered, click [Conditional Formatting]-[Highlight Cell Rules]-[Duplicate Values];

2. Select the condition as duplicate and select the duplicate item Display style, then you can show which data in the column is repeated.

Method 2,

1. Use the COUNTIF function to call out duplicate items. After using the COUNTIF function, you can directly display the total number of current data in the column. Appeared several times. First we need to insert a blank column after the search column to display the number, and then enter =countif(A$2:A$22,A2);

# in the cell after the first data

##2. After getting the first data, pull down the cell and apply the formula to the following cells to display the number of occurrences of all data.

Method 3,

1. In addition to COUNTIF, there is also the IF function that can also help us filter duplicates. Let’s learn about it together. Similarly, You need to reserve a blank column, enter the function =IF(COUNTIF(A$2:A$22,A2)>1,"Repeat","") in the first cell. After pressing Enter, we can see the cell Duplicate words appear in the cell, which means that the data has duplicates in all the data;

2. Similarly, the first cell obtains the data , drop down the cell and apply the function to other cells to get whether there are duplicates in other data.

Method 4.

1. The last method is no longer a formula. It uses the advanced filtering function. Click [Data] in the toolbar. , click [Advanced] in the filtering and sorting function bar;

2. Then we select [Show filter results in the original area] and select the data to be filtered in the list area , check [Select non-duplicate records], and finally click OK.

The above is the detailed content of How to find and mark duplicate data using Excel. For more information, please follow other related articles on the PHP Chinese website!