📋 How to Highlight Duplicates in Google Sheets and Remove

1. Overview

\[ \begin{array}{l} \textbf{Google Sheets allows you to easily find and highlight duplicate entries --} \\ \text{using conditional formatting or Apps Script automation.} \\ \textbf{Once identified, duplicates can be reviewed and removed --} \\ \text{to keep your dataset clean and accurate.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \textbf{Step 1:} & \text{Select the range of data you want to check for duplicates.} \\ \textbf{Step 2:} & \text{Go to Format $\rightarrow$ Conditional formatting.} \\ \textbf{Step 3:} & \text{Under "Custom formula is", enter: } \texttt{=COUNTIF(A:A,A1)>1}. \\ \textbf{Step 4:} & \text{Choose a highlight color and click "Done".} \\ \textbf{Step 5:} & \text{Optionally, use Apps Script to remove highlighted duplicates.} \end{array} \]

3. Sample Google Apps Script

// This script removes duplicate rows based on the first column
function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var seen = {};
  var rowsToDelete = [];
  
  for (var i = values.length - 1; i >= 1; i--) { // Skip header row
    var key = values[i][0]; // First column value
    if (seen[key]) {
      rowsToDelete.push(i + 1);
    } else {
      seen[key] = true;
    }
  }
  
  // Delete duplicate rows
  rowsToDelete.forEach(function(row) {
    sheet.deleteRow(row);
  });
}

4. Important Notes

\[ \begin{array}{l} \text{• Conditional formatting only highlights duplicates; it does not remove them.} \\ \text{• Always back up your sheet before running a delete script.} \\ \text{• Adjust the COUNTIF formula range to match your actual data.} \\ \text{• In Apps Script, modify the column index if duplicates are in a different column.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Raw Data} \xrightarrow{\text{Highlight Duplicates}} \text{Clean Data after Removal} \]

Where: \[ \text{Clean Data} = \{ \text{Unique Entries Only} \} \]