Witan Lint — The Linting Engine

ESLint for spreadsheets. Eleven semantic rules that catch formula bugs recalculation cannot see.

$ witan xlsx lint budget.xlsx

Error (1):
  D008  Sheet1!F10  Adding values from B2:B8 (USD) and C2:C8 (EUR) with conflicting currencies. Convert to a common currency before adding values.

Warning (3):
  D001  Sheet1!C12  cells B5:B8 contribute with net weight 2.0 via SUM(B1:B10) and SUM(B5:B15)
  D002  Sheet1!D5   VLOOKUP with approximate match mode requires ascending sorted range, but the lookup range is not sorted
  D009  Sheet1!E3   mixed percent-formatted (E1: 0.5) and non-percent (E2: 10) in addition

Info (1):
  D031  Sheet1!A1   Possible spelling error: 'Reveune' (suggestions: Revenue, Revue, Revenue)

5 issues (1 error, 3 warnings, 1 info)

Why lint?

Recalculation catches formula errors. When a formula produces #REF!, #DIV/0!, or #N/A, those errors are visible in the computed values. witan xlsx calc finds them and reports exactly what went wrong.

Linting catches the other kind of bug: formulas that produce valid numbers that happen to be wrong.

• A SUM that double-counts overlapping ranges produces a number, not an error.
• A VLOOKUP on unsorted data returns a value — just not the right one.
• A SUM that silently skips text entries in a column returns a total — minus the rows you expected it to include.
• Adding 50% to 10 gives 10.5, not 60% — the percent format means the cell stores 0.5.

These bugs are invisible to recalculation. They are almost impossible for an agent to catch by reading cell values alone — the values look plausible. Linting analyzes the formula structure and the data it references to find them before they reach your users.

The linting engine

Eleven rules, each targeting a specific class of silent formula bug. Every rule examines formula structure, the referenced data, or both.

D001 — Double Counting

Severity: Warning

Detects overlapping ranges in SUM and addition where the same cells contribute more than once to the result.

=SUM(A1:A10) + SUM(A5:A15)

Cells A5 appear in both ranges. They are counted twice. The formula produces a number, but it is inflated by the value of those six cells.

The engine uses algebraic coefficient analysis: it tracks the net weight of each base cell through the formula's arithmetic. Every cell that participates in a sum starts with weight 1.0. If the same cell is added again through an overlapping range, its weight increases. If any cell ends up with a net weight other than 1.0, it is flagged.

This works through transitive dependencies. If cell C1 contains =SUM(A1:A10) and cell C2 contains =SUM(A5:A15), then =C1+C2 in another cell will still detect that A5 contribute with weight 2.0, even though the overlapping ranges are hidden behind intermediate formulas.

Diagnostic message:

D001  Sheet1!C12  cells A5:A10 contribute with net weight 2.0 via SUM(A1:A10) and SUM(A5:A15)

D002 — Unsorted Lookup Range

Severity: Warning

Detects VLOOKUP, HLOOKUP, MATCH, and XLOOKUP in approximate match mode when the lookup range is not sorted in the required order.

=VLOOKUP(A1, B1:C20, 2)
=VLOOKUP(A1, B1:C20, 2, TRUE)

VLOOKUP defaults to approximate match (TRUE) when the fourth argument is omitted. Approximate match uses a binary search algorithm that requires the lookup column to be sorted in ascending order. If the data is not sorted, the binary search lands on arbitrary rows and returns silently wrong results.

The engine checks the actual cell values in the lookup range for monotonicity. VLOOKUP and HLOOKUP with TRUE require ascending order. MATCH with match type 1 requires ascending; -1 requires descending. XLOOKUP with search mode 2 requires ascending; -2 requires descending.

Diagnostic message:

D002  Sheet1!D5  VLOOKUP with approximate match mode requires ascending sorted range, but the lookup range is not sorted

D003 — Empty Cell Coercion

Severity: Warning

