# 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.

Requires `@svgrid/enterprise` (and `@svgrid/grid`). Category: Spreadsheet. Live demo: https://svgrid.com/demos/453-formula-bar-formats/

Install: `npm i @svgrid/grid @svgrid/enterprise` (Svelte 5).

## About this example

A formula bar and per-cell number formats on a Svelte 5 spreadsheet grid: the bar shows the raw text behind the active cell while the cell shows the formatted result, with function autocomplete and signature hints as you type a formula, a Name Box that jumps to an address, and formats that live on the cell rather than the column. Ctrl+Shift+4 applies currency, Ctrl+B bolds. The format store keys on row id, so sorting keeps a format with its cell. Enterprise, in @svgrid/enterprise.

The Excel "cell" experience on a plain <SvGrid>: a formula bar showing the RAW text behind the active cell, a Name Box that jumps to an address, and number formats that live on the CELL rather than the column.

```text
Formula bar   Click any cell. The grid shows the computed value; the bar
              shows what was typed. Start a formula with = and function
              names autocomplete (shortest match first), with a
              signature hint while you fill in arguments.
Name Box      Type C7 and press Enter to jump there. The dropdown holds
              defined names; picking one selects what it refers to.
Formats       Select cells and press Ctrl+Shift+4 for currency, 5 for
              percent, 1 for a thousands number. Ctrl+B / I / U style
              them. Only the DISPLAY changes; the stored value and every
              formula reading it are untouched.
```

The format store keys on row id, not row index, so sorting the grid does not leave the formatting behind on whatever row took that position.

## Imports, features and API used

- Imports: `@svgrid/grid`, `@svgrid/enterprise`
- Table features registered: `rowSortingFeature`

## Frequently asked questions

### Why does the cell show one thing and the bar another?

The cell shows the displayed value, 1,234.50 with its number format applied; the bar shows what was typed, the formula =B2*C2, which is what you edit.

### How do formats survive a sort?

The format store keys entries on the row's id, not its index, so when the rows reorder each format moves with its row instead of staying on the old position.

### Which shortcuts apply formats?

Ctrl+Shift+4 for currency, Ctrl+Shift+5 for percent, Ctrl+Shift+1 for a thousands number, Ctrl+Shift+~ for General, and Ctrl+B, Ctrl+I and Ctrl+U for bold, italic and underline.

## Related documentation

