‘To err is human!’ So, be it MS Excel or any other productivity tool, when the information is to be fed by us, then there is always a very big room for error. These errors at times can leave us scratching our heads puzzled and confused about what went wrong.
Also read: How to Switch Rows & Columns in Excel?
Sometimes they might even go unnoticed! This causes even more trouble because we might be confident in the results that were computed whilst in reality it ain’t. We shall uncover one such error in this article. It is one of the most dreadful errors that could affect data analysis & make people lose much of their precious time; It is the duplication of information.
In this article, we would be looking into the ways in which duplicate data across the rows can be spotted & removed in MS Excel. Following is the dataset which we shall use to demonstrate the process throughout this article.
Selecting the Data:
A closer observation of the above tabulation would help one to identify that rows 10 & 15 have duplication of data as indicated below.
Now that we know where these rows are we might as well select these entries & delete them. But, in this case, we have only 2 duplicate rows owing to choosing a simple example for demonstrating the technique.
But in real-time, there might be umpteen duplicate rows & it won’t be an effective way if one has to delete each duplicate row one after the other.
So, we shall select the entire tabulation & turn our focus on the arsenal of tools that MS Excel has got to offer.
Using Remove Duplicates Option:
Now one shall hover the cursor over the Tabs section and click on the one which says Data.
Once venturing into the Data tab, there might be several options in the offering to fiddle with data, but the one which we are after right now is the Remove Duplicates option as indicated below.
Click on the option & the Remove Duplicates pop-up shall appear.
One can select the column header under which the duplicate values are to be analysed thereby restricting the search only to the columns which are not to be having any duplicates rather than the including the columns which would be having duplicates to serve a specific purpose.
Also, there might be data without any header row. They are also to be specified when the selected data is to be analysed for duplicates.
The header row checkbox is ticked by default. So, if the selected data does not have any, then it is the user who needs to uncheck this checkbox.
In this example, the header row checkbox is to be ticked since it has a header and all columns are also to be selected since the check for duplicates is to be carried out throughout. Click OK.
One shall bear witness to the immediate deletion of the duplicate rows within the selected data. Also, there will be a pop-up message stating the count of duplicate values that were found and removed along with the count of the unique values that remain within the selected data.
Conclusion:
Now that we are concluding this article, hope it has given you the much-needed clarity on deleting duplicate rows in MS Excel. Here’s another article which elaborates on how to delete multiple rows in MS Excel. There are also a number of other cool articles in QuickExcel that will help you catch up with ever-evolving trends. Cheers!