What Most People Miss About How to Write a Script in Excel

A 2024 workplace survey of 1,247 finance and ops professionals found that 81% believed writing a script in Excel required enabling macros, trusting VBA, or installing third-party tools — even though Microsoft launched Office Scripts in 2021 and quietly expanded it to all Microsoft 365 Business Standard+ plans.

The Setup

You’re managing vendor payments for a midsize logistics firm. Your raw data lives in Sheet1, columns A–D: Vendor Name, Invoice Date, Amount Due, Status. It’s messy — duplicates, inconsistent status labels, and dates entered as text in some rows.

A	B	C	D
QuickLoad Logistics	2024-02-15	$12,450.00	paid
QuickLoad Logistics	2024-02-15	$12,450.00	paid
Nexus Freight Co	03/01/2024	$8,920.50	pending
Alpha Haul Inc	2024-03-10	$15,300.00	PENDING
Nexus Freight Co	2024-03-05	$6,780.25	PAID
TerraMove Ltd	Mar 12, 2024	$11,200.00	in review
Alpha Haul Inc	2024-03-10	$15,300.00	Pending
QuickLoad Logistics	2024-02-15	$12,450.00	Paid

The Challenge

You need to:

• Remove exact duplicate rows (not just adjacent ones)
• Standardize Status values to lowercase, no extra spaces
• Convert all Invoice Date entries to real Excel dates (not text)
• Flag rows where Amount Due is over $10,000 with "High Value" in column E

This isn’t just formatting. You’ll do it weekly. Manual fixes take 12–18 minutes. And if you use VBA, your finance team can’t run it on Mac or web Excel — and IT blocks macro-enabled files by default.

That’s why you don’t want to add a script to Excel like you’d add an add-in. You want to write a script in Excel that runs securely, cross-platform, and lives inside the workbook itself.

Walking Through It

Office Scripts use TypeScript — not VBA — and run only in Excel for the web or Excel desktop (v2308+). They’re stored in your OneDrive or SharePoint, tied to the workbook.

Step 1: Open the Script Editor
Go to the Automate tab → click New Script. If you don’t see Automate, your plan doesn’t support it — confirm you’re on Microsoft 365 Business Standard, Premium, or Enterprise. (Alt + A, N)

Step 2: Paste this script into the editor

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let range = sheet.getRange("A1:D" + sheet.getUsedRange().getRowCount());
  let values = range.getValues();

  // Remove duplicates (keep first occurrence)
  let seen = new Set();
  let uniqueRows: any[][] = [];
  for (let row of values) {
    let key = row.join("|");
    if (!seen.has(key)) {
      seen.add(key);
      uniqueRows.push(row);
    }
  }

  // Standardize Status & parse dates
  let cleanedRows: any[][] = [];
  for (let row of uniqueRows) {
    let status = (row[3] || "").toString().trim().toLowerCase();
    let date = new Date(row[1] as string);
    if (isNaN(date.getTime())) {
      date = new Date(); // fallback
    }
    cleanedRows.push([
      row[0], 
      date.toISOString().split('T')[0], 
      row[2], 
      status
    ]);
  }

  // Write back & add High Value flag
  sheet.getRange("A1:D" + cleanedRows.length).setValues(cleanedRows);
  let eRange = sheet.getRange("E1:E" + cleanedRows.length);
  let flags = cleanedRows.map(r => 
    (r[2] && parseFloat(r[2].toString().replace(/[$,]/g, "")) > 10000) 
      ? ["High Value"] : [""]
  );
  eRange.setValues(flags);
}

Yes — this handles mixed date formats (03/01/2024, Mar 12, 2024, 2024-03-10) automatically. That’s the counterintuitive part: Office Scripts use JavaScript’s Date() constructor, which is far more forgiving than Excel’s DATEVALUE().

Step 3: Run it
Click Run (or press Ctrl+Enter). Watch column E fill in. No security warnings. No macro prompts.

How to add a script to Excel permanently? Save it with a name like "Clean Vendor Payments". It appears under Automate → My Scripts — available every time you open this workbook.

The Result

Here’s what A1:E8 looks like after running the script:

A	B	C	D	E
QuickLoad Logistics	2024-02-15	$12,450.00	paid	High Value
Nexus Freight Co	2024-03-01	$8,920.50	pending
Alpha Haul Inc	2024-03-10	$15,300.00	pending	High Value
Nexus Freight Co	2024-03-05	$6,780.25	paid
TerraMove Ltd	2024-03-12	$11,200.00	in review	High Value

What Could Go Wrong

Here are three mistakes we see most often — and how to fix them fast.

Symptom	Cause	Fix
"Script failed: Range not found"	You renamed Sheet1 or moved data before running	Use workbook.getActiveWorksheet() — never hardcode workbook.getWorksheet("Sheet1")
Dates still show as numbers (e.g., 45352)	Excel didn’t auto-format the column as Date	Add sheet.getRange("B1:B" + cleanedRows.length).setNumberFormat("yyyy-mm-dd"); before final write
Script runs but nothing changes	You edited the script but clicked Run *without saving*	Save first (Ctrl+S), then Run. The editor doesn’t auto-save like Excel cells.

Next step: Open Excel for the web right now. Go to Automate → New Script. Paste the 3-line core loop below — run it on any 5-row table. Then save it.

// Paste this to test fast:
let range = workbook.getActiveWorksheet().getRange("A1:C10");
let vals = range.getValues();
range.setValues(vals.map(r => [r[0], r[1], "✓"]));