HOW TO REMOVE DUPLICATES IN EXCEL?

INTRODUCTION

Many times in our reports we are stuck will lengthy columns containing hundreds or thousands of rows. Sometimes these columns contain repeated rows, which are extremely hard to find manually, because if we do so, it is going to be very hectic and time consuming.

But thanks to Excel which provides us many options to remove such repeated data easily.

In this article we will discuss one such method to remove the duplicated rows from the given columns.

EXCEL provides us a direct solution for the problem known as REMOVE DUPLICATES.

This option facilitates to instantly remove the duplicate values from any column. The option removes all the duplicated rows and create a new column with unique values only.

There are several options which EXCEL asks before we execute the option. Let us find out the way to use REMOVE DUPLICATE option in Excel.

The button for REMOVING DUPLICATES can be found under the DATA TAB >DATA TOOLS as shown in the picture below.

REMOVE DUPLICATED BUTTON LOCATION

The keyboard shortcut for REMOVING DUPLICATES is

ALT+A+M

STEPS TO USE KEYBOARD SHORTCUT

  • SELECT THE RANGE IN WHICH WE NEED TO FIND OUT DUPLICATES.
  • PRESS ALT AND KEEP IT PRESSED.
  • IN A SEQUENCE PRESS A AND AFTER THAT M.

STEPS:

  • Select the column or a range, from which you want to remove the duplicate values.
  • Click the button, remove duplicate or click ALT+A+M.
  • A dialog box as shown in picture will open.
REMOVE DUPLICATED DIALOG BOX
  • Select the columns for the checking of duplicate values and click OK.

OUTPUT

INFORMATION SHOWING THE NUMBER OF REMOVALS

The duplicate values from each column has been removed as desired. The data is left without any repeated value.