How to easily remove or delete duplicate data in Excel with formula

Who I am
Marie-Ange Demory

Author and references

If you use constantly Excel , it is possible that as you fill the cells with information, some rows are duplicated. And if you haven't downloaded it yet, we recommend installing Microsoft Office. And if you have duplicate cells, you might be wondering, how can you tell if that happens? And how to remove them? In this article we will show you how to do it.

      How to know if Excel table contains duplicate values?

      If you come across various duplicate data in your Excel documents, they will surely become a problem when it comes to understanding certain data. Taking this into consideration, it is common that you want to know if there is a column with duplicate information in your Excel documents. And knowing how to search data in two or more Excel sheets can help you

      • Using conditional formatting to find and highlight duplicate data will show you what these files are so you can easily remove them. We'll show you how to spot them easily
      • Just select with the mouse which cell you want to check to verify that it is not duplicated.
      • Now you have to click on Home and then go to the Highlight Cell Rules option and then select Duplicate Values.
      • You will be able to see a small box that will appear on the screen and next to the Values ​​item you must choose the color you want to use so that the duplicates are highlighted and click OK.

      That's all, you will be able to see if you have duplicate data highlighted in the way you configured. Now that you know what they are, let's see how to remove 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 the documents in the tables have been duplicated on more than one occasion. There are at least two types of duplicate 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 deleting it. However, if you want to be more secure then you can be cautious and delete them yourself. In addition, Excel has other features 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 stands to reason that if you're going to use the function of elimination of duplicates it's because you have identical values ​​in Excel tables. Deleting them is very easy, you simply have to access your Excel sheet and position yourself in the rows that say exactly the same thing in each column. In this case you can compare two lists in Excel and highlight repeated.

      Once you locate them, you simply delete all but one entry that contains exactly the same value.

      Now, for Excel to remove all these duplicates, you need to select the cell you want with your mouse. You should now go to the menu called Data and select the option Instruments data and click Remove Duplicates.

      When you do this, a small box should appear on the screen where you click on the Select All option and then select OK. This is when Excel will remove these exact same duplicates and when it does you'll see a warning telling you it's done.

      You will be able to go to your Excel table and check that all duplicates have been removed except one which is the main voice. However, as mentioned above, it is not the only way to remove duplicates from Excel table, there is another variation of these duplicates which we will show you below.

      2 Form: When some Excel duplicates contain free matching values

      It is normal that we observe values ​​that are exactly the same called duplicates and we would like to delete them. However, there is another type of input that can be considered a duplicate, which is when columns only match on loose values. Those that contain old information and are therefore not needed can be considered as such.

      To delete this type of duplicates, you simply need to follow the steps we have given you above with a slight variation, it's that easy.

      You simply select the cell where the duplicate information is displayed with the mouse. Then go to the menu named 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 change that differs from the previous case. You need to uncheck or disable the boxes that correspond to columns that are considered duplicates. You have to accept the action and it will show you the warning telling you that the process is finished.

      add a comment of How to easily remove or delete duplicate data in Excel with formula
      Comment sent successfully! We will review it in the next few hours.