Format as Table and structured references

About this example

Excel's Ctrl+T in a Svelte 5 spreadsheet: a block becomes a table with a header row, the banded look, filter arrows and a name its columns are read by, so a formula says Orders[Amount] instead of E2:E13 and keeps meaning it as rows are added. [@Qty] is this row's cell, which is how each line works out its own amount. Auto-expand is the point: typing under the last row grows the table, and every total that reads it grows too, with no formula re-pointed. Insert > Table Styles picks the look from eighteen presets, six colours in three tones, under the names Excel stores them by. Tables ride in getState() and go into the .xlsx as real table parts, style included, so Excel shows a table there too. Enterprise, in @svgrid/enterprise.

Excel's Ctrl+T. A table names its columns, so a formula says Orders[Amount] instead of E2:E13 and keeps meaning it as rows are added.

Insert > Table (Ctrl+T) the block becomes a table: a header row, the banded look, the filter arrows, and a name its columns are read by. Orders[Amount] a whole column, whatever the table is now. [@Qty] this row's cell, which is how the Amount column works out its own line. Orders[#Totals] the totals row under it. Insert > Table Styles the look: six colours in three tones, by the names Excel stores them under. Insert > To Range the cells stay, the table goes.

Auto-expand is the point: type a product under the last row and the table grows, so every total that reads it grows too, with no formula re-pointed. The summary on the right is four structured references and never mentions an address.

Try: type a new order in row 14 and watch the summary follow. Put the cursor in the table and press Ctrl+T to rename it or pick another style from the gallery. Save As and open the file in Excel: the table is a table there too, wearing the style it wears here.

Imports, features and API used

Imports: @svgrid/enterprise

Frequently asked questions

What does a table buy over a range?

A structured reference names the column rather than the cells, so the range it resolves to is whatever the table currently is. Add a row and every formula reading Orders[Amount] covers it, with nothing re-pointed. A1 references are positional, which is why a total under a plain block silently stops covering the row you just typed.

What is [@Qty]?

This row's cell in the Qty column, which only means something inside the table. It is how the Amount column works out its own line, =[@Qty]*[@Price], and why the formula is the same in every row.

Does the table survive a save?

Yes. It rides in getState() with the workbook, and it goes into the .xlsx as a real table part with its columns and its style, so Excel opens it as a table rather than as cells that look like one. A file with a table in it reads back the same way.

Where does the style live?

On the table, as the name Excel knows it by, such as TableStyleMedium2. The look is drawn rather than written into the cells, so a row typed under the last one arrives already banded and no format has to be cleaned up when the table becomes a range again. Picking None keeps the cells exactly as they are.

Related documentation

• Tables and structured references

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.
• A Date-Picker Cell Editor in SvGrid - How to make date columns editable with a real date picker in SvGrid - including display formatting, time zone handling, and validation that preserves user input.
• CSV vs XLSX vs TSV - Which Export Format? - A practical breakdown of CSV, XLSX, and TSV for data grid exports - when each format earns its keep, what each one silently loses, and how to wire all three in SvGrid.

Source code (491-sheet-tables.svelte)

<script lang="ts">
  /**
   * 481. Format as Table, and structured references
   * -----------------------------------------------
   * Excel's Ctrl+T. A table names its columns, so a formula says
   * `Orders[Amount]` instead of `E2:E13` and keeps meaning it as rows are
   * added.
   *
   *   Insert > Table (Ctrl+T)   the block becomes a table: a header row,
   *                             the banded look, the filter arrows, and a
   *                             name its columns are read by.
   *   Orders[Amount]            a whole column, whatever the table is now.
   *   [@Qty]                    this row's cell, which is how the Amount
   *                             column works out its own line.
   *   Orders[#Totals]           the totals row under it.
   *   Insert > Table Styles     the look: six colours in three tones, by
   *                             the names Excel stores them under.
   *   Insert > To Range         the cells stay, the table goes.
   *
   * Auto-expand is the point: type a product under the last row and the
   * table grows, so every total that reads it grows too, with no formula
   * re-pointed. The summary on the right is four structured references and
   * never mentions an address.
   *
   * Try: type a new order in row 14 and watch the summary follow. Put the
   * cursor in the table and press Ctrl+T to rename it or pick another style
   * from the gallery. Save As and open the file in Excel: the table is a
   * table there too, wearing the style it wears here.
   */
  import { SvSheet, createWorkbook, createSheetDocument } from '@svgrid/enterprise'

  const orders = [
    ['North', 'Licence', '2', '1200'],
    ['South', 'Support', '5', '480'],
    ['EMEA', 'Licence', '1', '1200'],
    ['North', 'Training', '3', '950'],
    ['South', 'Licence', '4', '1200'],
    ['EMEA', 'Support', '9', '480'],
    ['North', 'Hosting', '12', '260'],
    ['South', 'Training', '2', '950'],
    ['EMEA', 'Hosting', '6', '260'],
    ['North', 'Licence', '3', '1200'],
    ['South', 'Hosting', '8', '260'],
    ['EMEA', 'Training', '1', '950'],
  ]

  const rows: string[][] = [
    ['Region', 'Product', 'Qty', 'Price', 'Amount', '', 'Reads the table, not the cells', ''],
    ...orders.map((o, i) => [
      ...o,
      '=[@Qty]*[@Price]',
      '',
      ...(i === 0 ? ['Orders', '=COUNTA(Orders[Region])'] : []),
      ...(i === 1 ? ['Total amount', '=SUM(Orders[Amount])'] : []),
      ...(i === 2 ? ['Biggest order', '=MAX(Orders[Amount])'] : []),
      ...(i === 3 ? ['Licence revenue', '=SUMIF(Orders[Product], "Licence", Orders[Amount])'] : []),
    ]),
  ]
  // A workbook takes a dense block.
  const width = rows.reduce((w, row) => Math.max(w, row.length), 0)
  for (const row of rows) for (let c = 0; c < width; c += 1) row[c] ??= ''

  const wb = createWorkbook([{ name: 'Sales', cells: rows }])
  // The table this sheet opens with. Insert > Table makes one the same way.
  wb.tables.define({
    name: 'Orders',
    sheet: 'Sales',
    headerRow: 0,
    firstCol: 0,
    lastCol: 4,
    lastRow: orders.length,
    hasTotals: false,
    // The gallery's names are Excel's own, so this is the style Excel
    // opens the saved file with too.
    style: 'TableStyleMedium6',
  })
  wb.recalculate()

  const doc = createSheetDocument({ workbook: wb })
  const sheet = doc.get('Sales')
  const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
  sheet.formats.set([[0, 6, 0, 6]], { bold: true, color: '#0f172a' }, at)
  sheet.formats.set([[1, 6, 4, 6]], { color: '#475569' }, at)
  // Past the last row too, so a row typed into the table looks right the
  // moment it is typed.
  sheet.formats.set([[1, 3, orders.length + 5, 4]], { numFmt: '$#,##0' }, at)
  sheet.formats.set([[2, 7, 4, 7]], { numFmt: '$#,##0', bold: true }, at)
  sheet.widths.A = 90
  sheet.widths.B = 100
  sheet.widths.G = 190
  sheet.widths.H = 110
  sheet.autoFilter = { range: [0, 0, orders.length, 4], filters: {} }
  sheet.freeze = { rows: 1, cols: 0 }
</script>

<SvSheet document={doc} height="100%" rows={20} columns={10} />

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.
• 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).