Spot the Doubles. How to Highlight Duplicates in Google Sheets

Instructions to Highlight Duplicates in Google Sheets Manually
Duplications in Google Sheets can be easily identified and highlighted using conditional formatting. Follow the steps below to manually highlight duplicates in Google Sheets:

  1. Open your Google Sheets document containing the data you want to check for duplicates.
  2. Select the range of cells where you want to highlight the duplicates. For instance, if you want to highlight duplicates in column A, select the cells in that column.
  3. Click on "Format" in the top menu bar and select "Conditional formatting."
  4. In the conditional formatting sidebar that appears on the right, click on the drop-down menu next to "Format cells if," and select "Custom formula is."
  5. In the input box next to the drop-down menu, enter the formula =countif($A:$A,A1)>1. Adjust the formula based on the column you want to check for duplicates.
  6. Click on the "Done" button, and the cells with duplicate values will be highlighted with the default formatting.

By following these steps, you can easily highlight duplicates manually in Google Sheets without the need for any scripting.

Google Apps Script to Highlight Duplicates
If you prefer an automated solution, you can use Google Apps Script to highlight duplicates in Google Sheets. The script below can be attached to a button or run periodically to highlight duplicates programmatically:

function highlightDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  range.setBackground(null);

  var data = range.getValues();
  var duplicateColors = ["#FF0000", "#00FF00", "#0000FF"]; // Customize as needed

  var duplicates = [];

  for (var i = 0; i < data.length - 1; i++) {
    var row = data[i];
    if (duplicates.indexOf(row.join()) === -1) {
      for (var j = i + 1; j < data.length; j++) {
        var compareRow = data[j];
        if (row.join() === compareRow.join() && duplicates.indexOf(compareRow.join()) === -1) {
          duplicates.push(row.join());
          range.getCell(i + 1, 1).setBackground(duplicateColors[duplicates.length % duplicateColors.length]);
          range.getCell(j + 1, 1).setBackground(duplicateColors[duplicates.length % duplicateColors.length]);
        }
      }
    }
  }
}

To use this script:

  1. Open the Google Sheets document and click on "Tools" in the top menu bar.
  2. From the dropdown menu, select "Script editor."
  3. In the script editor that opens, replace any existing code with the provided script.
  4. Save the script by clicking on the floppy disk icon or by pressing Ctrl + S.
  5. Close the script editor.
  6. A new function named "highlightDuplicates" should now appear in the "My Function" dropdown menu in the script editor toolbar.
  7. Run the function by selecting it from the dropdown menu and clicking the play button (▶️).

The script will highlight any duplicates it finds in the active sheet. You can customize the duplicateColors array to specify the colors to be used for highlighting.

Use Cases and Examples
The ability to identify and highlight duplicates in Google Sheets is helpful in various scenarios. Here are some sample use cases:

  1. Data Cleansing: When dealing with large datasets, duplicate values can skew analysis and create errors. By highlighting duplicates, you can swiftly identify and delete or modify these entries to ensure accurate results.

  2. Inventory Management: In an inventory spreadsheet, it's essential to identify duplicate items to avoid confusion and maintain accurate record-keeping. By highlighting duplicate product names or codes, you can quickly spot discrepancies and take necessary actions.

  3. Contact Lists: When managing contact lists or customer databases, identifying duplicate entries is crucial for maintaining data integrity. Highlighting duplicates lets you merge or delete records accordingly to keep your database clean and up to date.

  4. Order Monitoring: In an order tracking spreadsheet, duplicate order numbers can lead to confusion or errors. By highlighting duplicates, you can identify any discrepancies and take the necessary steps to rectify the situation.

By utilizing the ability to highlight duplicates in Google Sheets, you can enhance data accuracy, streamline workflows, and ensure the integrity of your spreadsheet-based processes.

To reference another sheet in Google Sheets, you can use the IMPORTRANGE function.
If you want to learn how to highlight duplicates in Google Sheets, you can follow this guide.
To alphabetize your data in Google Sheets, you can use the SORT function.
If you need to sort your data by date in Google Sheets, you can use the SORTBY function.
To remove blank rows in Google Sheets, you can follow this tutorial.

Published