[Excel Shortcut] Remove Duplicates (Windows & Mac)

Written by Kasper Langmann

Mastering Excel is a valuable skill in today’s data-driven world. One of the most useful features of Excel is the ability to remove duplicate entries, which can help streamline your data and make your analyses more accurate. In this guide, we will explore how to use shortcuts to remove duplicates in Excel on both Windows and Mac systems.

Understanding Duplicates in Excel

Duplicates in Excel are essentially repeated entries in a dataset. They can occur due to various reasons such as data entry errors, merging of datasets, or simply because the data contains recurring information. While duplicates can be useful in some scenarios, they often need to be removed to avoid skewing data analysis and interpretation.

Excel provides several methods to identify and remove duplicates. However, using shortcuts can make this process more efficient, especially when dealing with large datasets. Before we delve into the shortcuts, it’s important to understand how Excel identifies duplicates.

How Excel Identifies Duplicates

Excel identifies duplicates based on the entire row of data. If all the entries in a row are identical to another row, Excel considers it a duplicate. However, you can also specify certain columns to check for duplicates if you don’t want to consider the entire row.

It’s important to note that Excel’s duplicate identification is case-insensitive. This means that ‘APPLE’ and ‘apple’ would be considered duplicates. Additionally, Excel doesn’t consider formatting when identifying duplicates. So, even if two entries have different formatting, they would still be considered duplicates if their content is identical.

Removing Duplicates in Excel

Now that we understand what duplicates are and how Excel identifies them, let’s look at how to remove them. Excel provides a built-in feature to remove duplicates, but knowing the shortcut can save you a lot of time, especially when working with large datasets.

Here are the steps to remove duplicates in Excel using shortcuts:

  1. Select the range of cells or the entire column that you want to check for duplicates.
  2. Press Alt + A + M on Windows or Control + Shift + G and then M on Mac. This will open the ‘Remove Duplicates’ dialog box.
  3. In the dialog box, select the columns you want to check for duplicates. If you want to check the entire row, select all columns.
  4. Click ‘OK’. Excel will remove the duplicates and show a message indicating how many duplicates were removed and how many unique values remain.

Removing Duplicates in Multiple Columns

If you want to remove duplicates based on multiple columns, you can do so by selecting the appropriate columns in the ‘Remove Duplicates’ dialog box. Here’s how:

  1. Select the range of cells or the entire columns that you want to check for duplicates.
  2. Press Alt + A + M on Windows or Control + Shift + G and then M on Mac to open the ‘Remove Duplicates’ dialog box.
  3. In the dialog box, select the columns you want to check for duplicates.
  4. Click ‘OK’. Excel will remove the duplicates based on the selected columns and show a message indicating how many duplicates were removed and how many unique values remain.

Additional Tips and Tricks

While the above shortcuts can help you quickly remove duplicates, there are a few additional tips and tricks that can make your work even easier.

Highlighting Duplicates

Before removing duplicates, you might want to highlight them to ensure you’re not removing any important data. To do this, you can use the ‘Conditional Formatting’ feature in Excel. Here’s how:

  1. Select the range of cells or the entire column that you want to check for duplicates.
  2. Press Alt + H + L + N on Windows or Option + Command + L on Mac to open the ‘New Formatting Rule’ dialog box.
  3. Select ‘Format cells that contain’.
  4. Under ‘Format only cells with’, select ‘duplicate’.
  5. Choose the formatting you want to apply to the duplicates and click ‘OK’.

This will highlight all the duplicates in the selected range, making it easier for you to review them before removal.

Using Formulas to Identify Duplicates

You can also use formulas to identify duplicates in Excel. The COUNTIF function can be particularly useful for this. Here’s how you can use it:

  1. Select a cell next to the range you want to check for duplicates.
  2. Type =COUNTIF(range, cell) where ‘range’ is the range of cells you want to check for duplicates and ‘cell’ is the cell you’re checking.
  3. Press Enter. If the result is more than 1, the cell is a duplicate.

This can be a powerful tool when dealing with large datasets as it allows you to quickly identify duplicates without having to manually check each cell.

Conclusion

Removing duplicates is a common task when working with Excel. Knowing the shortcuts to quickly and efficiently remove duplicates can save you a lot of time and effort. Whether you’re working on Windows or Mac, these shortcuts and tips can help you master the art of handling duplicates in Excel.

Remember, while removing duplicates is important for data analysis, always ensure you’re not removing any important data. Always backup your data before removing duplicates and use the highlighting and formula features to double-check your data.