If you use Excel constantly it is possible that as you fill the cells with information, some rows will be duplicated. And if you don’t have it downloaded yet, we recommend installing Microsoft Office. And if you have duplicate cells, you may wonder how can you know if this happens? And how can you eliminate them? In this article we will show you how to do it.
How to tell if excel table contains duplicate values?
If you come across various duplicate data in your Excel documents, it will surely become a problem when it comes to understanding certain data. With this in mind, it is normal that you want to know if there is any column with duplicate information in your Excel documents. And to know how to find data in two or more Excel sheets it can help you
- Using conditional formatting to find and highlight duplicate data will show you what these files are and thus easily delete them. We will show you how you can locate them in a simple way
- Just select with your mouse which cell you want to check to verify that it is not duplicated.
- Now you must click on Start then go to the option Highlight cell rules and then select Duplicate values.
- You will be able to see a small box that will appear on the screen and next to the entry Values you must choose the color you want to use so that the duplicates are highlighted and click OK.
That’s it, you will be able to see if you have duplicate data highlighted the way you set it up. Now that you know what they are, let’s see how you can eliminate them.
How to remove duplicate data in Excel?
If you are one of the people who are used to using Excel, it is possible that on more than one occasion the documents in the tables have been duplicated. There are at least two types of duplicates and two ways to remove them and we will show you what they are.
The truth is that deleting duplicate data from Excel is very simple, since the same program tells you what this duplicate data is without you having deleted it. However, if you want to be more secure then you can be cautious and remove them yourself. In addition, Excel has other functionalities that will be very interesting such as counting or adding the number of cells with color. As we mentioned before, there are two ways to define a duplicate.
1 Way: When all duplicates or values in Excel are identical or their entries match
It is logical that if you are going to use the function to eliminate duplicates it is because you have identical values in the Excel tables. Eliminating them is very simple, you simply have to access your Excel sheet and locate yourself in the rows that say exactly the same in each column. In this case you can compare two lists in Excel and highlight repeated.
Once you have them located, you simply have to delete all the entries that contain exactly the same value except for one.
Now, for Excel to eliminate all these duplicates you must select the cell you want with your mouse. Now you must go to the menu called Data and select the Data Tools option and click on Remove duplicates.
When doing so, a small box should appear on the screen in which you will click on the Select all option and then select OK. That’s when Excel will remove these exactly the same duplicates and when it does you will see a notice telling you that it is done.
You can go to your Excel table and verify that all duplicates have been eliminated except one that is the main entry. However, as we mentioned earlier, it is not the only way to eliminate duplicates from your Excel table, there is another variant of these duplicates that we will show you below.
2 Way: When any duplicate of Excel contains individual values that match
It is normal that we look at the values that are exactly the same called duplicates and we would like to eliminate them. However, there is another type of input that can be considered as duplicate, it is when the columns coincide only in individual values. Those that contain old information and therefore not necessary can be considered as such.
To eliminate this type of duplicates you simply have to follow the steps that we gave you previously with a slight variant, that is how easy it is.
You just have to select the cell where the duplicate information appears with your mouse. Then go to the menu called Data and then the Data Tools option where you will click on Remove duplicates.
When the box appears, you simply have to make a small variant that differs from the previous case. You must uncheck or deactivate the boxes that correspond to the columns considered duplicates. You must accept the action and it will show you the notice telling you that the process is done.