Removing Duplicates in Excel

When we work with Excel, there’s often a situation where we have duplicate data in our worksheet. We can find and manipulate this data in many ways, there are also a few ways to delete them.

Find Duplicate Values

We will use the list of NBA players and their statistics from a couple of different games in our example.

In column A, all of the values are doubled, so all of them would be duplicated if we would decide to duplicate them.

For that reason, we will use this option for the Points column (column B).

To easily find and highlight our duplicate values, we will select range B2:B20 and then go to Home tab >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Duplicate Values.

In the pop-up window that appears, we will just click OK.

We will now have our duplicated values highlighted:

How to Remove Duplicate Values

To remove our duplicated values, we do not need to highlight them, but this can certainly be very helpful to see what information will be deleted, and the ones that will be kept.

We will select our table, then go to Data tab >> Data Tools >> Remove Duplicates:

Once we click on it, we will be presented with the pop-up window from which we choose in which column are our duplicated values found that we want to delete.

Since we want to delete duplicated values in column Points, we will only leave this column marked.

Then we click OK and we will receive the following message:

Our table now looks slightly different:

We do not have any duplicated values in the B column. We need to keep in mind that Excel deletes the entire rows where duplicated values are found and that it keeps only those rows that were first in order, while it removes all the other ones.

You should copy and save your original table in another sheet, just to be sure not to lose original data, in case something goes wrong.

Excel 365 Update

  • Improved Data Validation Tools: Excel 365 offers more robust data validation features. You can set stricter criteria to prevent duplicate entries during data input, reducing the need for manual removal later.
  • Conditional Formatting for Highlighting Duplicates: Enhanced conditional formatting options allow for easier identification of duplicate data points. By visually highlighting duplicates, you can efficiently target and remove them.

Excel 365 focuses on improving data quality through better prevention methods and identification tools, the core functionality for direct duplicate removal hasn’t significantly changed.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.