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.