Finding duplicate entries across two columns in Google Sheets can be a tedious task. Luckily, Google Sheets offers built-in features to quickly identify and highlight these duplicates. This guide provides a step-by-step approach on how to compare two columns for duplicates and customize the highlighting for better visibility.
Identifying Duplicates Across Two Columns
Here’s how to highlight duplicates in two columns using conditional formatting:
1. Select the Target Columns
First, select the two columns you want to compare. You can either click and drag your cursor across the columns or manually enter the range (e.g., A1:B10) in the name box located in the top left corner of the sheet.
2. Access Conditional Formatting
Navigate to the “Format” menu in the toolbar and select “Conditional formatting.” This will open the Conditional format rules sidebar on the right side of your screen.
3. Apply a Custom Formula
In the Conditional format rules sidebar, under “Format rules,” choose “Custom formula is” from the dropdown menu.
4. Implement the COUNTIF Formula
In the formula input box, enter the following formula:
=COUNTIF($A$1:$B$10,A1)>1
Remember to adjust $A$1:$B$10 to match the actual range of your selected columns. This formula counts the occurrences of each cell value within the specified range. If a value appears more than once (indicating a duplicate), the formula returns TRUE, triggering the conditional formatting. The dollar signs ($) create an absolute reference, ensuring the formula correctly references the entire range as you apply it to other cells.
5. Customize and Apply
Choose your desired formatting style (e.g., change the fill color, text color, or font style) to highlight the duplicates. Once you are satisfied with the formatting, click “Done.” Google Sheets will now highlight all duplicate entries across the two columns based on your chosen formatting.
This method allows for efficient and accurate comparison of two columns in Google Sheets to identify and highlight duplicate data. By leveraging the power of conditional formatting and the COUNTIF function, you can streamline your data analysis process.
