Detect and highlight data-entry anomalies

A finance team types thousands of numbers a year, and a slipped key happens to everyone. Northwind once approved a £10,000 expense that should have been £1,000 — a single extra zero, caught only when the month-end totals looked wrong. By then the payment had already gone out.

This automation catches that mistake at the keyboard. Every time someone enters an amount on the Expenses sheet, the script compares it against the spread of every other expense and colours the whole row pink if it sits more than three standard deviations from the average. A typo’d zero is an obvious outlier, so it lights up the instant it is entered — while the person who typed it is still looking at the row.

What you’ll need

• An Expenses sheet with a header row. One column must be headed exactly amount — that is the column the script watches.
• Enough existing expense rows for the average and spread to mean something. A handful of rows will produce a jumpy threshold; a few dozen settles it down.
• An installable onEdit trigger, set up once — see Trigger it.

The script

// The tab this automation watches. Edits to any other sheet are ignored.
const EXPENSES_SHEET_NAME = 'Expenses';

// The header of the column holding expense amounts.
const AMOUNT_HEADER = 'amount';

// How many standard deviations from the mean counts as an anomaly.
const ANOMALY_SIGMA = 3;

// The pink fill used to flag a suspicious row.
const FLAG_COLOUR = '#fde2e1';

/**
 * Runs on every edit. If the edited cell is an amount on the Expenses
 * sheet, it colours the row pink when the value is a statistical outlier.
 *
 * @param {Object} e The edit event passed by the onEdit trigger.
 */
function onEdit(e) {
  // Guard: a manual run has no event object — nothing to do.
  if (!e || !e.range) return;

  // Only act on the Expenses sheet.
  const sheet = e.range.getSheet();
  if (sheet.getName() !== EXPENSES_SHEET_NAME) return;

  // 1. Find the amount column from the header row.
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const amountCol = headers.indexOf(AMOUNT_HEADER) + 1;

  // Ignore edits outside the amount column (and bail if the header is missing).
  if (amountCol === 0 || e.range.getColumn() !== amountCol) return;

  // 2. Read every amount already on the sheet.
  const lastRow = sheet.getLastRow();
  if (lastRow < 3) return; // need at least two rows for a meaningful spread
  const data = sheet.getRange(2, amountCol, lastRow - 1)
    .getValues()
    .flat()
    .filter((v) => typeof v === 'number');
  if (!data.length) return;

  // 3. Work out the mean and standard deviation of the column.
  const mean = data.reduce((a, b) => a + b, 0) / data.length;
  const variance = data.reduce((s, x) => s + (x - mean) ** 2, 0) / data.length;
  const sd = Math.sqrt(variance);

  // 4. Compare the just-edited value against the spread.
  const val = parseFloat(e.value);
  const row = sheet.getRange(e.range.getRow(), 1, 1, sheet.getLastColumn());

  // 5. Colour the row pink if it is an outlier, or clear it if it is not.
  if (!isNaN(val) && sd > 0 && Math.abs(val - mean) > ANOMALY_SIGMA * sd) {
    row.setBackground(FLAG_COLOUR);
  } else {
    row.setBackground(null);
  }
}

How it works

1. The function runs on every edit. If there is no event object — which happens when you run it by hand — it returns straight away.
2. It checks the edited cell is on the Expenses sheet and ignores anything else, so the trigger stays cheap on a busy spreadsheet.
3. It reads the header row, finds the column headed amount, and ignores edits to any other column.
4. It reads every numeric value in the amount column and works out the mean and standard deviation — the average and the typical spread around it.
5. It compares the value just entered against that spread. Anything further than three standard deviations (ANOMALY_SIGMA) from the mean is treated as an anomaly.
6. An anomalous row gets a pink fill across all its columns; a normal value clears any existing fill, so correcting a typo removes the flag.

Example run

Suppose the Expenses sheet already holds a steady run of small expenses:

date	category	amount
02 Sep	Travel	120
03 Sep	Software	89
04 Sep	Travel	240
05 Sep	Catering	310

Someone now adds a new row and means to type 1000 but types 10000:

date	category	amount
06 Sep	Equipment	10000	← row turns pink

The £10,000 figure sits far outside the spread of the existing expenses, so the whole row fills pink the moment Enter is pressed. Correcting it to 1000 brings it back inside the normal range and the fill clears automatically.

Trigger it

Because a simple onEdit trigger cannot always read across the sheet reliably, set up an installable onEdit trigger:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose the onEdit function, event source From spreadsheet, event type On edit.
4. Save and approve the authorisation prompt.

From then on, every amount typed into the Expenses sheet is checked the moment it is entered.

Watch out for

• The threshold needs data. With only a few rows, one large expense skews the mean and standard deviation, and the next normal row can look like an anomaly. The flag settles down once a few dozen rows exist.
• A genuine large expense will be flagged too. The pink fill means “check this”, not “this is wrong” — clear it by hand once you have confirmed the figure is correct.
• It only checks the cell that was just edited. Pasting a whole block of rows fires one event for the range, so older anomalies pasted in bulk may slip through — re-enter or re-key a cell to re-check a row.
• Standard deviation assumes a roughly normal spread. If your expenses are wildly bimodal — say, tiny stationery costs and huge equipment buys — the three-sigma rule will misfire. Consider running the check per category.
• The script writes a background colour, not data. A flagged row is still a valid row; nothing is deleted or blocked.