When handling large data sets in Google Sheets, it is common to encounter duplicate entries. Identifying and managing these duplicates is crucial for maintaining data integrity. Thankfully, Google Sheets comes with a built-in feature that allows us to highlight duplicate entries within a given range, enabling us to easily spot and take necessary action on these duplicates.
In this blog post, we will guide you through the process of highlighting duplicates in Google Sheets.
Step-by-Step Guide:
-
First, select the range of cells where you want to find duplicates. For instance, if you want to check for duplicates in column A, click on the column header A to select the entire column.
-
Next, navigate to the menu by clicking on Format, then select Conditional formatting from the drop-down menu.
-
In the Conditional format rules panel that appears on the right, you’ll see the range you’ve selected displayed in the Apply to range fields. This range can be manually adjusted if required.
-
Now, select Custom formula is from the Format cells if drop-down menu.
-
Input the following formula in the box that appears:
=COUNTIF(A:A; A1)>1
This formula essentially counts the number of times a particular entry appears in column A. If it’s more than once, it’s a duplicate, and it will be highlighted.
-
Then, choose your preferred formatting style under the Formatting style section to highlight the duplicates. You can decide the text color, fill color, and other parameters as per your needs.
-
Finally, click on the Done button to apply the rule.
And voilà! Your selected range will now highlight duplicate entries as per your chosen formatting style. This method will save you lots of time and ensure accuracy when dealing with large datasets on Google Sheets.
