How to find and highlight duplicate data in Google Sheets?
When working with large amounts of data, it’s important to be able to identify duplicates efficiently. Whether you need to ensure your data set is unique or highlight recurring entries for review, Google Sheets offers a simple method for doing so using conditional formatting.
First, open your spreadsheet in Google Sheets. Here’s an image of some sample data:
How to Highlight Duplicates in Google Sheets
To highlight duplicates, you’ll use conditional formatting with a custom formula. The formula =COUNTIF(A:A, A1) > 1 will help identify duplicate entries.
Here’s how to use it step by step:
First, select the column where you want to search for duplicates. For example, if you’re examining the Director column, highlight column C. Next, go to the top menu and click on ‘Format,’ then select ‘Conditional formatting.’ A sidebar will appear with a ‘Format cells if’ section.
Choose ‘Custom formula is,’ and input =COUNTIF(B:B, B1) > 1 to highlight duplicate entries in that column. Once you apply this formula, any repeat values in column C will be highlighted, making it easy to spot duplicates.
Expanding to Multiple Columns
If you’re looking to find duplicates across multiple columns, such as comparing movie titles and directors, you can adapt the approach slightly. Highlight the range of columns you wish to check—for example, columns B and C. Clear any existing conditional formatting rules, then go back to ‘Format’ and ‘Conditional formatting.’
Input a custom formula like =COUNTIF(B:B, B1) > 1 to check for duplicates starting in the specified range.
To broaden the range, adjust the ‘Apply to range’ field to include the desired columns, such as B1. Make sure your formula matches the first column’s cell reference, for instance, =COUNTIF(C:C, C1) > 1 if checking starting from column C.
Using Absolute References
For more precise control, use absolute cell references with the “$” symbol to lock your range. This method ensures that Google Sheets evaluates a specific range accurately. The formula =COUNTIF($B$1:$C$50, B1) > 1 will check for duplicates within a defined range and highlight them.
By understanding these techniques, you can better manage and clean up your data in Google Sheets, ensuring your information is accurate and free from unintended repetition.