Detects references to empty cells that get silently coerced to 0 or FALSE in numeric and boolean contexts.

=A1 + B1     (where B1 is empty)
=SUM(A1, B1) (where B1 is empty)
=-C3         (where C3 is empty)

When an empty cell is used in arithmetic, comparison, or as an argument to a function that expects a number, Excel silently coerces it to 0. In boolean contexts, it becomes FALSE. The cell looks blank, but it participates in calculations as zero.

This catches bugs where a formula references a cell that should contain data but is accidentally empty — the formula runs without error but computes against a phantom zero instead of the expected value.

Diagnostic message:

D003  Sheet1!C5  Right operand (B1) of A1+B1 references an empty cell that will be coerced

D005 — Non-Numeric Values Ignored

Severity: Warning

Detects when aggregate functions silently skip text and boolean values in ranges.

=SUM(A1:A10)   (where A7 contains "N/A")
=AVERAGE(B:B)  (where B3 contains TRUE)

SUM, AVERAGE, MIN, MAX, PRODUCT, STDEV, VAR, MEDIAN, MODE, and other aggregate functions silently skip non-numeric values when they appear in range arguments. If you have a column of numbers and one cell contains a text entry like "pending" or "N/A", that entry is ignored without warning.

Diagnostic message:

D005  Sheet1!B11  SUM range A1:A10 contains non-numeric value "N/A" at A7 which will be silently ignored

D006 — Broadcast Surprise

Severity: Warning

Detects scalar-to-range broadcasting in arithmetic and comparison operations that is likely unintentional.

=A1 + B1:B10
=C5 * D1:D20

When a single cell is combined with a range in an arithmetic or comparison operation, the scalar is broadcast across the entire range, producing an array result. This is often unintentional — the author meant to reference a matching-size range and accidentally wrote a single-cell reference on one side.

Diagnostic message:

D006  Sheet1!E1  scalar A1 broadcast across range B1:B10 in addition

D007 — Duplicate Lookup Keys

Severity: Warning

Detects VLOOKUP, HLOOKUP, XLOOKUP, and MATCH with duplicate values in the lookup range.

=VLOOKUP("Product A", A1:C20, 3, FALSE)

If the lookup column contains "Product A" in multiple rows, VLOOKUP returns the first match. This is well-defined behavior, but it is often a sign that the lookup range is not what the author intended — they expected a unique key column and the formula silently returns data from whichever row happens to come first.

Diagnostic message:

D007  Sheet1!D5  VLOOKUP lookup range has duplicate keys in column A (e.g., "Product A" at rows 3, 15)

D008 — Mixed Currency

Severity: Error

Detects combining values with conflicting currencies in addition, subtraction, and aggregate functions.

=SUM(B2:B8) + SUM(C2:C8)   (where B is USD-formatted, C is EUR-formatted)
=SUM(B2:B8, C2:C8)         (where B is USD, C is EUR)

When values formatted in different currencies are added, subtracted, or aggregated together, the result is meaningless — you cannot add dollars to euros without a conversion step. Excel performs the arithmetic on the raw numbers and returns a result, but the result has no valid currency interpretation.

This is the only rule at Error severity. Currency mismatches are never intentional in additive contexts.

Diagnostic message:

D008  Sheet1!F10  Adding values from B2:B8 (USD) and C2:C8 (EUR) with conflicting currencies. Convert to a common currency before adding values.

D009 — Mixed Percent

Severity: Warning

Detects addition or subtraction between percent-formatted and non-percent values, which almost always indicates a scale mismatch.

=A1 + B1   (where A1 is 50% formatted, B1 is 10 unformatted)

This gives 10.5, not 60%. The percent format means cell A1 stores 0.5, not 50. When added to 10, the result is 0.5 + 10 = 10.5.

Diagnostic message:

D009  Sheet1!E3  mixed percent-formatted (A1: 0.5) and non-percent (B1: 10) in addition

D023 — Currency / Non-Currency Mix

Severity: Warning

