#Microsoft excel find duplicates how to#

You can also display the unique values in the second column. Note: take a look at the second picture on this page to see that we swapped the last 2 arguments of the IF function.Ģ. Display the unique values in the first column (these values do not occur in the second column). Unique Values in Each Columnĭo you want to compare two columns by displaying the unique values in each column? Simply swap the last 2 arguments of the IF function.ġ. At step 2, we match each value in the second column with the range in the first column. Want to master Microsoft Excel and take your work-from-home.

#Microsoft excel find duplicates how to#

Note: at step 1, we matched each value in the first column with the range in the second column. In this MS Excel tutorial from ExcelIsFun, the 577th installment in their series of digital spreadsheet magic tricks, youll learn how to use SUMPRODUCT and the join symbol (&/ampersand) to group duplicates and then see how to use advanced filtering to extract a list of unique records. You can also display the duplicates in the second column. As a result, the ISERROR function returns TRUE and the IF function returns an empty string.Ģ. The MATCH function in cell C4 returns a #N/A error (no letter D in the range B1:B7).

Expand the Conditional Formatting menu and go. Click the Home tab and select Conditional Formatting. Click Highlight Cells Rules, Duplicate Values. On the Home tab, in the Styles group, click Conditional Formatting. As a result, the ISERROR function returns FALSE and the IF function returns the value in cell A1. Details: However please note that as you have mentioned ADDRESSES, The system would consider an entry as duplicate only. In this tutorial you will learn various method of finding and removing duplicate values in Microsoft ExcelTo find duplicates in the selected rangeSub FindDu. To find duplicate entries from a single column, use the conditional formatting feature in Excel. To find and highlight duplicate values in Excel, execute the following steps.

Display the duplicates in the first column (these values also occur in the second column).Įxplanation: the MATCH function in cell C1 returns the number 5 (letter A found at position 5 in the range B1:B7). STEP 3: You can select the formatting that you want. Let's start by comparing two columns and displaying the duplicates.ġ. STEP 1: Select your list of words / data: STEP 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.