How to find duplicates in Excel

If you’re working with a spreadsheet, duplicate knowledge can generally make its approach in and trigger all kinds 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. Here 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. Be aware 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 may be offered with a dialog that lets you choose whether or not to point out duplicate or distinctive values (we would like the previous, after all) and the way you need the information highlighted. You’ll be able to select from the drop-down checklist or create a customized format.

Step 4: As soon as you’ve got chosen an choice or clicked OK, your duplicate knowledge will likely be highlighted. This may be textual or numeric knowledge, and it might embrace any variety of columns.

Utilizing the Take away Duplicates operate

You can too 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 the complete desk. Be aware that Take away Duplicates will solely take away these objects the place the values are an identical 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 Knowledge menu, then click on Take away Duplicates. Within the dialog field that pops up, you may be offered with a number of choices. First, you may determine in case your knowledge has headers. Second, you may select which columns to incorporate or exclude.

If you’re prepared, click on OK.

Step 3: You may 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. Be aware that the final three columns in our knowledge have been eliminated.

Be aware that it’s also doable to use VLOOKUP in Excel to seek out duplicate knowledge.

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