Spreadsheet + formulas

Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection. (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

A real formula engine inside the Svelte 5 data grid. Cells hold a literal or a formula starting with =, with A1 and $C$3 references, A1:A10 ranges, arithmetic, comparison and string concatenation, and SUM, AVG, MIN, MAX, COUNT, COUNTA, COUNTIF, IF, AND, OR, NOT, ROUND, ABS, LEN, LEFT, RIGHT, UPPER, LOWER, CONCAT and TODAY. The sheet recomputes on every change, reports #CYCLE!, #REF! and #VALUE!, and rows resize from the gutter with rowResize, all without bundling a spreadsheet library.

The formula engine, with the ribbon turned off so nothing competes with it. Same component as demo 27:

What to look at:

The fx bar shows the RAW text. Click E2 and the grid reads 56,600 while the bar reads =SUM(B2:D2). That split is the whole reason a spreadsheet has a formula bar, and it is why editing in the bar edits the formula rather than its result.

Type = in the bar and function names complete, shortest match first, with a signature hint while you fill the arguments in.

Recalculation is by dependency, not by sweeping the sheet: change B2 and only the cells that actually read it are recomputed.

Absolute references hold. E8 is =E5/$E$5 filled down - the numerator moves with the row, the divisor does not. Select E8, press Ctrl+D over the rows below it and watch which half moves.

The engine, the parser, the ~50 functions and the dependency graph all live in @svgrid/enterprise/sheet. This demo used to inline about 350 lines of them, and so did two other demos.

Imports, features and API used

Imports: @svgrid/enterprise

Frequently asked questions

How does the grid know a cell is a formula?

The raw text starts with =. The engine parses it, resolves references against the other cells, evaluates and returns the display value; cells without = are literals. A cell snippet shows the computed value while the raw text stays editable.

How are circular references handled?

The evaluator tracks the cells on the current evaluation path; a reference back into that path stops evaluation and the cell shows #CYCLE! instead of hanging.

When would I use a full spreadsheet engine instead?

When you need hundreds of functions, named ranges or array formulas. The HyperFormula demos show that path; this engine covers budgets, scorecards and lightweight planning with no extra dependency.

Source code (83-spreadsheet-formulas.svelte)

<script lang="ts">
  /**
   * 83. Spreadsheet formulas
   * -------------------------
   * The formula engine, with the ribbon turned off so nothing competes with
   * it. Same component as demo 27:
   *
   *   <SvSheet workbook={wb} showRibbon={false} />
   *
   * What to look at:
   *
   *   The fx bar shows the RAW text. Click E2 and the grid reads 56,600
   *   while the bar reads =SUM(B2:D2). That split is the whole reason a
   *   spreadsheet has a formula bar, and it is why editing in the bar edits
   *   the formula rather than its result.
   *
   *   Type = in the bar and function names complete, shortest match first,
   *   with a signature hint while you fill the arguments in.
   *
   *   Recalculation is by dependency, not by sweeping the sheet: change B2
   *   and only the cells that actually read it are recomputed.
   *
   *   Absolute references hold. E8 is =E5/$E$5 filled down - the numerator
   *   moves with the row, the divisor does not. Select E8, press Ctrl+D over
   *   the rows below it and watch which half moves.
   *
   * The engine, the parser, the ~50 functions and the dependency graph all
   * live in @svgrid/enterprise/sheet. This demo used to inline about 350
   * lines of them, and so did two other demos.
   */
  import { SvSheet, createWorkbook } from '@svgrid/enterprise'

  const wb = createWorkbook([
    {
      name: 'Formulas',
      cells: [
        ['Region', 'Q1', 'Q2', 'Q3', 'Year'],
        ['North', '18200', '18900', '19500', '=SUM(B2:D2)'],
        ['South', '12400', '13100', '11800', '=SUM(B3:D3)'],
        ['East', '9100', '9800', '10450', '=SUM(B4:D4)'],
        ['West', '15300', '14200', '16900', '=SUM(B5:D5)'],
        ['Total', '=SUM(B2:B5)', '=SUM(C2:C5)', '=SUM(D2:D5)', '=SUM(E2:E5)'],
        [],
        ['Best region', '=INDEX(A2:A5,MATCH(MAX(E2:E5),E2:E5,0))'],
        ['Share of best', '=MAX(E2:E5)/$E$6'],
        ['Average region', '=AVERAGE(E2:E5)'],
        ['Regions over 40k', '=COUNTIF(E2:E5,">40000")'],
        ['North vs South', '=IF(E2>E3,"North","South")'],
        ['Lookup East', '=VLOOKUP("East",A2:E5,5,FALSE)'],
        ['Rounded total', '=ROUND(E6/1000,1)'],
        ['Label', '=TEXTJOIN(" / ",TRUE,A2,A3,A4,A5)'],
      ],
    },
  ])

  const MONEY = { numFmt: '#,##0' } as const
  const money = (row: number) =>
    Object.fromEntries(['B', 'C', 'D', 'E'].map((c) => [`${c}${row}`, MONEY]))
</script>

<SvSheet
  workbook={wb}
  showRibbon={false}
  showTabs={false}
  height="100%"
  rows={20}
  columns={7}
  columnWidths={{ A: 160, B: 150 }}
  formats={{
    A1: { bold: true }, B1: { bold: true }, C1: { bold: true },
    D1: { bold: true }, E1: { bold: true },
    ...money(2), ...money(3), ...money(4), ...money(5),
    A6: { bold: true }, B6: { bold: true, numFmt: '#,##0' },
    C6: { bold: true, numFmt: '#,##0' }, D6: { bold: true, numFmt: '#,##0' },
    E6: { bold: true, numFmt: '#,##0' },
    B9: { numFmt: '0.0%' },
    B10: { numFmt: '#,##0' },
  }}
/>

View this example on GitHub

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.
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).
Blank sheet - just type - An empty Excel-style sheet on a plain <SvGrid>: column-letter headers (A..Z), a built-in 1..N row gutter, a name box + formula bar with a browsable function picker, gridlines, range selection and a fill handle. A real HyperFormula engine underneath: type a literal or a formula like =SUM(B2:D2) / =IF(...) and every dependent cell recalculates live. Drag a row or column border to resize; right-click for Cut / Copy / Paste / Clear.