How to Highlight Duplicated Cells in Google Sheets

By . in Internet. Updated on .

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.

Column B with names

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:

  1. Select the entire Column B by clicking on its header.
    2. Highlighting Column B
  2. Navigate to the menu and select Format > Conditional formatting.
    3. Conditional Formatting Menu
  3. In the "Conditional format rules" sidebar on the right, under the “Format cells if” dropdown, choose "Custom formula is".
    4. Custom Formula Selection
  4. 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.
    5. Inserting Formula
  5. Select your desired formatting style, like background color, for the duplicate cells, and then click “Done“.
    6. Choosing Formatting Style

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.

Highlighted Duplicates Result

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.

Sorting Column Alphabetically

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.

Remove Duplicates Option

After that, click on "Remove duplicates".

Remove Duplicates Button

This action will delete all duplicated data in the selected column.

Duplicated Data Removed
WebsiteFacebookTwitterInstagramPinterestLinkedInGoogle+YoutubeRedditDribbbleBehanceGithubCodePenWhatsappEmail