Excel - Remove Duplicates Values

The Remove Duplicates feature in Microsoft Excel allows you to remove repeated values more quickly than searching and deleting them manually. The Power Query Editor is a prominent tool for dealing with complex datasets. Data cleaning can be done seamlessly through the Power Query Editor tool by removing duplicates, errors, and null values.

Various ways to remove duplicates in Microsoft Excel are given below −

• Use the Power Query Editor tool to eliminate duplicate values from the dataset.
• Utilize Conditional Formatting to highlight the duplicate values and eliminate them
• Use the Remove Duplicate button in the Data tab.
• The complex VBA code can be written to remove duplicates in Excel.
• Use the Advanced Filter dialog box, which permits you to eliminate duplicate values in the Excel column.

Remove Duplicates Using Power Query Editor

Step 1 − Consider the sample datasets, which consist of four columns named Category, Incident ID, Issue, and Assignment Group. Go to the Data tab and select the "From Table/Range" button under the "Get & Transform Data" group.

Step 2 − After that, another dialog box, "Create Table," will appear. To create a table, specify the range of the cells "A1:D18," then click the OK button.

Step 3 − Moreover, the "Power Query Editor" dialog box will open. Select the Category column and then go to the "Home" tab, expand the "Remove Rows" tile, and select "Remove Duplicates" from the list under the "Reduce Rows" group.

Therefore, duplicate values have been removed based on the category column in a table. Using the ctrl keyword, you can also choose multiple columns to remove duplicate values.

Remove Duplicates in Excel Based on One Column

Step 1 − Assume the sample datasets where the names of doctors are listed in the B column. However, doctor names are repeated in the defined list. The task is to remove the duplicates and get the unique doctors' names from the given list.

Step 2 − First, navigate the Data tab and select the "Advanced" under the "Sort & Filter" group.

Step 3 − After that, another dialog box named "Advanced Filter" will open. In the Action category, select "Filter the list, in-place." Then, specify the range of cells "$B$2:$B$14" adjacent to the List range. Tick the "Unique records only" checkbox. Press the OK button.

Note: You can also choose another option, "Copy to another location," where unique names will be copied from the listed range to that location. You must specify the new location(cell reference) in the "Copy to:" section.

Step 4 − Therefore, unique doctors' names are displayed in the B column, and duplicate names are removed successfully.

Remove Duplicates in Excel Shortcut

The Excels "Remove Duplicate" built-in button is available in the Data tab to eliminate the duplicate values from the selected column.

The keyboard shortcut for the "Remove Duplicate" inbuilt feature is "Alt+A+M." Pressing this shortcut, the "Remove Duplicates" dialog box will open, where you can find one or more columns containing repeated values. Select the "City" column and press the OK button.

After that, a message will populate the Microsoft Excel window, telling you that 11 duplicate values have been identified and six unique values have been found. Hit the OK button.

Hence, the City column contains unique values.