Office Scripts: Highlight Duplicates in a Table With One Button

Duplicate invoice numbers, repeated email addresses, the same GSTIN twice — highlighting them should be one click. Office Scripts make that possible in Excel on the web.

The script

Automate → New Script, paste, and save as “Highlight duplicates”:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const table = sheet.getTables()[0];
  if (!table) { console.log("No table on this sheet"); return; }

  // Column to check: change the name to match your table
  const col = table.getColumnByName("Invoice No").getRangeBetweenHeaderAndTotal();

  // Remove earlier highlights on this column, then add a duplicate rule
  col.clearAllConditionalFormats();
  const cf = col.addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria).getPreset();
  cf.setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues });
  cf.getFormat().getFill().setColor("#FCE4D6");
  cf.getFormat().getFont().setColor("#9C2B00");
}

Add a button

In the Code Editor, open the script’s menu (…) → Add in workbook. A button appears on the sheet; anyone with the file can click it.

Variations

• Check a different column: change "Invoice No".
• Unique values instead: use uniqueValues in the rule.
• Run it on a schedule or when a file arrives, using Power Automate — see the Office Scripts course.