Format as Table and structured references

Excel's Ctrl+T: 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. 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 named the way Excel names them. Tables ride in getState() and go into the .xlsx as real table parts, style included. (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 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

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">
  /**
   * 491. 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.
  • 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.
  • Excel keyboard shortcuts - The muscle memory a spreadsheet user arrives with: Ctrl+Arrow jumps to the edge of the data region (a run-boundary search, so it hops gaps rather than running to the end), Ctrl+Shift+Arrow extends there, Ctrl+A takes the current region then the sheet, Ctrl+Space / Shift+Space take the column or row, Ctrl+D / Ctrl+R fill, Ctrl+; stamps the date. One enableSheet() call. Ctrl+Z undoes a whole fill in one press, not one per cell.
  • Formula bar + cell formats - The Excel cell experience: a formula bar showing the RAW text behind the active cell (the grid shows 1,234.50, the bar shows =B2*C2) with function autocomplete and signature hints, a Name Box that jumps to an address, and number formats that live on the CELL rather than the column. Ctrl+Shift+4 for currency, Ctrl+B to bold. The format store keys on row id, so sorting does not leave formatting behind on the old index.