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
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:A10 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:A10 contribute with weight 2.0, even though the overlapping ranges are hidden behind intermediate formulas.
Scalar multiplication (like =A1*3) is not flagged — only cases where multiple distinct expression paths contribute to the same cells.
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.
This is one of the most common spreadsheet bugs. The formula returns a value. No error. The value is simply wrong.
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 sortedD003 — 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.
The rule checks single-cell references in function arguments (where the function signature indicates coercion), arithmetic operators, unary operators, and comparison operators.
Diagnostic message:
D003 Sheet1!C5 Right operand (B1) of A1+B1 references an empty cell that will be coercedD005 — 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. The total, average, or other aggregate is computed over fewer values than the range suggests.
This is particularly dangerous when text values are data-entry errors or placeholder strings that should have been numbers. The formula returns a clean number. It is simply missing a row.
Diagnostic message:
D005 Sheet1!B11 SUM range A1:A10 contains non-numeric value "N/A" at A7 which will be silently ignoredD006 — Broadcast Surprise
Severity: Warning
Detects scalar-to-range broadcasting in arithmetic and comparison operations that is likely unintentional.
=A1 + B1:B10
=C5 * D1:D20When 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. =A1 + B1:B10 adds the value of A1 to each of the 10 cells in B1:B10 and returns a 10-element array.
This is often unintentional — the author meant to reference a matching-size range (e.g., A1:A10 + B1:B10) and accidentally wrote a single-cell reference on one side. The formula still computes, but the result is not what was intended.
Diagnostic message:
D006 Sheet1!E1 scalar A1 broadcast across range B1:B10 in additionD007 — 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.
The engine checks the actual cell values in the lookup column or row for duplicates. When duplicates are found, it reports which values are duplicated.
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. The engine inspects the number formats of all referenced cells and flags any operation that combines values with different currency codes.
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.
This is a common mistake when building models that mix rates and absolute values. The formula author sees "50%" in cell A1 and "10" in cell B1 and expects the sum to be 60. The actual result is off by orders of magnitude, but it is still a valid number — no error, no warning from Excel.
The rule checks the number formats of referenced cells in addition and subtraction operations and flags mismatches between percent and non-percent formatting.
Diagnostic message:
D009 Sheet1!E3 mixed percent-formatted (A1: 0.5) and non-percent (B1: 10) in additionD023 — 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 — $100 plus 50% gives 100.5 (since 50% is stored as 0.5), not $150.
This is distinct from D008 (mixed currencies) which flags conflicting currency codes. D023 flags mixing currency with an entirely different semantic type. The engine checks the number formats of referenced cells and identifies the specific non-currency types involved.
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:B5 returns empty/zero, even though the merged cell visually displays a value at that position.
The diagnostic reports the correct anchor cell to reference instead, so the fix is straightforward.
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.
Cell A1: "Reveune Summary"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. Because this is informational and not a formula correctness issue, it uses Info severity and is 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
| Severity | Meaning | Rules |
|---|---|---|
| Error | Must fix. The formula is structurally broken or will produce clearly wrong results. | D008 |
| Warning | Should review. The formula pattern is known to produce silent bugs. Most rules use this level. | D001, D002, D003, D005, D006, D007, D009, D023, D030 |
| Info | Informational. Worth knowing, but not a formula correctness issue. | D031 |
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 D006Run 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 D002Scope 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]| Flag | Short | Default | Description |
|---|---|---|---|
<file> |
— | — | Path to the .xlsx, .xls, or .xlsm file (positional, required) |
--range |
-r |
Entire workbook | Range(s) to lint (repeatable, e.g. "Sheet1!A1:Z50") |
--skip-rule |
-s |
— | Rule IDs to exclude (repeatable, e.g. D031) |
--only-rule |
— | — | Only run these rule IDs (repeatable, e.g. D001) |
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, but the lookup range is not sorted
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, the output is a single line:
0 issues (0 errors, 0 warnings, 0 info)