Detects combining currency-formatted values with non-currency semantic formats (percent, date, time, text) in addition, subtraction, and aggregate functions.

=A1 + B1   (where A1 is $100 USD-formatted, B1 is 50% percent-formatted)
=SUM(A1:A5, B1:B5)   (where A is currency, B is date-formatted)

Adding a dollar amount to a percentage or a date is almost always a unit error. Excel performs the arithmetic because the underlying values are numbers, but the result has no meaningful interpretation.

This is distinct from D008 (mixed currencies) which flags conflicting currency codes. D023 flags mixing currency with an entirely different semantic type.

Diagnostic message:

D023  Sheet1!C5  Adding currency-formatted A1 with non-currency semantic values (percent) from B1. Align formats and units before adding values.

D030 — Merged Cell Reference

Severity: Warning

Detects formulas that reference a non-anchor cell in a merged range.

=B3   (where B2:B5 is merged — only B2 holds the value)

In a merged range, only the anchor cell (top-left) holds the value. All other cells in the merge are empty. Referencing B3 in a merged range B2 returns empty/zero, even though the merged cell visually displays a value at that position.

Diagnostic message:

D030  Sheet1!C5  formula references B3 which is a non-anchor cell in merged range B2:B5 (use B2 instead)

D031 — Spell Check

Severity: Info

Detects possible spelling errors in text cells. This is an informational rule — it reports potential typos, not formula bugs.

The engine auto-detects the workbook's language and checks text cells against a dictionary. Supported languages: English, Spanish, French, German, Italian, Portuguese, Dutch, Polish, and Russian.

When a potential misspelling is found, the diagnostic provides up to 3 suggested corrections. Commonly excluded with --skip-rule D031 when you only care about formula bugs.

Diagnostic message:

D031  Sheet1!A1  Possible spelling error: 'Reveune' (suggestions: Revenue, Revue, Revenue)

Severity levels

Output is grouped by severity: errors first, then warnings, then info. The summary line at the end shows the total count broken down by severity level.

Filtering rules

By default, all eleven rules run against the entire workbook. You can narrow what gets checked in two ways: rule selection and range scoping.

Exclude specific rules

Use --skip-rule (short: -s) to exclude rules. Repeatable.

# Skip spell check
witan xlsx lint report.xlsx --skip-rule D031

# Skip spell check and broadcast surprise
witan xlsx lint report.xlsx -s D031 -s D006

Run only specific rules

Use --only-rule to run a subset of rules. Repeatable.

# Only check for double counting and unsorted lookups
witan xlsx lint report.xlsx --only-rule D001 --only-rule D002

Scope to specific ranges

Use --range (short: -r) to lint only specific areas of the workbook. Repeatable. Without this flag, the entire workbook is linted.

# Lint only the summary section
witan xlsx lint report.xlsx -r "Sheet1!A1:Z50"

# Lint two specific areas
witan xlsx lint report.xlsx -r "Sheet1!A1:Z50" -r "Summary!A1:H20"

CLI reference

witan xlsx lint <file> [flags]

Output format

Diagnostics are grouped by severity. Within each group, diagnostics are listed in the order they were found, with the rule ID, cell location, and message on each line.

Error (1):
  D008  Sheet1!F10  Adding values from B2:B8 (USD) and C2:C8 (EUR) with conflicting currencies. Convert to a common currency before adding values.

Warning (2):
  D001  Sheet1!C12  cells B5:B8 contribute with net weight 2.0 via SUM(B1:B10) and SUM(B5:B15)
  D002  Sheet1!D5   VLOOKUP with approximate match mode requires ascending sorted range

Info (1):
  D031  Sheet1!A1   Possible spelling error: 'Reveune' (suggestions: Revenue, Revue, Revenue)

4 issues (1 error, 2 warnings, 1 info)

The summary line at the end always shows the total count and the breakdown by severity, even when some categories are zero.

Clean output

When no issues are found:

0 issues (0 errors, 0 warnings, 0 info)