Duplicates are values that appear more than once in the Workbook. The duplicates may lead to wrong calculations, and thus they are a need to get rid of them. Excel allows users to check duplicates on two sheets found in the same Workbook. This post will discuss common ways of checking duplicates in the same Workbook.
Using the Conditional Formatting Tool
Conditional Formatting Tool is an inbuilt feature in Excel that can be used to check for duplicate values. Below are the steps to follow while using this method:
1. Open the Excel application.
2. Open the Workbook containing your dataset or sheets you need to check for duplicates.
3. Select the dataset on the first sheet. Then, click the Home tab from the ribbon.
4. In the Styles section, click the Conditional Formatting drop-down button. From the menu, click the New Rule button.
5. Use a formula to determine which cell to format the button. Type the rule that will compare the two sheets in the Rule box. For Example, =COUNTIF(Sheet2!$A$2:$A$6,A2)
6. Click the Format button and select the formatting option to show the duplicate cells. Finally, click the OK button.
Using the New Window Tool
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing your dataset or sheet. You need to check for duplicates.
3. Click the View tab from the ribbon, and locate the Window section.
4. Click the New Window button from the section.
5. Locate the Arrange All button, and click on it to open the Arrange Windows dialogue box.
6. Toggle on the Vertical button in the dialogue box and click the OK button. Now, you can see all the sheets.
Using the VLOOKUP function
Syntax
=VLOOKUP (lookup_value, sheet name!column index,value_if_true, value_if_false)
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing your dataset or sheet. You need to check for duplicates.
3. If you open a new worksheet, ensure you have all the required arguments in different cells.
4. Click on an empty cell that will hold the results, and then click on the Formula bar.
5. Type the Equal sign followed by the VLOOKUP function. That is, =VLOOKUP (
6. Enter the value you need to check if it has the duplicate. Then, enter the other sheet from which you want to check the value and select the columns.
7. Finally, enter the value that will be returned if the result is true and if the result is false. For Example, =VLOOKUP (B5,Sheet2!B2:B6,2,FALSE)