Are you searching for a method to easily identify duplicate content in your Google Sheets, particularly in large datasets? Fortunately, there’s a straightforward technique. By adjusting a few simple settings, you can automatically highlight duplicate content/data in your chosen color. Then, decide whether to keep or delete these duplicates. It’s quite simple.
Here’s the step-by-step guide to setting it up.
Highlight Duplicate Data in Google Sheets
Suppose you have a spreadsheet with a column of names in Column B and need to pinpoint the duplicates. Instead of manually scanning each name, Google Sheets can automatically highlight them for you.
To highlight duplicated data in Column B of Google Sheets, you’ll use Conditional Formatting with a custom formula.
Here’s how you can do it:
- Select the entire Column B by clicking on its header.
- Navigate to the menu and select Format > Conditional formatting.
- In the "Conditional format rules" sidebar on the right, under the “Format cells if” dropdown, choose "Custom formula is".
- Input the following formula:
=countif(B:B, B1)>1. This formula calculates how often the value in each cell of Column B appears in the entire column. If a value appears more than once, the formatting you choose will be applied to those cells.
- Select your desired formatting style, like background color, for the duplicate cells, and then click “Done“.
Once you’ve completed these steps, any cell in Column B with a value that appears more than once elsewhere in the column will be highlighted in the style you selected.
Another Simple Method…
There’s yet another effortless method to spot duplicated data. Just hover over a cell in column B, click on the arrow, and select to sort the entire column either A-Z or Z-A alphabetically.
Once sorted, any duplicated data, if present, will be grouped together. However, without highlighting, this still requires a bit of visual scanning to identify the duplicates.
Detect and Delete Duplicate Data
If your primary goal is simply to detect and delete all duplicate data, without the need for highlighting, follow these steps.
First, select or highlight Column B. Then, navigate through the menu: go to Data > Data cleanup > Remove duplicates.
After that, click on "Remove duplicates".
This action will delete all duplicated data in the selected column.