Do you have duplicate data in your spreadsheet and don't know how to remove it? Well, look no further! Here, we will teach you to how to remove duplicates in excel in two quick and easy ways. Read on to find out what they are.
As we work through cells, some rows are often duplicated on the spreadsheet.
How to remove duplicates in Excel?
Sometimes working with Excel can be difficult, especially if you have duplicate data and you don't know how to modify it. But with the formula that we will give you to eliminate this information, you will be able to better manage your spreadsheet from now on.
If you are beginners in the use of this tool, you should know that in Excel there can be cases of duplication of the rows when you are filling the cells with information. What many frequently asked questions about:
How to know if excel table contains duplicate values? Why is this happening? What is the fastest solution? Is there a way to prevent it? That is why, in this article we will tell you how to remove duplicates in excel, with an essential formula for this type of case. Next, we invite you to follow the following steps.
- Use conditional formatting, to find and highlight the duplicate data. Here you will be shown what these files are and you can easily delete them.
- Select the cell you want to check, whether or not it is duplicated.
- Click on the tab "Start" and then select the option "Highlight cell rules ”/"Duplicate values".
- Followed by this, a small box will appear on the screen and next to the entry "Values". There, you must choose the color you want to use, so that the duplicates are highlighted and finally you give "To accept".
It is done! With this you can see if you have duplicate data on your artboard, which will be highlighted the way you set it up. And, now that you know what they are, you can eliminate it in two ways.
One, applies to cases of duplicates or values in Excel, which are identical or whose entries match. Which is very easy to detect and quick to eliminate. On the other hand, there is the method of eliminating duplicates that are found in individual values that coincide with each other. Which are considered as old and unnecessary information.
Remove duplicates in Excel when they are identical or entries match
Access the Excel sheet that you want to modify and locate yourself in the rows that contain the same information -for each column-. Once you know what they are, all you have to do is delete all those entries that have the same value, leaving only one.
For this, you select all the cells to delete. Then, you go to the tab "Data" and select the option of "Data tools"/ "Remove duplicates". Then, a small box should be displayed on the screen, where you will click on the option "Select everything" and then to "To accept".
From there, the system will go to remove identical duplicates and matching entries. At the end of the debugging process, Excel will show you a warning confirming that everything is ready. On the other hand, and for further confirmation, you can go to the Excel table to verify that there is no duplicate data.
An important fact that you should know about this elimination method is that, before executing the command "Remove duplicates ". It is necessary that you select at least one of the cells of the data range, so that the Excel system can detect them and show the possible columns that we can visualize as duplicates.
Now, if you don't want Excel to do the column detection in an automated way, you will have to manually select the range of cells for such action to take place.
When there are loose values that match
As we have mentioned above, there is a second method of deduplication in Excel. And like the previous one, it is easy to operate. To debug these types of duplicates, you must follow the following steps that we will indicate below:
- Select the cell where the duplicate information appears.
- Enter the menu "Data"/"Data tools" / "Remove duplicates".
- A box will appear where you will uncheck and / or deactivate the boxes corresponding to the data that is duplicated. When finished, select the "OK" option.
- Wait for the removal process to complete. And, like the previous procedure, you will get a warning from the system indicating that the elimination of duplicates is done.
If you have been wanting to know more about the tools used in this program, we invite you to read about how to make labels in excel. And finally, we share the following video where everything about eliminating duplicates will be explained in detail.