Google Sheets is a powerful tool when working with data. However, duplicate entries can often pose a significant challenge. In this tutorial, we show you how to swiftly locate and manage duplicates in Google Sheets using a simple formula based on the COUNTIF function.
Using the COUNTIF Function to Find Duplicates
COUNTIF is an in-built function in Google Sheets which counts the number of times a specified value appears in a given range. We’ll use this function to identify duplicate entries.
Assume we are working on a list of names in column A. You would input the following formula into cell B2:
=COUNTIF($A$2:$A2, A2)>1
This formula will check if a name in cell A2 has appeared before in the range from cell A2 to the current cell. If a duplicate is found, the formula will return TRUE; otherwise, it will return FALSE.
Highlighting Duplicates Using Conditional Formatting
It’s possible to use Google Sheets’ conditional formatting feature to highlight duplicates. Follow the steps below:
- Highlight the column you wish to find duplicates in.
- Click on Format in the menu, then choose Conditional formatting.
- Choose “Custom formula is” in the Format cells if… dropdown menu.
- Enter our formula in the value field: =COUNTIF($A$2:$A2, A2)>1.
- Select a formatting style to highlight the duplicates, then click on Done.
Duplicates in your selected range will now be highlighted in the color you chose.
Conclusion
Finding duplicates in Google Sheets doesn’t need to be time-consuming or complex. By using the COUNTIF function or conditional formatting, you can quickly identify and manage duplicate entries, ensuring your data remains accurate and reliable.
Remember to adjust the formula to match your specific needs, for instance, by changing the range or the column index. Happy data cleaning!