People will often receive Excel file with many duplicate options, so it is important to filter them out and highlight or remove them, which will improve work efficiency and save unnecessary trouble. To prevent this problem, we need to have some kind of ability or formula to find duplicates in excel. Luckily, Excel does have the exact formula to find duplicates, and learning it, is essential to deal with those unseen duplicate entries.
Considering people often password protect their important document, so here also offers a professional Excel password recovery software – PassWiper for Excel to help you.
Is There a Formula in Excel to Find Duplicates?
There are several ways to find duplicates in Excel. Among all of them, using a duplicate checking formula is one of the preferred and easy ways to find repeated entries. Yes, there are formulas to find duplicates in excel. Using the formula to find duplicates is really helpful as it can be copied and pasted to different cells and even to different worksheets allowing you to work quickly. It eradicates the need to recreate the same formula whenever you want to perform the same operations over huge amounts of data.
Of course formula is one of the easiest ways to find duplicates, there’s the common steps to highlight duplicates.
How to Create Excel Formula to Find Duplicates in One Column
If you would like to search out duplicates in one specific column of your surpass sheet, you’ll be able to do that mistreatment the COUNTIF operate or mistreament conditional information.
Way 1. Finding duplicates using COUNTIF
Whether you’ve got numeric knowledge on your list or the other things knowledge together with invoices, merchandise IDs, or names. You’ll be able to use the formula to search out duplicates in surpass in one column simply.
Step 1. Suppose that you want to check duplicates in column B.
Step 2. Use the COUNTIF formula with the syntax”=COUNTIF (B:B, B2)>1″.
Step 3. You need to insert this COUNTIF formula in the next empty column.
Step 4. Press”Enter”, it will show TRUE for duplicate fields and FALSE for non-duplicate fields.
Way 2. Finding duplicates using Conditional Formatting
Using conditional formatting excel formula is another efficient way to find duplicates in one column. This method also allows you to highlight the duplicate values as well so you can decide if you want to remove them or not.
Step 1. Choose the cells from which you want to check for duplicates.
Step 2.Open Home tab. Find the Conditional Formatting option. For other excel versions, you need to find a conditional formatting icon available on your taskbar.
Step 3. Click on it and from the drop-down menu, select Highlight Cells Rules. Choose the Duplicate Values option.
Step 4. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.
How to Create Excel Formula to Find Duplicates in Multiple Columns
Sometimes, duplication occurs in more than one column, and to avoid that we need to use a different formula.
Method: Finding Duplicates in Multiple Columns using COUNTIF function
The COUNTIF function allows us to count the number of cells from a range based on multiple criteria.
Here’s the step-by-step procedure to find duplicates in multiple columns.
Step 1. Choose the cell range from which you want to check for duplicates and open the Home tab and select the Conditional Formatting drop-down menu. You will see the New Rule option there. Click on it.
Step 2. This will open a New Formatting Rule window. Now set the rule type to Use a formula to determine which cells to format. In the formula box, type the COUNTIF formula: =COUNTIFS($B$5:$B$10,$B5,$C$5:$C$10,$C5,$D$5:$D$10,$D5)>1 and Click Format.
Step 3. It will open the Format Cells window. Select the Fill option. Select the color of your choice from the Background Color group. When done, click OK.
Step 4. Click OK again to finish the process. You can see the sample color on the opened window. The duplicate values will be highlighted with the color you’ve selected.
How to Create Excel Formula to Find Duplicates in Two Worksheets
You may face situations in which you need to check duplicates in more than one worksheet. For this purpose, you can use Compare Tables.
Step 1. Select the Compare Tables options from your top menu.
Step 2. Select table 1. You need to select the range with your table 1 and all the changes will be applied to the table.
Step 3. Select table 2. This step involves the opening of workbooks and worksheets. You can see them from the Select your second table field.
Once done with choosing the second table, click Next.
Step 4. Specify the data you are searching for. In this step, you can choose the data, you are looking for in table 1:
Once done, click Next.
Step 5. Choose the columns to compare. This step will open up a list of columns in both Excel spreadsheets that you want to compare.
You need to select the column or multiple columns from the first table and the matching ones from the second worksheet to compare them and check for duplicate data.
Once the columns are selected, click Next.
Step 6. Choose what you want to check. In this final step, you need to specify what you are going to do with the found duplicate values.
Step 7. You can also Select the Identify in the Status column option. This option allows you to insert a special additional column into the table and specify duplicate values there:
Step 8. Click Finish! The duplicate values can be seen in table 1 and processed as you want!
Related Issue: How to Recover/Remove Excel Password?
Excel users also find it common to forget the password and it becomes hard for them to recover it.
Tools like PassWiper for Excel are there to help you out in such situations. It’s an amazing tool to recover excel passwords. PassWiper for Excel is very easy to use, fast, 100% secure, efficient, and no technical knowledge is required to use it.
Here’s how you can use this remarkable software for Excel file password recovery in a few clicks. You can Click Here to learn about more information of this tool and free download it to try.
Summary
This helpful guide shows the steps and methods for finding duplicates from a column, using formulas.
Moreover, in some cases, users also face problems where they forget their password and face trouble while accessing the file they want to work on. The question is how you can open that file? The answer is PassWiper for Excel. The tool is useful for this purpose as it is used widely for excel password recovery. It is quick and easy to use. Whenever you face such an issue, give PassWiper for Excel a try, you will find it worthy!
