Method 1: Highlight Duplicates Using Conditional Formatting

One of the simplest and most effective ways to highlight duplicates in Google Sheets is by using conditional formatting. Here’s a step-by-step guide to help you set it up:

1. Open Your Google Sheet: Start by opening the Google Sheets document where you want to highlight duplicates.
   
   
2. Select the Range of Data: Highlight the range of cells where you suspect there may be duplicates. You can select an entire column or a specific range.
   
   
3. Open Conditional Formatting: Go to the Format menu at the top of your Google Sheets document. From the dropdown, select Conditional formatting.
   
   
4. Set the Formatting Rule: In the conditional formatting pane that appears on the right, under the “Format cells if” section, select the rule Custom formula is.
   
   

Enter the Formula: In the box next to the formula, enter the following:

=countif(A:A, A1) > 1

1.  This formula checks if the value in each cell appears more than once in the selected range (replace A:A with your specific range if needed).
   
   
2. Choose Your Formatting Style: Select the color you want to use to highlight the duplicates. You can choose from several color options or customize your own.
   
   
3. Apply the Rule: Click Done to apply the rule. Any duplicate values in the selected range will now be highlighted.
   
   

By using conditional formatting, you can quickly and visually identify duplicates in your Google Sheets.

Method 2: Use the UNIQUE Function to Identify Duplicates

If you want to create a list that excludes duplicates or simply want to identify duplicates, the UNIQUE function is an excellent tool. Here’s how you can use it to find and highlight duplicates:

1. Open a New Column: Insert a new column next to your data to list the unique values.
   
   

Use the UNIQUE Formula: In the new column, type the following formula:

=UNIQUE(A1:A)

1.  This will display a list of all unique values from the range A1:A. Any duplicates will be excluded.
   
   
2. Compare Lists: Now, compare the new list with the original column to spot duplicates manually, or use conditional formatting as described earlier to highlight them.
   
   

The UNIQUE function helps identify and list unique values, making it easier to detect duplicates and clean up your data.

Method 3: Highlight Duplicates Using Google Sheets Add-ons

There are various add-ons available for Google Sheets that can help automate the process of identifying and highlighting duplicates. One popular add-on is Remove Duplicates, which simplifies the process. Here’s how to use it:

1. Install the Add-On: Go to Add-ons > Get add-ons in the menu. Search for Remove Duplicates and click on Install.
   
   
2. Select Your Range: Once installed, go to Add-ons > Remove Duplicates > Start.
   
   
3. Choose the Range to Check: The add-on will prompt you to select the range of data you want to check for duplicates.
   
   
4. Choose Duplicate Detection Criteria: Select the criteria for detecting duplicates (whether by row, column, or a specific set of conditions).
   
   
5. Highlight Duplicates: After the add-on runs, it will highlight duplicates in your Google Sheet.
   
   

Using an add-on can streamline the process, especially for large data sets.

Method 4: Use Google Apps Script to Highlight Duplicates

For more advanced users, Google Apps Script allows you to automate the process of finding and highlighting duplicates in Google Sheets. Here’s how to create a custom script:

1. Open the Script Editor: Go to Extensions > Apps Script.
   
   

Create a New Script: In the script editor, enter the following code:

function highlightDuplicates() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getDataRange();

  var values = range.getValues();

  var duplicates = [];

  for (var i = 0; i < values.length; i++) {

    for (var j = i + 1; j < values.length; j++) {

      if (values[i][0] == values[j][0]) {

        duplicates.push(i);

        duplicates.push(j);

      }

    }

  }

  for (var k = 0; k < duplicates.length; k++) {

    range.getCell(duplicates[k] + 1, 1).setBackground(‘#FF0000’);

  }

}

1.  
2. Run the Script: Save and run the script. This will highlight any duplicate entries in the first column of your sheet.
   
   

Using Google Apps Script offers flexibility and can be customized to fit your needs.

Method 5: Manually Highlight Duplicates

If you’re dealing with a small dataset or prefer a manual approach, you can highlight duplicates by eye. Here’s how:

1. Scan the Data: Look through your data to find any repeated values.
   
   
2. Use the Fill Color Tool: Highlight the duplicate values and manually use the fill color tool in the toolbar to apply a color.
   
   

While this method is not automated, it can work well for small, manageable data sets.