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?
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.
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.
- 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 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.
- 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.
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.
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.
- 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.
- 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.
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.
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.
- Choose the ‘Select All’ option, tick on the column, and ‘My data has headers.’
- Click OK to erase and remain with the unique data.
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.
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.
- 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.
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