This follows Excel's spreadsheet precedence, not programming-language conventions:
=-2^2 returns 4.
=2^3^2 returns 64.
=2^-2 returns 0.25.
Use parentheses when a formula must also work in a non-spreadsheet evaluator.
Percentage divides its operand by 100 and may repeat: =50%% returns 0.005. Concatenation formats numbers, booleans, and blanks as spreadsheet text. Arithmetic and comparison bind before &.
Comparisons return native booleans. For example, =IF(A1 >= 100, A1 * 0.9, A1) uses a comparison as its condition.
Intersection
A space between two references is the intersection operator. =SUM(A1:C3 B2:D4) adds the shared cells B2:C3. Intersection and union apply only to references.
Position or input
Result
One-cell intersection in a value position
The value of that cell.
Larger intersection at the root of a formula
Spills like a range.
Empty intersection
#NULL!
References on different sheets
#NULL!
An operand that is not a reference
#VALUE!
Other spaces are not operators. Spaces around other operators, after commas, before ), and between a function name and ( do not change the formula.
Union
A comma inside parentheses that are not a function call is the union operator. =SUM((A1:A2,C1:C2)) adds both ranges. =COUNT((A1,B2,C3)) counts three cells. A comma that separates function arguments is not a union.
Functions that read their arguments as a list of values accept a union. Examples are SUM, COUNT, COUNTA, AVERAGE, MAX, MIN, PRODUCT, SMALL, LARGE, MEDIAN, STDEV.S, STDEV.P, VAR.S and VAR.P. Other supported list functions include RANK.EQ, MODE.SNGL, PERCENTILE.INC, QUARTILE.INC and GEOMEAN.
Function
Union behavior
INDEX(reference, row, [column], [area_num])
Returns from area area_num. The first area is 1, which is the default. An area_num after the last area returns #REF!.
AREAS(reference)
Returns the number of union members, or 1 for a cell, range or intersection.
A union in other positions returns #VALUE!. Examples are =(A1,B1), the table of VLOOKUP, and ROWS.
3-D references
A 3-D reference reads the same cell or range on a span of sheets: =SUM(Jan:Mar!B2) or =SUM(Jan:Mar!B2:C5). If a sheet name needs quotes, quote the full span: ='Sheet 1:Sheet 3'!A1. The span includes every sheet from the first endpoint to the last endpoint, in workbook tab order.
These functions accept a 3-D reference: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, VAR.S and VAR.P. A 3-D reference in other positions returns #VALUE!. On a sheet with a smaller grid, cells outside that grid are blank.
Sheet edits
Sheet edits change the span as in Excel:
A sheet that you insert or move between the endpoints joins the span.
A sheet that you move out of the span leaves it.
A deleted sheet leaves the span. If it is an endpoint, the endpoint moves to its inner neighbour and the formula text changes.
If you move an endpoint past the other endpoint, that sheet leaves the span and its inner neighbour becomes the endpoint. For example, Sheet2:Sheet6 becomes Sheet3:Sheet6 when Sheet2 moves after Sheet6.
If you rename an endpoint, the formula text changes.
If you delete all sheets of the span, the reference returns #REF!.
Row and column insertions and deletions on one sheet do not change a 3-D reference. Undo of a tab move restores any formula text that the move rewrote.
Preservation and dependencies
Intersection dependencies include only the shared cells. Formula text for reference operators survives display, editing, and memory transfer.
XLSX import and export keep the Excel formula text of intersections, unions and 3-D references. Examples are SUM(Jan:Mar!A1), SUM((A1,B1)) and SUM(A1:A3 A2:A4).
const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.store: StoreinterfaceGrid {store:Store;}interfaceGrid {store:Store;}Store.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultApply a low-level storage transaction. This bypasses Grid read-only checks and Grid undo/redo history. Use Grid.applyTransaction for normal host-driven edits. Queued and flushed at a barrier — never reentrant.API reference →Transaction.patches: DocumentOp[]interfaceTransaction {patches:DocumentOp[];}interfaceTransaction {patches:DocumentOp[];}Ordered document operations submitted as one store commit.API reference →addr: CellAddresslet addr:CellAddresslet addr:CellAddressAPI reference →CellAddress.sheet: stringinterfaceCellAddress {sheet:string;}interfaceCellAddress {sheet:string;}Sheet identity used by the simple data-first adapter. Defaults to sheet1.API reference →CellAddress.row: numberinterfaceCellAddress {row:number;}interfaceCellAddress {row:number;}0-based row of the cell that holds the top-left corner.API reference →CellAddress.col: numberinterfaceCellAddress {col:number;}interfaceCellAddress {col:number;}0-based column of the cell that holds the top-left corner.API reference →value: CellValuelet value:CellValuelet value:CellValueAPI reference →src: stringlet src:stringlet src:stringfunction createGrid(host: HTMLElement, opts: GridOptions): GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridCreates and mounts an imperative Grid in the supplied host element.Exampleimport { createGrid, initSheetwrite } from"@sheetwrite/core";awaitinitSheetwrite();consthost= document.createElement("div");host.style.height ="400px";document.body.append(host);constgrid=createGrid(host, {workbook: {activeSheet: "sales",sheets: [{ id: "sales", name: "Sales", rowCount: 2,columns: [{ key: "amount", header: "Amount", type: "number", width: 120 }] }],},data: { rowCount: 2, columns: { amount: [12, 18] } },});grid.scrollToCell({ sheet: "sales", row: 1, col: 0 });// Call when the host view is removed.grid.destroy();See alsoGrid *See alsoGridOptionsAPI reference →function initSheetwrite(source?: BufferSource | URL | string | Request | WebAssembly.Module, engine?: SheetwriteEngine): Promise<void>functioninitSheetwrite(source?:BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>functioninitSheetwrite(source?:|BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>Load one WASM engine before createGrid. The default is @sheetwrite/wasm. Pass the @sheetwrite/formulas module as the second argument to select the full engine.Exampleimport { initSheetwrite } from"@sheetwrite/core";import*as formulas from"@sheetwrite/formulas";awaitinitSheetwrite(undefined, formulas);See also/docs/start/choose-an-engine/See alsocreateGridAPI reference →const host: HTMLElement | nullconsthost:HTMLElement|nullconsthost:HTMLElement|nullvar document: Documentvar document:Documentvar document:Documentwindow.document returns a reference to the document contained in the window. MDN ReferenceAPI reference →ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement | null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)Returns the first element that is a descendant of node that matches selectors. MDN Referenceinterface HTMLElementinterfaceHTMLElementinterfaceHTMLElementThe HTMLElement interface represents any HTML element. MDN Referencevar Error: ErrorConstructor
new (message?: string, options?: ErrorOptions) => Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)const host: HTMLElementconsthost:HTMLElementconsthost:HTMLElementElementCSSInlineStyle.style: CSSStyleDeclarationinterfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}interfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}MDN ReferenceAPI reference →CSSStyleDeclaration.height: stringinterfaceCSSStyleDeclaration {height:string;}interfaceCSSStyleDeclaration {height:string;}MDN ReferenceAPI reference →GridOptions.workbook: WorkbookinterfaceGridOptions {workbook:Workbook;}interfaceGridOptions {workbook:Workbook;}Live workbook schema adopted by the store and updated by document operations.API reference →Workbook.activeSheet: stringinterfaceWorkbook {activeSheet:string;}interfaceWorkbook {activeSheet:string;}Active sheet ID and initial tab presented when the grid is created.API reference →Workbook.sheets: Sheet[]interfaceWorkbook {sheets:Sheet[];}interfaceWorkbook {sheets:Sheet[];}Sheets in display/tab order.API reference →Sheet.id: stringinterfaceSheet {id:string;}interfaceSheet {id:string;}Stable identifier, unique within the workbook and used by every cell address.API reference →Sheet.name: stringinterfaceSheet {name:string;}interfaceSheet {name:string;}User-facing sheet name shown in tabs and workbook exports.API reference →Sheet.rowCount: numberinterfaceSheet {rowCount:number;}interfaceSheet {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →Sheet.columns: Column[]interfaceSheet {columns:Column[];}interfaceSheet {columns:Column[];}Ordered schema; array positions are the zero-based column coordinates.API reference →Column.key: stringinterfaceColumn {key:string;}interfaceColumn {key:string;}Non-empty key, unique within the sheet, used to map input and datasource values.API reference →Column.header: stringinterfaceColumn {header:string;}interfaceColumn {header:string;}Schema label used by table exports and data-grid presentation headers.API reference →Column.type: CellFormatinterfaceColumn {type:CellFormat;}interfaceColumn {type:CellFormat;}Controls cell input parsing and default value formatting for this column.API reference →Column.width: numberinterfaceColumn {width:number;}interfaceColumn {width:number;}Unzoomed column width in CSS pixels.API reference →GridOptions.data?: ColumnarData | undefinedinterfaceGridOptions {data?:ColumnarData|undefined;}interfaceGridOptions {data?:ColumnarData|undefined;}Eager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →ColumnarData.rowCount: numberinterfaceColumnarData {rowCount:number;}interfaceColumnarData {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →ColumnarData.columns: Record<string, ArrayLike<CellScalar | CellValue>>interfaceColumnarData {columns:Record<string, ArrayLike<CellScalar|CellValue>>;}interfaceColumnarData {columns:Record<string,ArrayLike<CellScalar|CellValue>>;}Exact column runs represented by every returned row.API reference →a: number[]let a:number[]let a:number[]b: number[]let b:number[]let b:number[]c: number[]let c:number[]let c:number[]result: null[]let result:null[]let result:null[]Search state after the replacement (matches re-scanned against the new data).API reference →