Witan Lint

Witan Lint runs semantic checks for spreadsheet issues that can survive recalculation: formulas that calculate successfully but are likely incorrect, unit and format mismatches, invalid data validation inputs, clipped display values, and overlapping objects.

Use it from exec when linting is part of a larger workbook script:

witan xlsx exec budget.xlsx --code '
  const lint = await xlsx.lint(wb, { rangeAddresses: ["Sheet1!A1:Z50"] })
  return lint.diagnostics
'

Use the standalone command for file-level checks, CI, or a quick terminal report:

$ witan xlsx lint budget.xlsx

Error (2):
  D004  Sheet1!F10  Formula result is #DIV/0!: The formula or function divides by zero.
  D043  Inputs!B5   Cell value violates Decimal data validation rule.

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
  D032  Sheet1!E3   Text display is clipped: "Quarterly revenue forecast" needs 142px but only 64px is available. Widen the column or clear the blocking cell.

5 issues (2 errors, 3 warnings, 0 info)

Why lint?

Recalculation catches formula evaluation errors. When a formula produces #REF!, #DIV/0!, or #N/A, those errors are visible in the computed values. witan xlsx lint also reports cached Excel error values through D004.

Linting also catches bugs where the workbook still produces values but the result is misleading or not visible to the reader.

• 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.
• A value can violate an Excel data validation rule while still being stored normally in the workbook.
• A value can be present in the workbook but hidden by a narrow column, short row, or floating object.

These problems are hard to catch by reading cell values alone. The values can look plausible, violate workbook constraints, or exist in the file but be clipped or covered in the visible spreadsheet. Linting analyzes formula structure, referenced data, cell formatting, workbook constraints, and workbook layout.

The linting engine

The public CLI and lint engine expose sixteen rules. Most rules examine formula structure and referenced data; validation rules inspect workbook constraints; layout rules also inspect cell rendering metadata and floating objects.

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

D004 — Calculation Error

Severity: Error

Detects cells whose cached value is an Excel calculation error.

=A1 / B1   (where B1 is 0)
=VLOOKUP(A1, B:C, 2, FALSE)   (where A1 is not found)

This rule reports error values such as #DIV/0!, #REF!, #VALUE!, #NAME?, and #N/A. It applies to formulas and to cells that directly contain an error value.

Diagnostic message:

D004  Sheet1!F10  Formula result is #DIV/0!: The formula or function divides by zero.

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 reported at Error severity because currency mismatches are not meaningful 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)

D032 — Column Width Clipping

Severity: Warning

Detects visible cell values that do not fit in their column. This includes text that is clipped and number or date displays that Excel renders as hashes.

#####        (a date or number too wide for the column)
Revenue for the quarter   (text cut off by the next occupied cell)

The rule estimates rendered text width from workbook font, column width, merged cells, alignment, shrink-to-fit settings, and blocking cells. It skips hidden rows and columns, empty display values, rotated text, and values hidden by data bars.

Diagnostic message:

D032  Sheet1!E3  Text display is clipped: "Quarterly revenue forecast" needs 142px but only 64px is available. Widen the column or clear the blocking cell.

D034 — Row Height Clipping

Severity: Warning

Detects visible cell text that does not fit vertically in the row or merged range.

A wrapped paragraph in a row with fixed height

The rule estimates the rendered text block height from row height, column width, merged ranges, wrapping, alignment, shrink-to-fit settings, workbook fonts, and rich text runs. It skips hidden rows and columns, empty display values, rotated text, and values hidden by data bars.

Diagnostic message:

D034  Sheet1!A12  Cell text is clipped vertically: "Quarterly commentary..." needs 45.0pt but only 15.0pt is available. Increase or autofit the row height.

D041 — Object Covers Cell Content

Severity: Warning

Detects floating objects such as charts, images, and shapes that cover visible cell content.

An image placed over A1:C4 where A2 contains a visible label

The rule uses object bounds and worksheet geometry to find visible objects that overlap cells with display text. It reports cells where the object covers a substantial portion of the cell interior. To keep output bounded, each object reports at most 10 covered cells.

D041 is a workbook-level rule. It runs when linting the whole workbook and does not run when --range scopes linting to specific cell ranges.

Diagnostic message:

D041  Sheet1!A2  Picture "Logo" covers most of cell A2: "Revenue". Move the object or clear the cell.

D042 — Object Covers Object

Severity: Warning

Detects floating objects that substantially overlap other floating objects on the same worksheet.

A shape placed over most of a chart

Objects with higher z-order are treated as being in front. The rule reports overlaps where the front object covers at least half of the object behind it.

D042 is a workbook-level rule. It runs when linting the whole workbook and does not run when --range scopes linting to specific cell ranges.

Diagnostic message:

D042  Sheet1!D5  Shape "Callout" covers most of Chart "Sales". Move one of the objects.

D043 — Data Validation Issue

Severity: Error

Detects cells whose current value or formula result violates an applicable Excel data validation rule.

Inputs!B5 contains -10, with a decimal validation rule requiring values greater than or equal to 0

The rule evaluates whole number, decimal, list, date, time, text length, and custom validation criteria against cells in the lint scope. Blank handling follows the validation rule's ignoreBlanks setting. When a validation rule definition itself cannot be evaluated, lint reports one diagnostic for that rule instead of repeating the same rule-level issue for every cell it covers.

Diagnostic message:

D043  Inputs!B5  Cell value violates Decimal data validation rule.

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 16 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 column width clipping
witan xlsx lint report.xlsx --skip-rule D032

# Skip column width clipping and broadcast surprise
witan xlsx lint report.xlsx -s D032 -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.

Range scoping applies to cell-level rules, including D043 data validation checks. Workbook-level object checks D041 and D042 only run for whole-workbook linting, because they reason about floating objects across the sheet rather than a single cell range.

# 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

3 issues (1 error, 2 warnings, 0 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)