How to find and remove duplicates Excel: tutorial

How to find and remove duplicates Excel: tutorial

How often do you find mistakes in your work? Whether you are an accountant, secretary, writer, or data entry expert, we all try hard to ensure there are minimal or no errors in our worksheets. Duplicates in Excel are one of the common blunders made when handling data and can bring a lot of confusion and troubles if not addressed. So, how do you remove duplicates Excel? What can you do to avoid such mistakes?

Remove duplicates Excel
Source: UGC

Handling large sets of data can be tedious and tiring, making someone vulnerable to mistakes. In this situation, it takes more time to recognize the blunders. Once you find and delete duplicates in Excel, you are sure of submitting a complete and accurate work.

How to find and remove duplicates Excel

The first thing to do is to identify the repeated data in your project. Do you know how to check for duplicates in Excel? There are several techniques you can apply to solve this in either single, multiple columns or the entire row of data.

Read also

Flash Fill Excel: Find out how to use it quite easily

How to find identical figures in a single column

Use the following procedure to identify repeated values in a single column:

  • First, select the entire part that you want to highlight. For keyboard shortcut, use Ctrl+Space Bar.
  • Next, proceed to the HOME tab of the Excel document.
  • Click the Conditional Formatting section and select Highlight Cell Rules.
  • Select the Duplicate Values section in the drop-down. A new window will open.
How to find duplicates in Excel
Source: UGC
  • Finally, you can now specify the format and colour. Click OK to highlight the cells with duplicates.

Once done, you can remove the identical figures but only after verifying the information from the source documents or person. In some cases involving many statistics, the repeated data may scatter and mix up with the other unique ones. To make work easier, all you need is to cluster them in one group.

Read also

How to add in Excel: Simple instructions to follow

READ ALSO: How to save a Word document as a PDF

How to filter the column with duplicates

The process involves applying a colour filter or a font to separate the highlighted data from the rest.

  • Go to the HOME tab and select the Filter section on the header.
  • Select Filter by colour in the drop-down.
Finding duplicates in Excel
Source: UGC
  • Proceed to choose a font or fill colour, probably the same colour used in the previous process.
  • The column will now display rows that only contain identical figures.
  • Alternatively, you can also sort the filtered information from the smallest to the largest and vice versa. It is simple and only takes a short time.
  • Highlight the figures and go to the Filter section.
  • Select Sort A to Z or ‘Sort from Smallest to Largest’ in the drop-down.
  • Figures with similar values will be sorted and grouped.

Read also

A step by step guideline of how spell check in Excel works

Highlighting duplicates in two or more columns

How do you discover identical figures in multiple columns? The process is the same as that of a single column:

  • First, select your content, then head HOME.
  • Proceed to Conditional Formatting followed by Highlight Cell Rules and Duplicate Values.
  • Choose the colour of your preference in the window log and click OK.
  • You will see all identical values in the multiple columns.

For extensive data, you can decide to filter and sort the identical figures to make it easier to analyze the information.

Finding duplicate rows in Excel

Another aspect is how one can locate repeated rows of data in Excel. The process is different from the previous one and a bit complex. We will use the information below to go through the procedure.

Finding duplicates in Excel
Source: UGC

Create another column E for merging the data in the other columns. Begin with highlighting the content in column E, and follow the same procedure as previous scenarios.

Read also

Top tips on how to add error bars in Excel in Windows and Mac

  • Go to the HOME section, then Conditional Formatting.
  • Proceed to Highlight Cell Rules and Duplicate Values.
  • Ensure you also select the preferred colour in the category of Values with before clicking OK.
  • It will highlight data in column E only.

To highlight all the identical rows, use the formula, A2&B2&C2&D2 to fill the rows in column E, as described above.

  • After filling all the rows in column E, select cells from A2 to D11.
  • Proceed to HOME then Conditional Formatting.
  • This time, select New Rule in the drop-down. Select the ‘Use a formula to determine which cells to format’ option under Select a Rule Type section.
How to check for duplicates in Excel
Source: UGC
  • Use the COUNTIF function =COUNTIF($E$2:$E$11,$E2)>1 to enter as the formula then go to format to choose a colour. Click OK.
  • It will highlight all the identical rows.

Read also

A useful guide on how to double space in Word

You now know how to find duplicates in Excel, but what should you do next? Time to learn how the techniques in Excel remove duplicates in a set of data.

Remove duplicates in Excel

Knowing how to get rid of duplicates in Excel will help clear some of the mistakes in your work. So, what is the formula for achieving this? Is there a shortcut? To achieve this, Excel has diverse methods depending on the number of rows and columns.

Single column

The procedure, in this case, is short and simple, but ensure you have a back-up copy of the original records because the process will alter the data.

  • Select your data and go to the DATA section on the header.
  • Click on Remove Duplicates option that will bring a log box will.
Removing duplicates in Excel
Source: UGC
  • Choose the ‘Select All’ option, tick on the column, and ‘My data has headers.’
  • Click OK to erase and remain with the unique data.

Read also

Incredibly easy steps to help you learn how to AutoFill Excel

Multiple columns

In this case, your project may have two or more columns, and the following process will guide you in removing duplicates in excel:

  • Go to DATA then Remove Duplicates option.
  • In the new window, select all the columns and data headers, then OK.
  • The process will successfully remove all identical rows. You can also choose any entry of your choice, depending on what you want.
How to get rid of duplicates in Excel
Source: UGC

Removing duplicate rows in Excel

Removing identical rows in Excel is another vital and exciting processes of cleaning your project.

  • As usual, select your content first.
  • Proceed to DATA section and then, Remove Duplicates to check on the headers and all columns.
  • Click the OK button to clean your data.
Remove duplicate rows in Excel
Source: UGC
  • You can see three identical rows have been deleted leaving only the original ones.
  • That is how to delete duplicates in Excel for projects with rows.

Read also

Step-by-step guide on how to save a Word document as a PDF

You now have the skills and knowledge that will help you understand how to remove duplicates Excel with ease. Next time you come across a stranded friend, be quick to guide him/her through the process and solve their problem.

READ ALSO: How to insert checkbox in Word


Online view pixel