Evaluate Formula and Error Checking
The two auditing tools that answer why a cell says what it says. Evaluate Formula underlines one part of the formula and replaces it with its value on each click, until the formula is the answer. Error Checking walks every cell that reports an error, with a sentence on what each one means, and every formula that breaks the pattern of the ones above and below it. Both are engine functions too: evaluationSteps() and checkSheet() run with no browser. (requires @svgrid/enterprise)
A live, editable Svelte 5 data grid example from the SvGrid gallery (Spreadsheet). See the SvGrid documentation for the full API.
About this example
Excel's Evaluate Formula and Error Checking in a Svelte 5 spreadsheet. Evaluate Formula underlines one part of the active cell's formula and replaces it with its value on each click, until the formula is the cell's answer. Error Checking walks every cell on the sheet that reports an error, with a sentence on what each error means, and every formula that breaks the pattern of the ones above and below it. Show Calculation Steps takes one straight into the other. Enterprise, in @svgrid/enterprise.
The two auditing tools that answer "why does this cell say that?".
Formulas > Evaluate Formula the active cell's formula with one part underlined. Each click replaces that part with what it is worth, until the whole thing is the cell's answer. Formulas > Error Checking every cell on the sheet that reports an error, and every formula that breaks the pattern of the ones above and below it, walked one at a time with what each error means.
The sheet below is a commission model with three planted faults, which is what a real one looks like a week after two people have edited it: a rate cell that is text, a lookup that finds nothing, and one row in the commission column whose formula is not the column's formula.
Try: click B13 and open Evaluate Formula, then click Evaluate three times to watch the sales figure resolve, the rate come back as text, and the multiplication fail. Open Error Checking and press Next through all five problems, the broken row among them. Show Calculation Steps takes you from one straight into the other.
Imports, features and API used
Imports: @svgrid/enterprise
Frequently asked questions
What does Evaluate Formula show?
The formula with one part underlined, which is the part about to be worked out. Each click replaces that part with its value, so a long formula collapses step by step into the cell's answer and the step where it goes wrong is the step you are looking at. Step Back and Restart walk it again.
What counts as an error worth checking?
Any formula whose value is an error code, and any formula that breaks the pattern of the cells above and below it. The second one is deliberately quiet: a formula is only called the odd one out when its two neighbours are formulas that agree with each other once translated and this one does not, which is the shape of a column filled down and then broken in the middle.
Can I run these checks without the dialog?
Yes. evaluationSteps() returns the walk as data, given a formula and something to evaluate a sub-formula with, and checkSheet() returns the findings for a sheet. Both are exported from @svgrid/enterprise, so a save-time check or a report can use them with no browser.
Related documentation
Related articles
- A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
- Empty, Loading, and Error States for a Svelte Data Grid - How to handle the three non-data states in a Svelte grid - empty (with two subtypes), skeleton loading that does not flash, and recoverable errors that keep existing rows visible.
- Pivot Tables in Svelte - Summarize Data Without a Spreadsheet - Run a cross-tab pivot directly inside your Svelte app using @svgrid/enterprise createPivotModel - no Excel, no server-side aggregation, no stale exports.
Source code (493-sheet-auditing.svelte)
<script lang="ts">
/**
* 483. Evaluate Formula and Error Checking
* ----------------------------------------
* The two auditing tools that answer "why does this cell say that?".
*
* Formulas > Evaluate Formula the active cell's formula with one part
* underlined. Each click replaces that
* part with what it is worth, until the
* whole thing is the cell's answer.
* Formulas > Error Checking every cell on the sheet that reports an
* error, and every formula that breaks
* the pattern of the ones above and below
* it, walked one at a time with what each
* error means.
*
* The sheet below is a commission model with three planted faults, which
* is what a real one looks like a week after two people have edited it:
* a rate cell that is text, a lookup that finds nothing, and one row in
* the commission column whose formula is not the column's formula.
*
* Try: click B13 and open Evaluate Formula, then click Evaluate three
* times to watch the sales figure resolve, the rate come back as text,
* and the multiplication fail. Open Error Checking and press Next through
* all five problems, the broken row among them. Show Calculation Steps
* takes you from one straight into the other.
*/
import { SvSheet, createWorkbook, createSheetDocument } from '@svgrid/enterprise'
const rows: string[][] = [
['Rep', 'Region', 'Sales', 'Rate', 'Commission', '', 'Rate card', ''],
['Ada', 'North', '184000', '=VLOOKUP(B2, $G$2:$H$5, 2, FALSE)', '=C2*D2', '', 'North', '0.04'],
['Brin', 'South', '97400', '=VLOOKUP(B3, $G$2:$H$5, 2, FALSE)', '=C3*D3', '', 'South', '0.035'],
['Cyd', 'EMEA', '212500', '=VLOOKUP(B4, $G$2:$H$5, 2, FALSE)', '=C4*D4', '', 'EMEA', '0.045'],
// The odd one out: this row multiplies by the rate above it, not its own.
['Dov', 'North', '150800', '=VLOOKUP(B5, $G$2:$H$5, 2, FALSE)', '=C5*D4', '', 'APAC', 'see below'],
['Eze', 'South', '88300', '=VLOOKUP(B6, $G$2:$H$5, 2, FALSE)', '=C6*D6', '', '', ''],
// A region that is not on the rate card: the lookup finds nothing.
['Fen', 'LATAM', '64900', '=VLOOKUP(B7, $G$2:$H$5, 2, FALSE)', '=C7*D7', '', '', ''],
['', '', '', '', '', '', '', ''],
['Total sales', '=SUM(C2:C7)', '', '', '', '', '', ''],
['Total commission', '=SUM(E2:E7)', '', '', '', '', '', ''],
['', '', '', '', '', '', '', ''],
['A rate that is text, not a number', '', '', '', '', '', '', ''],
['APAC commission', '=C2*H5', '', '', '', '', '', ''],
]
const wb = createWorkbook([{ name: 'Commission', cells: rows }])
const doc = createSheetDocument({ workbook: wb })
const sheet = doc.get('Commission')
const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
sheet.formats.set([[0, 0, 0, 7]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
sheet.formats.set([[11, 0, 11, 4]], { bold: true, fill: '#f1f5f9', color: '#0f172a' }, at)
sheet.formats.set([[1, 2, 6, 2]], { numFmt: '$#,##0' }, at)
sheet.formats.set([[1, 4, 6, 4]], { numFmt: '$#,##0' }, at)
sheet.formats.set([[1, 3, 6, 3]], { numFmt: '0.0%' }, at)
sheet.formats.set([[8, 1, 9, 1]], { numFmt: '$#,##0', bold: true }, at)
sheet.formats.set([[1, 7, 4, 7]], { numFmt: '0.0%' }, at)
sheet.widths.A = 180
sheet.widths.B = 90
sheet.widths.G = 100
sheet.widths.H = 90
sheet.freeze = { rows: 1, cols: 1 }
</script>
<SvSheet document={doc} height="100%" rows={18} columns={10} />More Spreadsheet examples
- Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
- Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
- Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
- Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
- HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).