- [Number formats and cell styles](https://svgrid.com/docs/help/cells/number-formats.md)

## Related articles

- [A Fill Handle (Drag to Fill) in SvGrid](https://svgrid.com/blog/fill-handle-drag-to-fill/) - 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.
- [Spreadsheet-Style Cell Range Selection in SvGrid](https://svgrid.com/blog/cell-range-selection/) - How to enable drag-to-select cell ranges, read live selection state, and build a status-bar footer that sums and averages the selected values.
- [Status Badge Cells in SvGrid](https://svgrid.com/blog/status-badge-cells/) - Turn a plain text status column into colored, accessible pill badges without breaking sort, filter, or virtualization - using SvGrid's snippet cell renderer.

## Source code (453-formula-bar-formats.svelte)

```svelte
<script lang="ts">
  /**
   * 453. Formula bar, Name Box and per-cell number formats
   * ------------------------------------------------------
   * The Excel "cell" experience on a plain <SvGrid>: a formula bar showing the
   * RAW text behind the active cell, a Name Box that jumps to an address, and
   * number formats that live on the CELL rather than the column.
   *
   *   Formula bar   Click any cell. The grid shows the computed value; the bar
   *                 shows what was typed. Start a formula with = and function
   *                 names autocomplete (shortest match first), with a
   *                 signature hint while you fill in arguments.
   *   Name Box      Type C7 and press Enter to jump there. The dropdown holds
   *                 defined names; picking one selects what it refers to.
   *   Formats       Select cells and press Ctrl+Shift+4 for currency, 5 for
   *                 percent, 1 for a thousands number. Ctrl+B / I / U style
   *                 them. Only the DISPLAY changes; the stored value and every
   *                 formula reading it are untouched.
   *
   * The format store keys on row id, not row index, so sorting the grid does
   * not leave the formatting behind on whatever row took that position.
   */
  import {
    SvGrid,
    tableFeatures,
    rowSortingFeature,
    renderSnippet,
    type GridColumns,
  } from '@svgrid/grid'
  import {
    enableSheet,
    SvFormulaBar,
    createFormatStore,
    createNames,
    setFormatTarget,
    compileNumberFormat,
    entryToStyle,
    parseFormula,
    evaluateFormula,
    formatCellValue,
    withCustomFunctions,
    type SheetCellValue,
  } from '@svgrid/enterprise'

  enableSheet()

  type Row = {
    id: string
    item: string
    units: string
    price: string
    revenue: string
    share: string
  }

  const FIELDS = ['item', 'units', 'price', 'revenue', 'share'] as const

  // Column E is share-of-total, and it is written with the total PINNED as
  // $D$5. That is the point of absolute refs: fill E1 down and the D moves
  // with the row while the divisor stays put.
  const start = (): Row[] => [
    { id: 'r1', item: 'Widget',  units: '1200', price: '24.5',  revenue: '=B1*C1', share: '=D1/$D$5' },
    { id: 'r2', item: 'Gadget',  units: '860',  price: '41',    revenue: '=B2*C2', share: '=D2/$D$5' },
    { id: 'r3', item: 'Doohick', units: '2400', price: '8.75',  revenue: '=B3*C3', share: '=D3/$D$5' },
    { id: 'r4', item: 'Gizmo',   units: '540',  price: '112.4', revenue: '=B4*C4', share: '=D4/$D$5' },
    { id: 'r5', item: 'TOTAL',   units: '=SUM(B1:B4)', price: '', revenue: '=SUM(D1:D4)', share: '' },
  ]

  let rows = $state<Row[]>(start())
  let active = $state<{ rowIndex: number; colIndex: number } | null>({ rowIndex: 0, colIndex: 3 })
  let formatVersion = $state(0)

  const store = createFormatStore()
  const names = createNames({ Target: '=$B$5' })

  // Seed a couple of formats so the demo opens looking like a real sheet.
  const lookup = {
    rowIdAt: (i: number) => rows[i]?.id ?? null,
    columnIdAt: (i: number) => FIELDS[i] ?? null,
  }
  store.set([[0, 2, 4, 3]], { numFmt: '$#,##0.00' }, lookup)
  store.set([[0, 4, 4, 4]], { numFmt: '0.0%' }, lookup)
  store.set([[4, 0, 4, 4]], { bold: true }, lookup)

  setFormatTarget({ store, lookup, onChange: () => (formatVersion += 1) })

  // ---- the engine -----------------------------------------------------
  const functions = withCustomFunctions(undefined)

  function raw(rowIndex: number, colIndex: number): string {
    const field = FIELDS[colIndex]
    return field ? (rows[rowIndex]?.[field] ?? '') : ''
  }

  const ctx = {
    resolve: (_sheet: string | null, r: number, c: number): SheetCellValue => {
      if (r < 0 || r >= rows.length || c < 0 || c >= FIELDS.length) return { error: '#REF!' }
      return compute(r, c)
    },
    lastRow: () => rows.length - 1,
    resolveName: (name: string): SheetCellValue | undefined => {
      const node = names.resolve(name)
      return node ? evaluateFormula(node, ctx) : undefined
    },
    functions,
  }

  function compute(r: number, c: number): SheetCellValue {
    const text = raw(r, c).trim()
    if (text === '') return ''
    if (!text.startsWith('=')) {
      const n = Number(text)
      return Number.isFinite(n) ? n : text
    }
    try {
      return evaluateFormula(parseFormula(text), ctx)
    } catch {
      return { error: '#PARSE!' }
    }
  }

  function display(r: number, c: number): { text: string; color?: string } {
    void formatVersion
    const value = compute(r, c)
    const rowId = rows[r]?.id
    const columnId = FIELDS[c]
    const entry = rowId && columnId ? store.get(rowId, columnId) : undefined
    if (typeof value === 'object' && value !== null && 'error' in value) {
      return { text: value.error, color: '#dc2626' }
    }
    if (!entry?.numFmt) return { text: formatCellValue(value) }
    return compileNumberFormat(entry.numFmt).format(value)
  }

  const activeRaw = $derived(active ? raw(active.rowIndex, active.colIndex) : '')

  function commit(text: string, cell: { rowIndex: number; colIndex: number }) {
    const field = FIELDS[cell.colIndex]
    if (!field) return
    rows = rows.map((row, i) => (i === cell.rowIndex ? { ...row, [field]: text } : row))
  }

  const features = tableFeatures({ rowSortingFeature })

  const columns = $derived<GridColumns<Row>>(
    FIELDS.map((field, c) => ({
      id: field,
      field,
      header: `${String.fromCharCode(65 + c)}  ${field}`,
      width: c === 0 ? 140 : 130,
      editable: false,
      cell: (cellCtx: { row: { index: number } }) =>
        renderSnippet(Cell, { r: cellCtx.row.index, c }),
    })),
  )
</script>

{#snippet Cell(props: { r: number; c: number })}
  {@const shown = display(props.r, props.c)}
  {@const rowId = rows[props.r]?.id}
  {@const columnId = FIELDS[props.c]}
  {@const entry = rowId && columnId ? store.get(rowId, columnId) : undefined}
  {@const isActive = active?.rowIndex === props.r && active?.colIndex === props.c}
  <button
    type="button"
    class="cell"
    class:active={isActive}
    style={`${entryToStyle(entry)};${shown.color ? `color:${shown.color}` : ''}`}
    onclick={() => (active = { rowIndex: props.r, colIndex: props.c })}
    title={raw(props.r, props.c)}
  >{shown.text}</button>
{/snippet}

<section class="wrap">
  <SvFormulaBar
    {active}
    value={activeRaw}
    onCommit={commit}
    onNavigate={(cell) => (active = cell)}
    names={names.list()}
    onSelectName={(name) => {
      const node = names.resolve(name)
      if (node && node.k === 'ref' && node.ref.row !== null) {
        active = { rowIndex: node.ref.row, colIndex: node.ref.col }
      }
    }}
  />

  <SvGrid
    data={rows}
    {columns}
    {features}
    selectionMode="cell"
    enableCellSelection={true}
    filterMode="none"
    statusBar={true}
    containerHeight={260}
  />

  <p class="note">
    Click a cell, then try <kbd>Ctrl+Shift+4</kbd> (currency),
    <kbd>Ctrl+Shift+5</kbd> (percent), <kbd>Ctrl+B</kbd>. Sort by clicking a
    header: the formatting follows its row rather than staying on the index.
    Column D holds <code>=B*C</code> and column E holds
    <code>=D/$D$5</code>; edit units or price in the bar and the revenue, the
    total and every share recompute together.
  </p>
</section>

<style>
  .wrap {
    display: flex;
    flex-direction: column;
    gap: 10px;
  }
  .cell {
    display: block;
    width: 100%;
    height: 100%;
    text-align: inherit;
    font: inherit;
    border: 0;
    background: transparent;
    color: inherit;
    padding: 0 2px;
    cursor: pointer;
  }
  .cell.active {
    box-shadow: inset 0 0 0 2px var(--sg-accent, #6366f1);
    border-radius: 2px;
  }
  .note {
    margin: 0;
    font-size: 13px;
    line-height: 1.6;
    color: var(--sg-muted, #64748b);
  }
  kbd {
    font-family: ui-monospace, Menlo, monospace;
    font-size: 11px;
    border: 1px solid var(--sg-border, #cbd5e1);
    border-radius: 3px;
    padding: 0 4px;
  }
  code {
    font-family: ui-monospace, Menlo, monospace;
    font-size: 12px;
  }
</style>
```

[View this example on GitHub](https://github.com/sv-grid/sv-grid/blob/main/examples/src/demos/453-formula-bar-formats.svelte)