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:
- Select the range where you want to remove duplicates. If your data has headers, include them in the selection.
- Press Alt + A to navigate to the Data tab.
- 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”
Once the ‘Remove Duplicates’ dialog box is open:
- If your data includes headers, ensure the “My data has headers” checkbox is checked. You can toggle this option by pressing Alt + H.
- 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”
Enterto remove duplicates based on your selection.
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:
- Select the range from which you want to remove duplicates. Include the headers if applicable.
- Go to the Data tab on the Ribbon.
- Click on Remove Duplicates in the Data Tools group
- 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:
- Excel Keyboard Shortcuts
- Shortcut to Duplicate Sheet in Excel
- Shortcut to Copy Cells Above in Excel
- Show Formulas Instead of Values in Excel (Shortcut)
- Apply Remove Filter in Excel (Shortcut)
- Shortcut to Insert Drop Down in Excel
Other Excel articles you may also like: