This results in a table with unique data as shown below Step 6: Click the checkbox Unique records only. Step 5: Click the cell in the worksheet where the new(unique) data table needs to be created. Step 4: Select the Copy to another location option Step 3: Under the Sort & Filter Group, select the Advanced option. Using Excel’s Advanced Filter, we want to remove the duplicate values.

The table below shows the brand name of four-wheelers, as well as their model name and colour. Using Advanced Filter to Remove Duplicates in Excel Step 3: Drag the formula in the remaining cells to get the number of duplicates as shown belowĥ. Step 2: Press Enter key to get the total number of duplicates ( 3) for the brand Ford.

A6 – It indicates the first value in the range A6:A16.

A6:A16 – It is the range across which we want to find the duplicate data.

Step1: Place the cursor in cell D6 and enter the formula, We want to find the number of duplicate values using the COUNTIF function in Excel. The table below shows the Brand names of four-wheelers with the Model name and colors. Consider the following example to understand how it works We can find the number of duplicate values in a given data set using the COUNTIF function in Excel. Using the COUNTIF function to find the Number of Duplicates Step 4: Select the column headings (customer Name) by which the duplicate value needs to be searchedĪ Microsoft Excel pop-up appears indicating the number of duplicate values found and removed And the number of unique values that remain.Īll the duplicate values are removed and the table now consists of unique values.Ĥ.

Step 3: Under the Data Tools Tab, click the Remove Duplicates icon. Using the Remove Duplicates feature, we want to remove duplicate entries from the list. The table below displays a list of customers along with their Total Bill and the number of items purchased. Using the Remove Duplicates feature in Excel Shortcut: To apply a filter, select the desired table range and press the keys CTRL + SHIFT + L together. TEXT and String Functions in Excel (26+).Lookup and Reference Functions in Excel (36+).Excel Conditional Formatting Based on Another Cell Value.SUMPRODUCT Function with Multiple Criteria.Compare Two Columns in Excel for Matches.

How to merge duplicate rows in excel