Often times, while dealing with data we get duplicate values. These duplicate values can lead to double counting in data and hence give wrong results. This makes it important to remove the duplicates to get the desirable results.

To remove duplicates, select the Array where you want to remove duplicates and then press ALT + A + M

For further steps, completely read the article till the end.

Basic Rule : Suppose there is an array which has duplicate entries in Rows 1,2, and 5. While removing duplicates, excel will just keep the first instance of the duplicate, that is Row 1.

Removing Rows with completely duplicate entries

Consider the example below, where Row 2 and 3 are exactly identical i.e. Name, City and Age, all are similar in these two Rows.

If you wish to remove the rows which are completely duplicate i.e. they have the same entries under all columns (Name, City, Age) then you can simply press ALT + A + M and tick mark all the columns of the dialogue box that appears, as shown below

After you click OK, the Excel will keep only the Row with first instance of the entry and remove all the other Rows, which are completely duplicate to it, as shown below.

Removing Rows with duplicate entries under certain columns

Consider the example below, where Name and Age in Row 3 and 5 are identical. However, the City column in these two Rows are different.

If you wish to remove the rows in which certain values are duplicate i.e. they have the same entries under specific columns, then you can simply tick mark those columns in the dialogue box which you want to consider for removing such duplicate rows .

In this example, we want to consider only duplicate entries under ‘Name’ and ‘Age’, as shown below.

After you click OK, the Excel will keep only the Row with first instance of the duplicate entries and remove all the other Rows, as shown below.

We hope that you found this article informative.

Popular Course in this category
Microsoft Excel for Finance Registered Microsoft Partner | Specialized for Core Finance | 20+ hours Videos

Write A Comment