How to find duplicates in Excel

Once you’re working with a spreadsheet, duplicate knowledge can generally make its means in and trigger all types of issues. On this how-to, we display two strategies of using Microsoft Excel for figuring out and eradicating duplicate knowledge.

Figuring out duplicate knowledge utilizing conditional formatting

One of many best methods to find duplicate knowledge in Excel is to make use of conditional formatting. This is how.

Step 1: Choose the portion of your knowledge the place you wish to find duplicates. In our instance, we’re choosing our total desk. Notice that conditional formatting will not work in case you are using pivot tables in Microsoft Excel.

Step 2: Choose Conditional Formatting from the Residence menu, then select Spotlight Cell Guidelines > Duplicate Values…

Step 3: You will be offered with a dialog that lets you choose whether or not to point out duplicate or distinctive values (we would like the previous, in fact) and the way you need the information highlighted. You may select from the drop-down checklist or create a customized format.

Step 4: As soon as you have chosen an possibility or clicked OK, your duplicate knowledge will probably be highlighted. This may be textual or numeric knowledge, and it may possibly embrace any variety of columns.

Utilizing the Take away Duplicates operate

You too can elect to have Excel take away any duplicate knowledge out of your spreadsheet. Use this operate with warning – it is most likely a good suggestion to again up your spreadsheet first.

Step 1: First, choose the columns the place you wish to take away duplicates. Right here, we’re choosing your complete desk. Notice that Take away Duplicates will solely take away these objects the place the values are equivalent throughout rows or from particular person cells if a single column is chosen.

In our instance, the final three rows include duplicate knowledge.

Step 2: Choose the Information menu, then click on Take away Duplicates. Within the dialog field that pops up, you will be offered with a number of choices. First, you possibly can determine in case your knowledge has headers. Second, you possibly can select which columns to incorporate or exclude.

Once you’re prepared, click on OK.

Step 3: You will be offered with a dialog that may point out what number of duplicate values have been discovered and eliminated and what number of distinctive values stay. Notice that the final three columns in our knowledge have been eliminated.

Notice that it is usually attainable to use VLOOKUP in Excel to search out duplicate knowledge.

Attending to grips with these superior options turns what seems to be a plain grid of numbers into a robust instrument. Need extra tips? This is how to use the concatenate function in Excel.