Shortcut to Remove Duplicates in Excel

While working with large datasets in Excel, it is common to come across data that has duplicate entries.

I used to really struggle with duplicate data records if I needed to count the unique records before I started using the Remove Duplicate feature in Excel.

This feature is useful for getting rid of duplicate records and keeping only the unique data entries when you’re cleaning up a lot of information.

In this article, we will discuss how to remove duplicates in Excel using a simple shortcut.

Keyboard Shortcut to Remove Duplicates in Excel

Here is the keyboard shortcut to open the Remove Duplicates dialog box in Excel:

Alt + A + M

The above shortcut opens the Remove Duplicates dialog box only. You will still have to select the columns from which you want to remove the duplicates and then click OK.

How to Use the Keyboard Shortcut to Remove Duplicates in Excel

Here are the steps to use the keyboard shortcut mentioned above:

  1. Select the range where you want to remove duplicates. If your data has headers, include them in the selection.
  2. Press Alt + A to navigate to the Data tab.
  3. Press M to select the ‘Remove Duplicates’ option. If there are multiple columns in your dataset, you will receive a prompt to either “Expand the selection” or “Continue with the current selection”
Remove duplicates warning dialog box

Once the ‘Remove Duplicates’ dialog box is open:

  1. If your data includes headers, ensure the “My data has headers” checkbox is checked. You can toggle this option by pressing Alt + H.
  2. Use the Tab key to navigate through the list of columns. Use the Spacebar to select or deselect specific columns for duplicate criteria. In our case, I have selected only the column for “Store”
Select columns in the remove duplicate dialog box
  1. Press Enter to remove duplicates based on your selection.
Keyboard Shortcut to Remove Duplicates in Excel

Excel removes duplicate rows based on the columns we will select. It keeps the first instance of a duplicate record and removes subsequent instances. This means the order of the data can affect which duplicates are removed.

Alternate Way to Remove Duplicates in Excel

For those who prefer using the mouse or want an alternate method:

  1. Select the range from which you want to remove duplicates. Include the headers if applicable.
  2. Go to the Data tab on the Ribbon.
  3. Click on Remove Duplicates in the Data Tools group
Click on the remove duplicates dialog box
  1. In the ‘Remove Duplicates’ dialog box:
    • Check or uncheck columns as needed. If your data has headers, make sure the ‘My data has headers’ option is checked.
    • Click OK to remove the duplicates.

Some Useful Things to Know About Remove Duplicates in Excel:

  • Backup the Data: I always make a copy of my data before removing duplicates. This way, if I accidentally remove something important, it allows me to retrieve it.
  • Selection: Ensure the correct range is selected before removing duplicates. If you select a single cell in a range with consistent formatting, Excel might automatically extend the selection to the entire data set.
  • Headers: Properly identify if your data has headers. Incorrectly indicating the data headers can lead to the unintended removal of the first row of data.
  • Formulas and References: Be aware that removing duplicates can affect formulas or cell references in your workbook. If other parts of your workbook rely on the data you’re deduplicating, those parts may be affected.

Remember, ‘Remove Duplicates’ is a powerful tool, but with great power comes great responsibility.

Always double-check your selections and understand the implications of the changes you’re making to your data.

Other Excel shortcuts you may also like:

Other Excel articles you may also like: