Changelog
v19.1.0 (2026-11-21)
Section titled “v19.1.0 (2026-11-21)”Features
Section titled “Features”-
Workbook.setSheetHiddenhides, very-hides or unhides a sheet, and emits ahidesheetevent when it does. It refuses to hide the only visible sheet, as Excel does, and refuses to hide the active sheet until another sheet is made active. -
Charts can be added, read, changed and deleted through
sheet.graphics, which takes and returns JSF objects, always as copies. A chart is stored inworkbook.charts, and shown on the sheet by a chart graphic (the object Excel’s Selection Pane lists), which sits in a drawing anchored to the sheet. Graphics are identified by theirid, unique on the sheet:sheet.graphics.addChart(chart, anchor, { name })places a chart in a drawing of its own and returns the new chart graphic. Without a name it is called “Chart N”, as Excel names charts.sheet.graphics.getChart(id)returns the graphic, where it sits, whether it is in a group and the chart it shows; for a chart in a group, the position is worked out around the chart itself.sheet.graphics.setChart(id, chart),setPosition(id, anchor)andsetName(id, name)change the chart, the position and the name. A chart in a group can’t be moved on its own.sheet.graphics.delete(id)removes a chart graphic, also from inside a group, ungrouping and refitting the group as Excel does. The chart is removed fromworkbook.chartsonce no graphic shows it.
addChartandsetChartrefuse a chart whose ids clash, whose plots, encodings and axes name things it doesn’t have, or whose formulas don’t parse or name missing sheets;addChartandsetPositionrefuse an anchor outside the sheet.A chart that has no axis for one of its scales is left out when the workbook is saved as XLSX.
API changes
Section titled “API changes”- The following now throw the new exported
RewriteRefusedErrorwhen a name cannot be written into a reference. Previously, they threwFormulaSyntaxError, wrongly implying that the original formula did not parse. Code that catchesFormulaSyntaxErrorto handle a refused name should catchRewriteRefusedErrorinstead.- The functions returned by
getReplaceSheetReferencesFn,getReplaceTableReferencesFnandgetReplaceWorkbookFn, for the new name. Model.rewriteFormulaAfterMove, whentonames a sheet, workbook or directory that cannot be written into a reference prefix, such as one containing[.Workbook.moveCells.
- The functions returned by
- A
recalcEventrootevent can now have the cause'aborted', for a cell that a failed recalculation left for the next one. Code that handles each cause needs a case for it. - A call that changes the model while it is recalculating, as from a
metricsorrecalcEventlistener, now throws the newRecalculationInProgressError. Previously, the change could give wrong results. Make the change from arecalclistener, or afterModel.recalculatereturns, instead. - Assigning to
WorkSheet.hiddendirectly is deprecated; useWorkbook.setSheetHidden. WorkbookView.setActiveSheetNamenow refuses a hidden or very hidden sheet, with the newActivateHiddenSheetError. Show the sheet withWorkbook.setSheetHiddenfirst.Workbook.removeSheetnow refuses to remove the following:- The only visible sheet of a workbook, with the new
LastVisibleSheetBlockedError, as Excel does. Previously, it removed the sheet and left a workbook of hidden sheets only, or, when the sheet was the workbook’s only one, put an emptySheet1in its place. To replace a workbook’s only visible sheet, add the new sheet before removing the old one. - The active sheet of a workbook view, with the new
ActiveSheetBlockedError. Make another sheet active withWorkbookView.setActiveSheetNamefirst.
- The only visible sheet of a workbook, with the new
Bug fixes
Section titled “Bug fixes”-
Defined names:
- A workbook-scoped name behind a sheet prefix (
=Sheet1!name) is now read, except through a link to a workbook that is not loaded. Previously, the formula evaluated to#NAME?. - A formula now reads a built-in defined name such as
Extract,CriteriaorDatabase, which a workbook file stores with an_xlnm.prefix, instead of evaluating to#NAME?. - Formulas reading a defined name that calls
INDIRECTnow follow later changes to the cells the name reads after the name is given a new formula witheditCellorsetDefinedName. Previously, they kept their value until every formula was recalculated.
- A workbook-scoped name behind a sheet prefix (
-
In the following cases, deleting a sheet now drops a name scoped to it, as Excel does, rather than making it workbook-level, so that formulas using the name evaluate to
#NAME?:- A formula has referred to that name without a sheet prefix and could not resolve it.
- A workbook-level name of the same name has been removed.
- Nothing refers to it but formulas on that sheet, and other names scoped to that sheet that sort before it alphabetically and are dropped themselves.
-
Array formulas and spilling:
- An array formula in a workbook exported from Google Sheets that another cell blocks now evaluates to
#SPILL!. Previously, some such formulas spilled over the blocking cell, or, where the blocker was another array formula, both got wrong results and the workbook could not be exported. - A dynamic array formula loaded with a saved
#SPILL!error now shows its result when that result is a single cell, such asSEQUENCE(1), instead of keeping the error.
- An array formula in a workbook exported from Google Sheets that another cell blocks now evaluates to
-
Non-array formulas that result in a range:
- Outside Google Sheets mode, a formula that results in a range of several rows and columns now evaluates to the cell at the formula’s own row and column when the formula’s cell lies within both the range’s rows and its columns. Previously, it evaluated to
#VALUE!. - In Google Sheets mode, a formula whose result is a defined name for a range, directly or through a function such as
CHOOSEorIF, no longer spills the range. Like the range written out, it now evaluates to#VALUE!for a range of several rows and columns, and to the value in the formula’s row or column for a single column or row. - In Google Sheets mode, a formula that is an
INDIRECTcall of a range address or anOFFSETcall, or returns one through a function such asIF, now spills the range. - In Google Sheets mode, a formula that is a
LETcall now spills a range that is its result. - In Google Sheets mode, a formula that returns a range of more than one cell from
INDIRECT,OFFSETorLETthroughIFSorSWITCHnow always evaluates to#VALUE!. Previously, some such formulas spilled the range or evaluated to the value in the formula’s row or column.
- Outside Google Sheets mode, a formula that results in a range of several rows and columns now evaluates to the cell at the formula’s own row and column when the formula’s cell lies within both the range’s rows and its columns. Previously, it evaluated to
-
Circular references and recalculation:
- When a recalculation fails partway, such as when a
recalcEventlistener throws, the next recalculation now updates the cells that depend on cells the failed one had already changed. Previously, those cells could keep stale values. - Array formulas in a circular reference that repeat a calculation over a range, such as
=C2+SUM(D$2:D$101)/1000filled downD2:D101, now iterate to the same values as formulas that don’t repeat it. - With iterative calculation on, a formula cell in a circular reference that its Excel file saves without a value now starts the calculation as an empty string, as in Excel, rather than as 0, so arithmetic on it now evaluates to
#VALUE!. - A circular reference through a defined name holding
INDIRECTwith relative R1C1 text, such asINDIRECT("R[-1]C",FALSE), is now reported as a plain circular reference, without an “Unexpected error evaluating formula” error.
- When a recalculation fails partway, such as when a
-
Sheet, table and workbook names written in another form:
- A reference now finds a sheet, table or workbook whose name it spells in a form Excel for Windows reads as that name: a sheet or workbook name in another case that was not matched before, such as
λογοσ!A1for sheetλογος, and a table name also withssforß, such asSTRASSE[Wert]for tableStraße. - A reference now also finds a name written with a canonically equivalent character, such as
ά(U+1F71) forά(U+03AC), or with a modifier letter that Excel reads as its base letter, such asA_s!A1finding sheetᴬ_s. - Names that differ only by a case pair that Excel for Windows keeps apart no longer find each other, and two sheets can now have such names. These pairs include
𐐀and𐐨(U+10400 and U+10428),Ɒ(U+2C70) andɒ, and the two cases of a Cherokee letter or of most Georgian letters. Likewise,İ!A1no longer finds a sheet namedi̇(ifollowed by the combining dot U+0307), as in Excel. - Adding, copying or renaming a sheet to such a form of an existing sheet’s name, such as
λογοσbesideλογος, is now refused, as in Excel. - A loaded formula that writes a name in another case or in such a form is now rewritten with the stored name, as in Excel:
=SHEET2!A1loads as=Sheet2!A1. - Renaming a sheet or table, inserting or deleting cells, rows or columns, moving cells, and the functions that
getReplaceSheetReferencesFnandgetReplaceWorkbookFnreturn now rewrite a reference written in such a form, such asMassefor tableMaße. Removing a name doesn’t yet rewrite such a reference.
- A reference now finds a sheet, table or workbook whose name it spells in a form Excel for Windows reads as that name: a sheet or workbook name in another case that was not matched before, such as
-
Moving cells, and editing rows, columns and sheets:
- Moving cells onto one edge of a range and the cells just beyond it, such as
G5:I6ontoC5:E6underSUM(C2:E5), now contracts the reference to the cells left uncovered (SUM(C2:E4)), as Excel does, instead of leaving it as written. This includes whole-column and whole-row references, and moves from another sheet. - Moving cells over all of a whole-column or whole-row reference, such as column B onto column C under
SUM(C:C), now turns the reference into#REF!, as Excel does. - When
Workbook.moveCellscannot rewrite a formula for the move, as in a workbook whose name contains[, it now throwsRewriteRefusedErrorand changes nothing. Previously, it threwFormulaSyntaxErrorafter dissolving merges in the moved range, without moving the cells. Workbook.moveCellsnow throwsArrayEditBlockedErrorwhen its source or destination covers part of a legacy Ctrl-Shift-Enter array formula, as Excel refuses those edits, rather than moving the whole array or writing into part of it.- When
Workbook.moveCellsmoves charts or other graphics to another sheet, they now get that sheet’s next graphic ids, as Excel does, rather than keeping ids it may already use. A default name such as “Chart 1” is renumbered to match. - Reordering rows or columns so that one edge of a range moves past the other now leaves the reference on the cells that stayed, as Excel does: moving columns E to G before column A under
SUM(C3:E4)now writesSUM(F3:G4), notSUM(F3:H4). - Inserting, deleting or moving cells now shifts a whole-column or whole-row span written with a trim-range operator, such as
B.:Dor2.:.4. Previously, such a span stayed unchanged. - In a chain of range operators, deleting rows or columns now shrinks each pair of cells as one range: deleting column A under
1:1:$A$2:$C$3now writes1:1:$A$2:$B$3, not1:1:#REF!:$B$3. - Renaming a table no longer renames a
LETorLAMBDAvariable of the same name. - Renaming a sheet now quotes its new name in references wherever Excel for Mac or Excel for Windows quotes it, as in
's_Ϳ'!A1. Previously some such names were written unquoted, and Excel for Windows removed those formulas when it opened the file. - Renaming a sheet so that a 3-D reference inside
LETorLAMBDAstarts at a sheet named like one of its variables now quotes the reference whole, as in=LET(y,1,SUM('y:Sheet2'!A1)). Previously it read as a range from that variable, and evaluated to#VALUE!. - A model error’s
referencesnow still name the same cells and sheet-scoped names after a sheet is added, reordered or copied, instead of whichever sheet took over the old position. - Removing a sheet that comes before a view’s active sheet now leaves that sheet active. Previously, the view’s active sheet became the one after it, or none.
- Moving cells onto one edge of a range and the cells just beyond it, such as
-
Formula parsing:
- In a
LETorLAMBDA, a variable directly before an unquoted sheet-qualified reference, as inLET(R,Mar!B1,SUM(R:Mar!A1)), now ranges from the variable to that reference, as in Excel, instead of reading as a 3-D reference from a sheetRtoMar. The same holds for a variable written in another case, such asΛXforλx. - A
LETthat binds the same name twice, or aLAMBDAthat names a parameter twice, is now rejected, as in Excel, also where the names differ only in case or as Excel folds them, such asxandX,ßandSS, orfiandfi. Previously, one of the bindings was lost. - A name now finds a
LETvariable as Excel does, and as it already found aLAMBDAparameter:ẞfindsß, butSSno longer findsß, though the two still count as the same name when bound twice. - The following trim-range forms are now rejected, as in Excel: a trim after a range, as in
$A$1:$B$2.:$C$2, and a trim before a sheet-qualified reference, as inA1:.Sheet2!B2. - A name ending in a dot before a colon, as in
SUM(Revenue.:B2), now reads as the nameRevenue.and a range operator, as in Excel, instead of as the nameRevenueand a trim-range operator. - A spill operator after a range of two cells, as in
C1:A1#, now applies to the whole range, as in Excel, instead of to the second cell alone. - A formula nested more than 128 levels deep is now rejected instead of overflowing the stack, which broke the parsing of every later formula.
- An error literal written in lower or mixed case in a formula, such as
=#ref!, now evaluates to that error, as in Excel and Google Sheets, instead of#UNKNOWN!. - A structured reference to a table column whose name has a
',#or@, such asSales[It''s]orSales[Item '#], now finds that column instead of evaluating to#REF!, and keeps its escapes when cells are sorted. - The message for a formula that does not parse now reads “Unrecognized formula syntax”, not “Invalid name or unrecognized formula syntax”. It is the message of
FormulaSyntaxErrorand of theModelErrorrecorded for such a formula, and the detail of the#NAME?thatModel.readValueandModel.evaluateExpressionreturn for one.
- In a
-
The following formulas, which were rejected, now parse as Excel reads them:
- a 3-D reference whose first sheet name alone is quoted, as in
'foo bar':Gamma!A1 - a column name in doubled brackets that starts with
@, such asSales[[@x]], which names the column@x - a workbook name in brackets directly before a name, as in
SUM([Book.xlsx]Alpha:'[1]3'!A1), which evaluates to#NAME?
- a 3-D reference whose first sheet name alone is quoted, as in
-
The following no longer fail with a stack overflow:
- loading a sheet with very many cells that need recalculation on load
- loading a workbook exported from Google Sheets with very many formulas whose spill ranges conflict
- inserting or deleting rows or columns that very many formulas refer to
- adding, copying, renaming or moving a sheet that very many 3-D references span
Workbook.setArrayFormulaover a range of very many cells- deserializing a model saved by an old version with very many formulas
-
A reference into an external workbook that Excel could not refresh now reads a cell missing from the saved cache as
#REF!where Excel does, rather than as blank, andCELLasked about such a cell’s format, color, parentheses or width now returns#N/A. -
A two-variable data table where one input cell is a formula that reads the other now computes, instead of showing
#VALUE!in its first result cell and leaving the others blank. -
Label detection in
describeWorkbooknow handles more forecast-style layouts:- Row names and their units now name a row’s values even when blank historical columns separate them from those values.
- A named row that has no values yet now keeps its own empty cells.
- A unit-only row that continues a named row is now named by its unit, even when the units repeat down the column.
- When the first row of a block holds a unit or a text marker such as
n/a, it is now treated as data instead of a header. - A bold or filled heading alone on its row now covers its section across blank rows and indented blocks, stopping at a spanning table’s label row or text styled like the heading or an enclosing heading.
-
When a pivot table is removed and its charts become plain charts, each series now keeps a point for every cell of its range, blanks included, and series over several ranges keep their values instead of their last cached ones.
-
A pivot table loaded into an Excel workbook with the output its file saved now keeps that output as saved until it is refreshed or edited, as in Excel:
GETPIVOTDATAnow reads that output instead of values computed from the pivot cache; for a pivot table saved without its list of row items, it returns#REF!until then.- Loading no longer writes computed cells, such as a second
Grand Totalrow, into the empty cells of that output. - A pivot table saved with its list of row items and no output cells now shows no output until it is refreshed, and
GETPIVOTDATAreads its empty cells as blank.
-
A pivot table in compact form (Excel’s default) set to show subtotals at the bottom of each group now shows each group’s subtotal on a row of its own at the bottom of the group, as Excel does, instead of on the group’s header row.
-
A pivot table with several value fields and Σ Values on rows now lays out as in Excel:
- In compact form, each item of an outer row field now opens on a row of its own with its subtotal below, and the value field names nest in the label column instead of taking a column of their own.
- A column field’s subtotal columns are now captioned
2024 Totaland so on, not with a value field’s name. - With a single row field in outline form, each group now opens on a row of its own, whatever the field’s subtotal setting. Previously, the group label shared the first value field’s row.
- With several row fields, the innermost now opens each group on a row of its own when its subtotals are off through
subtotalFunctions: [], as it already did withdefaultSubtotal: false. - An inner row field’s item now shows only on the top row of its group in each values block. Previously, the item was repeated on the row below.
- An outer row field’s custom subtotal (such as Average) now labels the subtotal row of each value field, as in
North Average of AmtandNorth Average of Qty. Previously, only the first of those rows had a label, and it lacked the value field’s name (North Average). - A row field below Σ Values now shows its subtotal label in every values block. Previously, only the first block had the label.
-
Pivot table date fields:
- In a pivot table with several value fields and Σ Values on rows, or between its column fields, header cells of a date field now show dates in the field’s number format, as Excel does. Previously, they showed date serial numbers.
- In any pivot table, header cells of a date field with no number format of its own now show dates as
m/d/yyyy, date-times asm/d/yyyy h:mmand times ash:mm:ss AM/PM. Previously, they showed the source column’s format or date serial numbers. A Top or Bottom sum or percent filter on such a field now picks among tied items by that text. - A date field grouped by years, with the grouping’s “Ending at” date set by hand to a 1 January, now keeps a record dated exactly on that day in its own year after a refresh, as Excel does. Previously, the refresh counted it in the previous year.
-
A pivot table with merged labels now clears the borders of a cell that a merged label spanned, once a later refresh shrinks or drops the merge. Previously, the cell kept its borders on the sheet and in the exported workbook if the merge had survived an earlier refresh.
-
A pivot table created with
Workbook.pivots.addnow hides items with no data, as a pivot table created in Excel does, rather than listing every inner item under every outer item. -
Pivot table sorting:
- Sorting an outer row or column field, by value or by another field’s labels, now moves each of its items together with the items under it, as Excel does, instead of scattering them and showing garbled output such as repeated subtotal rows.
- Sorting by a value field summarized by average, max, min, product or standard deviation now orders the items by the totals the pivot table shows for them.
- A sort by another field’s labels, which files from producers other than Excel can declare, now also applies in a pivot table with no value fields, and now uses that field even when the file’s sort settings list an empty field reference before it.
- Grouped fields (date, numeric range or discrete groupings) with
sortType: 'ascending'or'descending'now sort their items as Excel does, instead of keeping their group order. - Fields with
sortType: 'ascending'or'descending', and the fields of a new pivot table, now set hyphens and apostrophes aside when sorting text items, as Excel does, soabsorts beforea-c.
-
Renamed pivot items and pivot label filters:
- A renamed pivot item, such as a renamed group, now shows its new caption in headers, subtotal labels and the report filter, and label filters and sorts now use that caption. Renamed dates sort ahead of the other dates.
- Label filters now test a blank item as “(blank)”.
- On a field that mixes numbers with text, a label filter with a number value now compares number items by number: “greater than 10” keeps only number items, and “does not equal 5” keeps every text item.
-
Pivot table report filters now match Excel:
- A report filter with some items hidden now reads
(Multiple Items), or(All)when the pivot table turns that label off. Previously, it read(All). When it allows multiple items and shows exactly one, it now reads that item’s name. - A report filter that does not allow multiple items now leaves out its hidden items. Previously, it showed every item. When it selects a hidden item, it now shows the next visible item in its place, instead of the hidden item’s data. With every item hidden, it now shows the first item.
- A pivot table whose filters leave out every record now shows its report filter cells, header row and
Grand Totalrow. Previously, it showed nothing. PivotField.setAxis('page'), which moves a field to the report filter area, now removes the field’s label, value, top-N and date filters, instead of keeping them to reapply when the field moves back. The field’s hidden items now still filter the pivot table after the move.
- A report filter with some items hidden now reads
-
Exported pivot tables:
- After a refresh, an exported workbook’s pivot cache now describes the refreshed data: each cache field’s type and range attributes (
minValue,maxValue,contains*) are recomputed. A field whose dates the refresh replaced is no longer treated as date-typed. - A pivot table with several value fields, no column fields and a Values header row kept in its saved layout now exports without the “hide the Values row” flag, so Excel keeps that row when it refreshes the exported workbook. Previously, Excel collapsed the row on refresh.
- A pivot table whose only fields are report filters now exports its location as its top-left cell alone after a refresh, as Excel saves it.
- A report filter that selects a hidden item is now exported, after a refresh or a change to the pivot table, with the item shown in its place as the selected item. With every item hidden, that item is also unhidden.
- A report filter that allows multiple items is now exported with its first item unhidden after a refresh with every item hidden.
- A report filter that allows multiple items is now exported without a selected item, since Excel refuses to open a file that has one. The items it does not show are exported as hidden instead.
- After a refresh, an exported workbook’s pivot cache now describes the refreshed data: each cache field’s type and range attributes (
-
GETPIVOTDATAnow matches items by the captions that the pivot table shows, as Excel does:- An item renamed in the pivot table now also matches its new caption, instead of returning
#REF!. - The older
GETPIVOTDATA(A3,"Region[item]")form now matches an item only by the caption it shows, including(blank)for the blank item and a caption that reads as a number. A caption that two items show matches neither, and returns#N/A. - That form now also matches a page field’s items written without brackets, such as
GETPIVOTDATA(A3,"Sud"). - An omitted item argument now matches the blank item, instead of returning
#REF!. So does"(blank)", unless the blank item is renamed or another item is the text(blank).
- An item renamed in the pivot table now also matches its new caption, instead of returning
-
GETPIVOTDATAnow returns what Excel returns in the following cases:- For a source field’s name that several value fields summarize, such as
"Sales"withCount of SalesandSum of Sales, it now reads the first value field that sums the field, or the first of them if none does. Previously, it returned#REF!. - The older form
GETPIVOTDATA(A3,"Sales")now reads the first of those value fields. - In the older form
GETPIVOTDATA(A3,"Units"), a name that is both a field’s name and another value field’s caption now reads the value field that summarizes the named field. - A field renamed in the pivot table is now named by its new name, as a value name and in a field and item pair. In Google Sheets mode too, a renamed row field is named by its new name in a field and item pair, as in Google Sheets.
- An outer item that has no subtotal of its own and no label row now reads its one row or column. Previously, it returned
#REF!or 0. - An item whose subtotal is hidden under a parent item that shows its own now reads the row that shows its label when the parent’s subtotals are at the top of their groups. With them at the bottom,
GETPIVOTDATAnow returns#REF!for the item’s blank label row. Previously, it returned 0.
- For a source field’s name that several value fields summarize, such as
-
In Google Sheets mode, the following
GETPIVOTDATAresults now match Google Sheets:- A pivot table argument that is not a reference now returns
#N/A, even when it or the value name is an error. One such argument is the text in Excel’s older formGETPIVOTDATA(A3,"Region[North]"). - An item argument that evaluates to an error now returns that error, instead of
#REF!:GETPIVOTDATA("Sales",A3,"Region",1/0)returns#DIV/0!. - With errors in several field and item arguments, the result is now the first of them in argument order.
- A field with no item, as in
GETPIVOTDATA("Sales",A3,1/0), and a reference outside any pivot table now return#REF!, even when a field or item argument is an error. - An empty item argument (omitted,
""or an empty cell) now matches the blank item or the""item, instead of returning#REF!. ""now names a field whose source header is empty.- A value name that names no value field now reads the value field of a pivot table that has only one, instead of returning
#REF!.
- A pivot table argument that is not a reference now returns
-
Outside Google Sheets mode, the following comparisons of text now match Excel:
- The
<,<=,>and>=operators and criteria (inCOUNTIF,SUMIFand others) now set hyphens, dashes and apostrophes aside, so"<25|x"no longer counts25-34|y. - The comparison operators and criteria now read
ßasss, and=criteria now ignore soft hyphens, as the operators already did. XMATCH,XLOOKUPandSWITCHnow match text as=does, andSORT,SORTBYand approximate lookups now order it as<does.- A
=criterion now tells a hyphen from a fullwidth hyphen, and the hyphen U+2010 from the non-breaking hyphen U+2011. - Criteria, the
=operator and lookups such asMATCHandXLOOKUPnow tell a fullwidth letter from its ASCII counterpart, and the ideographic space U+3000 from a space, as Excel for Windows does.
- The
-
Outside Google Sheets mode, text longer than 255 characters is now handled as in Excel:
MATCH,VLOOKUP,HLOOKUPandLOOKUPnow return#VALUE!for a lookup text longer than 255 characters.- In wildcard and approximate lookups, those four functions now also pass over cell texts longer than 255 characters.
COUNTIF,COUNTIFS,SUMIF,SUMIFS,AVERAGEIF,AVERAGEIFS,MINIFSandMAXIFSnow return#VALUE!for a criterion longer than 255 characters.
-
Outside Google Sheets mode,
MATCHnow reads its match type as Excel does:- With a match type read from a cell holding an error or non-numeric text, it now returns
#REF!. Previously, it returned the cell’s error, or#VALUE!for the text. - It now reads a match type given as a range of several cells from the range’s first cell. Previously, it returned
#VALUE!. - With a match type given as a union of ranges, such as
(A1,C1), it now returns#REF!. Previously, it returned#N/A.
- With a match type read from a cell holding an error or non-numeric text, it now returns
-
MATCHnow returns the error of an invalid match type rather than the#N/Aof a lookup range that is a union of ranges or not a single row or column. -
The following functions now return
#VALUE!for a range in an external workbook that is not loaded, rather than computing from the values saved for the link:SUMIFS,COUNTIFSand the other criteria functions now do so for any range argument, as Excel for Windows does. They now also return#VALUE!for a range in a workbook that the model has no link to and, outside Google Sheets mode, for a range on a sheet that does not exist. Previously, they returned#REF!for those two.- The database functions (
DSUMand the like) now do so, as Excel does.
-
INDIRECTnow reads a workbook-scoped name behind a sheet prefix (INDIRECT("Sheet1!name")), a name defined as a table’s name, and, in Excel mode,#after a name or a cell address (INDIRECT("A1#")). -
INDIRECTnow returns#REF!, as Excel does, for the following text, in Google Sheets mode too:- a range with a name as an endpoint (
INDIRECT("A1:name")) - a range with a sheet prefix on its right endpoint
- a chain of range operators
- an intersection
- a reference in parentheses
- a name behind a bracketed workbook prefix with no sheet (
INDIRECT("[Book1.xlsx]!name"), whileBook1.xlsx!nameis still read) - text with whitespace at either end; in Excel mode, trailing spaces and line feeds are still ignored, as in Excel
- a range with a name as an endpoint (
-
SUBTOTAL(2, …)andSUBTOTAL(102, …)now skip an error cell in their ranges, asCOUNTdoes, instead of returning the error. -
Count arguments and text functions:
LEFT,INDEX,ROUNDand the other functions that read a count just below an integer as that integer now do so up to about 2.3853E-7 below it, rather than about 2.3842E-7, as Excel for Windows does. So doWEEKDAYandWEEKNUMfor their return type.- Outside Google Sheets mode,
SUBSTITUTEnow reads an instance number just below an integer as that integer, as Excel for Windows does: 0.9999999 as 1. In Google Sheets mode, it now replaces every match when the instance number isFALSE, blank or a number above -0.9999999 and below 1, and readsTRUEas instance 1, as Google Sheets does. Previously, it returned#VALUE!in both cases. - Outside Google Sheets mode,
MID,MIDB,REPLACEandREPLACEBnow return#VALUE!for a start or length past Excel’s limit on it: 32767 forREPLACEandREPLACEB, and 2^31-1 (2147483647) forMIDandMIDB, or 2^31 (2147483648) for the start ofMID. Previously, they returned text.
-
Number formatting, as
TEXTapplies it, has the following fixes:- Fraction formats now floor the integer part and carry a fraction that rounds to 1:
# ??/16formats 2.96 as2 15/16, not3 15/16. - Outside Google Sheets mode, subnormal numbers (nonzero values smaller in magnitude than about 2.2E-308) now format as in Excel, such as
0.00E+00for 5E-324. - In Google Sheets and GRID sheet modes, a date before 30 December 1899 now gets the right weekday name.
- A date-only format now shows the next day when the time rounds up to midnight, as Excel does.
- Fraction formats now floor the integer part and carry a fraction that rounds to 1:
-
In Google Sheets and GRID sheet modes, unary plus (
+A1) now keeps the number format of its operand, such as a date or currency format, as unary minus does. -
Financial functions:
- The bond and Treasury bill functions (
PRICE,YIELD,COUPDAYS,TBILLPRICEand sixteen others) now return#VALUE!for a 3-D reference in any argument, as Excel does, rather than#REF!. - In Google Sheets mode,
MIRRover cash flows without both a positive and a negative value now returns#DIV/0!whatever the rates, as Google Sheets does. Previously, it returned -1 for some rates. - In Google Sheets mode,
PVat a rate of -1 now returns#NUM!, as Google Sheets does, not#DIV/0!.
- The bond and Treasury bill functions (
-
Outside Google Sheets mode,
XIRRnow matches Excel in the following:- It now returns the rate where Excel’s search stops, which can differ from the exact root in the last digits, such as 0.19999999403953553 rather than 0.2.
- Where cash flows have more than one root, it now returns the one Excel does, or
#NUM!where Excel finds none. - It now compounds a nonzero cash flow dated before the first date as Excel does, instead of returning
#NUM!. - With a single amount, it now returns the error in a one-cell dates reference rather than
#N/A.
Performance
Section titled “Performance”- Editing cells next to a spill range is now faster, most of all in a long-running process that has handled many different workbooks. Previously, it could take several times as long there as in a fresh process.
- Clearing the cells that block a large spill range, one at a time, is now much faster.
- Recalculation is now faster for non-array formulas loaded from a file that have many function calls, and for formulas that call
IFor another function that evaluates only the arguments it needs.
v19.0.0 (2026-11-08)
Section titled “v19.0.0 (2026-11-08)”Breaking changes
Section titled “Breaking changes”-
Workbook.chartsis now aRecord<string, Chart>of JSF charts (theCharttype of@jsfkit/types), keyed by thechartIdthat the drawings hosting them use, instead of an array ofChartCSF. Look a chart up by its drawing’schartIdinstead of searching the array byid. Charts in CSF input are no longer kept, though the drawings that host them still are.Model serialization is now version 12. Payloads serialized by earlier versions still load, but without their charts, which those versions stored in the CSF form; the drawings that hosted them are kept.
-
The package no longer includes a separate CommonJS build.
require('@grid-is/apiary')now loads the ES module build, as Node.js and bundlers such as webpack and esbuild supportrequireof an ES module. -
Workbook.toXlsxandWorkbook.toXlsxFile, deprecated in favour ofWorkbook.toXLSXandWorkbook.toXLSXFile, are removed. CalltoXLSXandtoXLSXFileinstead, with the same arguments.toXLSXandtoXLSXFileno longer take anoptionsargument, and theToXLSXOptionstype is gone. Its only setting,compressionLevel, has no replacement: each part of the file is now compressed when that makes it smaller, and stored otherwise. -
Cell writes such as
Workbook.editCellno longer acceptz(the number format) orzf(a number format assigned by a formula). Set the number format asnumberFormatin the style,s, instead;sreplaces the cell’s whole style, so leaving outnumberFormatclears the number format. -
Cell.zis read-only. -
The deprecated
editmethod of cells and defined names is removed. UseWorkbook.editCellinstead.
Features
Section titled “Features”- Outside Google Sheets mode, a built-in function’s name can now stand where a lambda is expected, as in
BYROW(A1:C3,SUM); previously such a formula evaluated to#NAME?. Entering such a formula stores the name with Excel’s_xleta.prefix, and the formula without its typed spacing:BYROW( A1:B2 , SUM )is stored asBYROW(A1:B2,_xleta.SUM). Other formulas are stored as typed. - New functions:
- the odd-coupon bond functions
ODDFPRICE,ODDFYIELD,ODDLPRICEandODDLYIELD - the regression functions
LOGEST,GROWTH,PEARSON,RSQandSTEYX - the statistical tests
T.TEST,TTEST,F.TEST,FTEST,CHISQ.TESTandCHITEST - the forecasting functions
FORECAST.ETS,FORECAST.ETS.CONFINT,FORECAST.ETS.SEASONALITYandFORECAST.ETS.STAT GROUPBYandPIVOTBYASCandDBCSAREAS, in Excel and Canvas modes
- the odd-coupon bond functions
Workbook.unsupportedFeatureslists the features of an XLSX file that are known to be missing from the workbook loaded from it, each with a category saying what kind of thing is lost. The list is kept throughtoJSFand model serialization. See “Round-trip losses” in the documentation.- Charts in JSF input are now kept, and written out by
toJSF,toXLSXandtoXLSXFile, along with the drawings that host them. Like CSF charts did, they follow sheet renames, copies and deletions, and row, column and cell edits.
API changes
Section titled “API changes”- The new
formulaForDisplayfunction returns a formula without Excel’s storage prefixes, such as the_xlfn.beforeCONCATor the_xleta.before a function name used as a value, for showing it as a formula bar would. Workbook.setDefinedNamenow takes its optional settings as one options object:setDefinedName(name, formula, { sheet, abortOnError, validateNow, qualifyUnprefixedReferences }). The positional form is deprecated but still works, so existing callers need no change.
Bug fixes
Section titled “Bug fixes”- A formula that Excel saved with a
_xludf.prefix (its marker for a user-defined function), such as_xludf.CONCAT("a","b"), now keeps the prefix on load and evaluates to#NAME?, as in Excel, rather than as the built-in function of that name. - A formula that refers to a defined name in its own workbook as
[0]!MyNamenow resolves the name on load, rather than turning the reference into#REF!. A hidden name still does not resolve and gives#NAME?, because hidden names are not loaded. - A formula in a workbook exported from Google Sheets whose result is blank or an error now loads as that result rather than as look-alike text, because such cells recalculate on load. Pass
recalcUntrusted: falsein the load options to keep the saved values. - A defined name referenced in a sheet-scoped defined name’s formula now resolves to the workbook-scoped name of that spelling, if there is one; previously it resolved to the sheet-scoped one.
- Number formats loaded from CSF are no longer lost on JSF or XLSX export.
- Writing
sas a style index now applies that style’s number format; previously the cell could keep its old one. - An
swrite that changes or removes a cell’s number format now recalculates the formulas that depend on the cell, as azwrite did; previously answrite did not. - Saving a workbook to JSF or XLSX now writes every cell of a spill. Previously the last cells of a spill were left blank when they repeat one value, such as the zeros of
=A2:A20*B2:B20below the data or the#REF!that fills a data table whose input cell is spilled into. A whole-column spill such as=A:A+1is now saved with all its cells. CSF export still leaves these cells out. - With iterative calculation off, the cells of a circular reference and the cells depending on them now keep their saved values, or are left empty if they have none, in more cases than before. When a formula closes the cycle, or is entered later and reads cells of the cycle, that formula gets the value 0. Before, cycle cells and cells depending on them were recalculated from the cycle cell that was reached first, which got the value 0 if it had no saved value.
- A formula calling a defined name’s recursive
LAMBDA, such as=MyFact(B1)withMyFactdefined asLAMBDA(n,IF(n<=1,1,n*MyFact(n-1))), now recalculates when its inputs change. It used to keep its old value and record a model error about building the dependency graph, and loading the workbook did not finish when the recursive call’s arguments used a parameter more than once. - Formulas that pick an element with
INDEXfrom an expression over a range, such asINDEX(A$1:A$600+0,B2), now evaluate correctly when the same expression appears in several formulas within that range, rather than reporting a circular reference and evaluating to 0. - Moving cells, inserting or deleting cells, rows, columns or table rows, and reordering rows or columns now update the formulas that read where a moved formula lands, such as a
SUMover that range or anOFFSETorINDIRECTpointing at it. - Sorting cells now keeps the following in a moved formula, where it used to change the formula’s meaning or fail:
- parentheses around a right operand, as in
=10-(5-A1) - empty arguments, as in
=IF(A1>1,A1,) - trimmed ranges, as in
=SUM(A:.A) - references to another workbook’s directory or to a
#REF!sheet - table column names containing brackets, as in
=SUM(Table1[[Col'[x']]])
- parentheses around a right operand, as in
- Renaming a sheet now updates the text of every formula reading it. Previously, formulas repeating a calculation over it, such as
SUM(Data!C2:C101+0)down a column of array formulas, kept the old sheet name. - In Excel mode, a formula whose result of more than one cell would spill into a cell of a table now returns
#SPILL!, as in Excel, wherever the formula is and even if that table cell is empty; previously it spilled. - A newly entered formula with a
SWITCHorIFScall is now saved as a dynamic-array formula even when no argument of the call is an array or range, such as=SWITCH(A1,0,"zero","n"), as Excel saves it. As in Excel, it stays a plain formula where the call is passed directly to a function that takes an array and returns a single value, as in=SUM(SWITCH(A1,0,0,1,5,6)). - A data table whose source cell is part of a spill or an array formula’s range, or reads a cell that only some of the table’s input values spill into, now updates on a change to a cell that the spilling or array formula reads through a function such as
INDIRECT. - References with no sheet (such as
$B$5) in a workbook-scoped defined name, including inside aLAMBDA:- On load, such a name is now discarded with a warning, as Excel drops it when repairing the file; cells reading it evaluate to
#NAME?. - When set through the API, such as
Workbook.setDefinedNameoreditCell, such a name is now stored with the first sheet’s name (Sheet1!$B$5), and so now survives export to XLSX.B5becomes the relativeSheet1!B5; write$B$5for an absolute reference. The rewritten formula loses its typed spacing and literal forms:SUM(A1:A3) + 1.50is stored asSUM(Sheet1!A1:A3)+1.5.setDefinedNameleaves the formula as written when itsqualifyUnprefixedReferencesoption isfalse.
- On load, such a name is now discarded with a warning, as Excel drops it when repairing the file; cells reading it evaluate to
- Formulas that call unsupported functions:
- Moving a cell that such a formula reads now recalculates the formula to
#NAME?instead of leaving it blank. - Sorting such a formula to another row now keeps its cached value instead of leaving it blank. A sort recalculates it to
#NAME?only if it reads a cell in the sorted range, or lies in that range and has a reference with a relative row or to another sheet. - Such a formula reading a cell through a defined name that uses
OFFSETorINDIRECTnow keeps its cached value when that cell is written or sorted.
- Moving a cell that such a formula reads now recalculates the formula to
- Pivot tables:
- A label filter such as “greater than 10” on a field of numbers now compares the numbers, as Excel does; previously it compared their labels as text, so it kept 9.
- Label filters that compare order (greater than, less than, their “or equal to” variants, between and not between) now sort symbols such as
~and?before digits and letters, as Excel does. - Label filters on a number field now compare numbers at 15 significant digits, as Excel does, so “equals 0.3” now also keeps an item of 0.1+0.2 and “equals 0.30” keeps 0.3. A filter value with a leading zero, such as 0.123456789012345, now keeps all 15 of its significant digits. An “equals” filter that a workbook stores as a list of values instead matches the item’s name, which rounds a tie in the 15th significant digit toward zero, as
&now does. - An “equals” label filter whose stored value is too large for a double, such as
1.7977E+308, now reads it as the largest double of its sign, as Excel does. - A field holding both month and weekday names now lists the weekdays first and then the months, whatever the order of the source values; previously the order depended on it.
- Workbook descriptions (
describeWorkbook) now find table bounds and labels in more layouts:- Aggregate footer labels now stay with their results, including time-valued results that were previously mistaken for labels.
- Separated table headers now label their data without overriding the labels of input values that sit between the header and the data.
- A table’s header fields now label its data when text notes beside the data extend past the header.
- A detected table no longer loses rows or columns when adjacent blocks of cells are merged into it.
- Row and column labels whose first entry repeats a later one, such as a caption that also names the first row, are now kept, instead of being treated as data or merging the table with its neighbour.
- Rows with values in only some columns now get their row names across the blank columns between them.
- A row’s values that continue to its right under column headings set one row higher now get the row’s name.
- Column headings over empty cells at the edge of a table, such as percentage inputs that formulas read or formatted blank result cells, are now kept and label the matching cells in sections below, without crossing into separately headed tables.
- Date-formatted column headings following a plain number in a header row now label their values, such as period dates following a numeric checkpoint.
- A separated header row now labels its table when a heading is computed by a formula returning text.
- Column headings now label all their values when the header block also holds a date and a stray cell sits between the headings and values.
- A column heading now stays with its values beside a tall row-label strip spanning several separate value rows, and the row labels describe their own rows.
- Row names across a column of repeated units now label each row’s values when the column headings start a row above the names.
- Each result of a two-way sensitivity table now has both axis names, its two coordinates and a merged caption above the table; a formula in the top-left corner no longer drops the coordinate labels.
- Each value in a row now gets both its row name and a second label set apart by blank columns, such as a unit or status tag, even when the tag repeats down the column and the table is split into captioned sections.
- A table’s headers no longer describe separate captioned sections of sparse rows below it, and those sections’ captions and row labels now describe their own values.
- Reference unions such as
SUM((A1:B2,D4))now evaluate as in Excel, where they previously evaluated to#REF!: functions that accept one, includingAREASandINDEXwitharea_num, read its areas, and most other functions return the error Excel returns.HSTACK,VSTACK,TOCOL,TOROW,CELL,SHEETandSHEETSdo not support unions yet. INDIRECTof the name of a defined name now returns#REF!, as in Excel, unless the name is in scope and defined as a plain cell or range reference (a structured or spill reference included), not as a formula, constant, another name, 3-D reference, trimmed range, intersection or union. Previously it read such a name however it was defined.- Trimmed ranges:
- A whole column or row (
K:.K,5:.5) is now trimmed, where it was previously read untrimmed. - A trimmed range with no values in it (
=K1:.K3,=TRIMRANGE(K1:K3)) now evaluates to#REF!as a cell’s result, as aSWITCHexpression or result, and as an operand, where it was previously blank,#VALUE!,#N/Aor 0. - A formula now recalculates when a write changes where a trimmed range it reads ends: one that reads only the range’s position, such as
=ROWS(G1:.G9), and a trim of a computed reference, such as=SUM(TRIMRANGE(INDIRECT(B1))). - A trailing trim now ends at a value in the sheet’s first row or column, where it previously removed nothing.
- A trim now leaves out blank cells that have only a number format, which previously stayed in the spill.
- A trim now ends at a cell showing
#SPILL!, as it does at any other value. - A formula that reads only the size of a range around itself, trimmed or through
TAKE,DROP,CHOOSEROWS,CHOOSECOLS,LETor@, such as=ROWS(G1:.G9)in G5, or=ROWS(TRIMRANGE(Q1:Q9))or=ROWS(TAKE(Q1:Q9,1))in Q2, now evaluates to that size, where it was previously a circular reference. - A
SINGLEcall written out, which is how a workbook saved by Excel stores@, now intersects to a cell reference as@does, so=ROWS(SINGLE(Q1:Q9))is 1 where it was previously#VALUE!. INDIRECTnow returns#REF!for text that names a trimmed range, such as"G1:.G9".
- A whole column or row (
- A
LAMBDAparameter can now be called as a function, as in=LAMBDA(f,f(2))(LAMBDA(x,x*3)), which used to be#NAME?. Calling a parameter that holds no lambda returns:#REF!for a reference, a boolean or an omitted argument#VALUE!for a number or text- the error itself for an error
- for an array,
#VALUE!for each element, except the error itself for an error and#REF!for a lambda #N/Ain Google Sheets mode
- Lambdas and lambda helpers:
MAP,BYROW,BYCOL,SCAN,REDUCEandMAKEARRAYnow return#VALUE!for an error in place of the lambda, as Excel does, rather than that error;BYROWandBYCOLnow return#CALC!when the lambda is omitted. In Google Sheets mode, all six now return#N/Afor anything but a lambda taking the right number of arguments, as Google Sheets does, except thatMAKEARRAYreturns an error it is given.- Outside Google Sheets mode,
MAKEARRAYnow returns#VALUE!for fractional rows or columns, as Excel does, rather than truncating them. - Outside Google Sheets mode, calling an expression that evaluates to something other than a lambda, such as
IF(TRUE,5)(2), now returns#VALUE!for a number or text,#REF!for a boolean and the error itself for an error, rather than#N/Afor all of these; an array is called element by element, where a boolean element is#VALUE!. - Calling a
LETvariable or defined name that is not a lambda now returns#VALUE!, or the value itself if it is an error, rather than#REF!; a reference or a boolean stays#REF!. In Google Sheets mode, aLETvariable now returns#N/Aand a defined name#NAME?, as Google Sheets does. - In Excel mode, calling a
LETvariable,LAMBDAparameter or defined name whose name is also a function’s now calls the function only if Excel has that function, as Excel does. So=LET(SPLIT,LAMBDA(x,x*3),SPLIT(2))now returns 6, sinceSPLITis a Google Sheets function; previously it returned#NAME?, or for a name such asARRAYFORMULAcalled the Google Sheets function. And=LET(CUBEVALUE,LAMBDA(x,x*3),CUBEVALUE(2))now returns#NAME?, sinceCUBEVALUEis an Excel function that Apiary does not support; previously it called the variable. - Calling a
LETvariable, defined name or function result that holds an array, as in=LET(v,VSTACK(LAMBDA(y,y*10),1),v(5))or=IF(TRUE,VSTACK(LAMBDA(y,y*10),1))(5), now returns#REF!for each lambda in the array, as Excel for Mac does; Excel builds disagree here, Excel for the web returning#VALUE!and one Windows build calling each lambda. Previously each lambda element gave#VALUE!, like any element that is not an error, calling a function result gave#N/A, and the lambda in a 1x1 array was called. - A cell that calls a lambda picked out of a lambda helper’s argument, as in
=MAP(VSTACK(LAMBDA(y,y*10),LAMBDA(y,y*100)),LAMBDA(g,INDEX(g,1)(5))), now recalculates instead of throwing an error. MAP,REDUCEandSCANover a range now pass each cell to theirLAMBDAas a reference, as Excel does, soROW,ISREFand the like work on it, and a range that includes the formula’s own cell is circular only where that cell’s value is read.REDUCEandSCANwith no initial value over a one-element array that a formula computes, such asA2+0orSEQUENCE(1), no longer report a circular reference when theirLAMBDAreads the formula’s own cell: as in Excel, they return the element without calling theLAMBDA.
- Lookup and array functions:
- Outside Google Sheets mode,
VLOOKUPandHLOOKUPnow return a value for each element of an index given as an array or range, and#REF!for an index cell holding an error or non-numeric text, as Excel does; with an array lookup value orrange_lookup, they use only the index’s first element. INDIRECTwith R1C1 text (a1set toFALSE) now reads lower-caserandc, leading zeros (R01C01), whole rows and columns (R2,C[1],RorCalone), and relative references that wrap past the last row or column, as Excel does.SORTandSORTBYnow order errors by their error type, as Excel does (ascending:#DIV/0!before#NAME?before#N/A; descending: the reverse), rather than keeping them in their original order.- Outside Google Sheets mode,
UNIQUEnow treats text values as duplicates when they differ only by Unicode case folding, such asStraßeandSTRASSE, or by control characters such asCHAR(1), unless one text is the other followed by control characters, as Excel does. UNIQUEwithexactly_onceTRUE now leaves out blank rows (or columns) that occur more than once at the end of a range, as Excel and Google Sheets do; over more than one blank row (or column) and nothing else, it now returns#CALC!.- Outside Google Sheets mode,
WRAPCOLSandWRAPROWSnow return a vector shorter than the wrap count as a single column or row of its own length, without padding, as Excel does. MMULTnow returns a single#NUM!when its result overflows, as Excel does, rather than spilling an array of#NUM!. In Google Sheets mode, each overflowing element is now#NUM!, as in Google Sheets.- Outside Google Sheets mode,
SWITCHnow matches a blank cell with 0 and not with empty text orFALSE, as Excel does. - In Google Sheets mode,
SWITCHnow matches a blank cell expression with empty text andFALSE, as Google Sheets does.
- Outside Google Sheets mode,
- Outside Google Sheets mode,
SWITCHnow treats an array or range given as a case value as Excel does, rather than returning#VALUE!:- It returns one result per element of the array or range.
- In a formula saved without array marks, it compares the expression with the cell of a range case in the formula’s row or column.
- The database functions (
DSUM,DGETand the other ten) now match Excel in the following respects:- Outside Google Sheets mode, a range of more than one cell as
fieldnow returns#VALUE!, rather than one result per cell. - Outside Google Sheets mode, an array given as
criterianow spills one result per element (#VALUE!, or the element’s own error). - A 3-D reference in any argument now returns
#VALUE!, rather than#REF!. - Outside Google Sheets mode, an error held by the cell given as
fieldis now looked up among the headers: the result comes from the first column whose header holds that error or its name as text, such as="#N/A", and is#VALUE!if there is none. Previously the result was the error itself. - Wherever they match headers,
#CALC!,#SPILL!and#FIELD!now count as#VALUE!.
- Outside Google Sheets mode, a range of more than one cell as
- Text functions and number-to-text conversion:
- Outside Google Sheets mode, a number converted to text now has 14 significant digits, not 15, at an exponent of 99 or more in magnitude, as Excel does: in
&, in text functions such asLEN,CONCATandTEXTJOIN, and in the header labels of database functions such asDSUM. - Outside Google Sheets mode,
TEXTunder the General format now writes five significant digits, not six, at an exponent of 99 or more in magnitude, as Excel does. TEXTunder the General format now writes1E+15, not10E+14, when rounding carries, as Excel does.TEXTunder a date format now shows the right day for a time just short of midnight, as inTEXT(0.99999999999,"yyyy-mm-dd"), as Excel does.- Outside Google Sheets mode, converting a number to text, as
&does, now rounds a number exactly halfway between two 15-digit values toward zero, as Excel does:123456789012345.5&""now gives123456789012345. FIXEDnow honours itsno_commasargument.FIXEDandDOLLARnow return#VALUE!for more than 127 decimals, as Excel does, and, outside Google Sheets mode, for a result longer than 255 characters.- Outside Google Sheets mode,
TEXTnow returns#VALUE!where the result or the format is too long, or the format has more than 127 decimal places, as Excel does. - In Google Sheets mode,
FIXEDwith an emptydecimalsargument now uses 0 decimals, as Google Sheets does. - In Google Sheets mode,
DOLLARnow writes a negative number with a leading minus, not in parentheses, as Google Sheets does. - In Google Sheets mode,
FIXED,DOLLARandTEXTnow write numbers near the largest representable number as Google Sheets does, not as∞or a#NUM!error. - In Google Sheets mode,
TEXTnow rounds a number to 15 significant digits as Google Sheets does in a few more cases, such as just below 1. TEXTwith an empty format, such as""or a blank cell, now returns empty text for a number, as Excel and Google Sheets do, rather than the number in General format. Outside Google Sheets mode, it returns-for a negative number, as Excel does.- Outside Google Sheets mode,
ENCODEURLnow returns#VALUE!where its result would be longer than 32766 characters, as Excel does. NUMBERVALUEnow reads a comma that is neither of its separators as a group separator before three or more digits, as Excel does:NUMBERVALUE("1,500","a",".")is now 1500 rather than#VALUE!.- Outside Google Sheets mode,
DECIMALnow skips leading spaces and tabs in its text, and returns#VALUE!rather than#NUM!for text over 255 characters, as Excel does. - Outside Google Sheets mode,
DATEVALUEand other conversions of date text now read a date with spaces on either side of a-or/separator, such as2026 -01-01or4 - Feb - 26, as Excel does. - Outside Google Sheets mode,
VALUE,NUMBERVALUE,DATEVALUE,TIMEVALUEand other conversions of text to a number, date or time now return#VALUE!for text longer than 255 characters, spaces included, as Excel does. TEXTSPLITof empty text, or of a blank cell, now returns#VALUE!, as Excel does.TEXTSPLITnow returns an error among its delimiters, as Excel does, rather than splitting at the error’s name, or returning#VALUE!for a blank delimiter cell that comes after the error.- In Google Sheets mode,
SPLITnow converts a part with leading spaces to a number or date, as Google Sheets does.
- Outside Google Sheets mode, a number converted to text now has 14 significant digits, not 15, at an exponent of 99 or more in magnitude, as Excel does: in
- Math functions:
PI()now returns pi to full double precision, as Excel does, which corrects trigonometric functions near multiples of pi/2, such asTAN(PI()/2-1E-12), and the last digits ofDEGREES,RADIANSandSQRTPI.ACOTnow keeps its precision for large arguments, such asACOT(9999999999999).- Outside Google Sheets mode,
10^kandPOWER(10,k)now match Excel in the last digit for a negative integerkand forkof 126, and so doesx^kfor any otherxand an integerk. - Results that scale by a power of ten, such as those of
ROUND,ROUNDUP,ROUNDDOWN,DOLLARDE,DOLLARFR,PERCENTRANK,POWERand10^k, are now the same on Node 22 as on later Node versions, rather than sometimes differing in the last digit. SUBTOTALandAGGREGATEwith function 6 (PRODUCT) now return 0 over cells with no numbers, as Excel does, rather than#VALUE!.- In Google Sheets mode,
ROUNDnow rounds numbers of 15 or more significant digits as Google Sheets does, including near the largest representable number. - In Google Sheets mode,
=and other comparisons now find numbers near the largest representable number equal where Google Sheets does.
- Financial functions:
CUMPRINC,CUMIPMT,PPMTandIPMTnow return Excel’s results where(1+rate)^nperoverflows; previously they returned wrong results or, over many periods, never finished.PPMTandIPMTno longer lose precision where the principal is a small part of the payment, or return#NUM!for a number of periods between 0 and 1.- In Google Sheets mode,
CUMPRINCwith a fractional start or end period now includes the last period, as Google Sheets does.
- Outside Google Sheets mode,
TBILLEQ,TBILLPRICEandTBILLYIELDnow match Excel in the following cases:- They now allow a maturity up to the same date a year after settlement, with 29 February going to 1 March; previously some settlements before March 1900 or on 29 February returned
#NUM!. - When the date a year after settlement would be later than 9999-12-31, they now return
#NUM!. - When the discount makes the price exactly 0,
TBILLEQ(over a term of up to 182 days) andTBILLPRICE(over any term) now return 0 rather than#NUM!. TBILLEQover a term of exactly 366 days, which spans a 29 February, is no longer about 0.3% low.
- They now allow a maturity up to the same date a year after settlement, with 29 February going to 1 March; previously some settlements before March 1900 or on 29 February returned
- Statistical functions:
- Outside Google Sheets mode,
CORRELnow returns#NUM!where a sum of squares overflows, rather than 0, and elsewhere now matches Excel to the last digit. - Outside Google Sheets mode,
SLOPE,INTERCEPT,FORECAST,FORECAST.LINEAR,COVARIANCE.P,COVARIANCE.SandCOVARnow treat values too small to represent in full as 0, as Excel does. - Outside Google Sheets mode,
SLOPE,INTERCEPT,FORECASTandFORECAST.LINEARnow return#NUM!where the values are spread too widely to compute with, as Excel does. - In Google Sheets mode,
CORRELnow returns#NUM!where its result is not a number, as Google Sheets does, rather than#DIV/0!. - In Google Sheets mode,
SLOPE,INTERCEPT,FORECASTandFORECAST.LINEARnow return#N/Awhere no pair of values are both numbers, as Google Sheets does, rather than#DIV/0!. NORM.S.INV,NORM.INV,LOGNORM.INVand their older forms are now accurate for probabilities far from 0.5, where they could previously be wrong from the third digit. Outside Google Sheets mode,NORM.DIST,NORM.S.DISTandLOGNORM.DISTnow return tiny lower-tail probabilities, down to about 2.2E-308, instead of 0.NORM.DISTandNORM.S.DISTnow return#NUM!, as Excel does, where the standardized value overflows, or for the density where its square does.- Outside Google Sheets mode, the density of
NORM.DIST,NORM.S.DISTandLOGNORM.DISTnow returns 0 where it is below the smallest normal double, about 2.2E-308, as Excel does. - The t, F and chi-squared distribution functions and their inverses (
T.DIST,T.INV,F.DIST,F.INV,CHISQ.DIST,CHISQ.INV, their right- and two-tailed forms and their older forms) are now accurate far into the tails and at huge degrees of freedom, where they could previously be wrong from the second digit, return#NUM!or take seconds. T.DISTwithcumulativeTRUE,T.DIST.RT,T.DIST.2T,TDIST,T.INV,T.INV.2T,TINV,F.DIST,F.DIST.RT,FDIST,F.INV,F.INV.RTandFINVnow return#NUM!for degrees of freedom above 1E10, as Excel does.CHISQ.INV.RTandCHIINVnow accept degrees of freedom above 1E10, and, outside Google Sheets mode, return#NUM!as Excel does in a range between about 1E9 and 5E10 that depends on the probability. Above that range they return the true quantile, where Excel returns about 1.000266 times it.- Outside Google Sheets mode,
CHISQ.INV.RTandCHIINVnow return#NUM!at very small probabilities, as Excel does: below about 6E-17 at 1 degree of freedom, and, from 3 degrees of freedom up, below a limit that falls as the degrees of freedom rise, about 1E-21 at 3 and 1E-105 at 15. - The density of
T.DISTnow returns#NUM!where the square of itsxoverflows, as Excel does. - Outside Google Sheets mode,
CHISQ.INVnow returns 0 where its quantile is below the smallest normal double, about 2.2E-308, as Excel does. - Outside Google Sheets mode,
T.DIST,T.DIST.RT,T.DIST.2T,TDIST,F.DIST,F.DIST.RTandFDISTnow return 0 where the result is below that double, as Excel does. - Cumulative
BINOM.DIST,BINOMDISTandNEGBINOM.DISTare now accurate far into the tails and at many trials, where they were previously rounded to 10 decimals, returned 0 for tiny probabilities or returned#NUM!. - Outside Google Sheets mode, the density of
NEGBINOM.DISTandNEGBINOMDISTis now accurate at many trials, where it previously returnedFALSE,#NUM!or 0. As in Excel, it now returns#NUM!wherenumber_sis above about 9E15 timesnumber_f, and#DIV/0!where the sum of the two overflows. - The densities of
BETA.DIST,GAMMA.DIST,GAMMADIST,WEIBULL.DISTandWEIBULLat the bounds of their support, such asx= 0, now return 0 or#NUM!where Excel does. GAMMA.INV,GAMMAINV,BETA.INVandBETAINVare now accurate for probabilities far into the tails, where they previously returned values far from the quantile; outside Google Sheets mode, a quantile below about 2.2E-308 is 0.BETA.INVandBETAINVnow return#NUM!at extreme alpha and beta, as Excel does. Outside Google Sheets mode,GAMMA.INVandGAMMAINVnow return#NUM!as Excel does in a range of huge shapes that depends on the probability.PERCENTRANK,PERCENTRANK.INCandPERCENTRANK.EXCnow check for asignificancebelow 1 only after their data andx, as Excel does, so that with such asignificance, a single value equal toxreturns 1, and anxoutside the data, or an error in the data, returns#N/Aor that error rather than#NUM!.STDEV,VARand their variants now measure deviations from the mean as computed, keeping its rounding error, as Excel does, so that equal values whose mean is not exact, such as three copies of1E30, have the same small nonzero spread as in Excel. So doDSTDEV,DVARand their variants, andSUBTOTALandAGGREGATEwith functions 7, 8, 10 and 11 (the standard deviations and variances).KURTandZ.TESTnow take their standard deviation the same way.
- Outside Google Sheets mode,
- Date and time functions:
- Outside Google Sheets mode,
WEEKDAYandWEEKNUMnow read a fractionalreturn_typeas Excel does: truncated, unless within about 2.4E-7 of an integer. - Outside Google Sheets mode,
WEEKNUMnow returns#VALUE!for a logical value in a referenced cell or for date text with a year past 9999, and checks the date beforereturn_type, as Excel does. - In Google Sheets mode,
WEEKDAYandWEEKNUMnow truncate a fractionalreturn_type, andWEEKNUMnow reads a logicalreturn_typeas 0 or 1, as Google Sheets does. WORKDAYandWORKDAY.INTLwith a day count of 0 now return the start date without checking theweekendandholidaysarguments, as Excel does, soWORKDAY(45000,0,"test")is 45000.WORKDAY.INTLwith an invalidweekendargument and a single error, non-date text or logical value asholidays, given directly or in a single cell, now returns that error, or#VALUE!for the text or logical value, as Excel does, rather than#NUM!.
- Outside Google Sheets mode,
Performance
Section titled “Performance”- Loading a workbook with many defined names whose formulas are neither a constant nor a plain reference is now much faster; previously its load time grew with the square of their count.
Model.readCellsandModel.evaluateExpressionwithcropTo: 'cells-with-non-blank-values'are now much faster on an expression that evaluates to a tall array, such as=B:B*1over a column with data far down.- Recalculating a formula that reads many cells through
OFFSETorINDIRECT, such as in aMAPorBYROWlambda, is now faster: its time grows linearly with the number of cells read, not with its square. - Recalculation is faster where formulas repeat an aggregate such as
SUMorAVERAGEover the same range, as inC2/SUM(C$2:C$5001)filled down (outside Google Sheets mode), or in array formulas repeating a larger expression around it, as inG2-SUM(…)in every row: the aggregate is now computed once rather than once per row. BYROW,BYCOLandMAKEARRAYnow call theirLAMBDAwith less overhead, which makes them several times faster when thatLAMBDAis cheap.
Documentation
Section titled “Documentation”- The description of the
iterativeCalculationload option now says that omitting it uses the settings stored in the workbook rather than disabling iteration.
v18.3.2 (2026-10-30)
Section titled “v18.3.2 (2026-10-30)”Bug fixes
Section titled “Bug fixes”- Defined names:
- A chain of sheet-scoped names, each listed before the name it reads, now evaluates for the cell that reads it. For example, with
alpha=Sheet1!beta+1,beta=Sheet1!gamma+1andgamma=ROW(), all scoped to Sheet1,=alphain A6 is now 8; previously it was#VALUE!. Outside Google Sheets mode, a reference without$at the end of such a chain, such asSheet1!B1, is now read relative to that cell; previously Apiary read it relative to A1. Excel lists names alphabetically, so a file it saves can have names in this order. - Outside Google Sheets mode, a cell that reads a relative name (one with a reference without
$, such asSheet1!B1) through other names now recalculates when the cell that name points to changes; previously it kept its old value. - A name prefixed with a sheet or workbook that the model does not have, such as
=Nope!total, now returns#NAME?, or#REF!for the bracketed=[other.xlsx]!total; previously Apiary read the like-named name in the referencing workbook.
- A chain of sheet-scoped names, each listed before the name it reads, now evaluates for the cell that reads it. For example, with
- A cell holding the text
#N/A,#REF!or another error name now exports to XLSX, JSF and CSF as text; previously it exported as an error value. - Sorting a range, and
Model.rewriteFormulaAfterMovewith a destination in another workbook, no longer drop the directory from references into another workbook, such asC:\path\in'C:\path\[Book.xlsx]Sheet1'!A1. - With iterative calculation on:
- With a maximum of one iteration, a data table that reads another data table’s results through a cell that depends on a circular reference now computes its first result from that cell’s current value; previously it used a stale one.
- A formula that reads a cell inside the spill of a formula in a circular reference, other than the spill’s first cell, now recalculates with the circular reference; previously it kept a stale value.
- A formula in A1 such as
=SHEET(1+A1)or=INDEX(1+A1,1)is now a circular reference and evaluates to 0, as in Excel; previously these returned#N/Aand 1. The same goes for a formula that reads its own cell insideSHEETS,ISREF,ROWand similar functions. - A formula that returns part of another formula’s spill as an array, such as
=C5:D6, now returns that spill’s values. Previously it could return blanks: with A5 =TAKE(3:4+{10;20},,4)and only A3:B4 filled in rows 3:4,=C5:D6spilled blanks instead of 10 and 20. TAKEandDROPover a whole-column or whole-row calculation now return the right values past the cells that have values: with values only in B1:C2,=TAKE(B:C+{10,20},-2)now returns{10,20;10,20}; previously it returned 20 in every cell.- Where a single value is expected, a 3-D reference that comes out of
IF,CHOOSE,IFERROR,IFNA,LETorREDUCE, as in=IF(TRUE,Jan:Mar!A1,0), or that is bound to aLETvariable, is now#VALUE!rather than#REF!, as in Excel, and an enclosingIFERRORno longer catches it. - A
LAMBDAparameter orLETvariable nested in aLAMBDAbody now shadows an outer parameter of the same name, so=LAMBDA(x,LAMBDA(x,x*2)(3)+x)(10)is 16 rather than 30. IFERRORandIFNAnow pass on a fallback lambda, as Excel and Google Sheets do:MAP({1;2},IFERROR(1/0,LAMBDA(x,x*10)))now spills 10 and 20; previously it was#CALC!or#VALUE!. A cell whose result is the fallback lambda is still an error.- Pivot tables:
- A file with two same-named pivot tables on one sheet now loads both, renaming the second and saying so in a
duplicate_pivot_namewarning; previously the second overwrote the first’s output. - A pivot table set to refresh on load, in a workbook loaded from JSF, now reads the formula cells in its source correctly; previously it read them as blank, so its totals were wrong and a field with a formula header could go missing.
- If refreshing a pivot cache on load fails unexpectedly, the workbook now gets a
pivot_refresh_on_load_failedwarning; previously the failure went unreported. - A label, value or top-N filter on a report filter field, or on a field removed from the pivot table, now has no effect, as in Excel; previously it filtered the pivot table.
- % of Parent Row Total and % of Parent Column Total over a value field summarized by something other than Sum, such as Max or Average, now divide each item by its parent’s own Max or Average, as Excel does, rather than by the sum of the parent’s items.
- Pivot table fields now sort month and weekday names ahead of other text, in calendar order, as Excel does.
- Refreshing a pivot table, including on load, now re-fits its column widths when its
useAutoFormattingandapplyWidthHeightFormatsattributes are both on, as Excel does. Export now leaves out either attribute where the original lacked it, so that Excel keeps the column widths on refresh. - Inserting, deleting or moving rows or columns, and
Workbook.insertCellsanddeleteCells, no longer discard the saved values of a loaded pivot table elsewhere on the sheet.
- A file with two same-named pivot tables on one sheet now loads both, renaming the second and saying so in a
- Outside Google Sheets mode, arithmetic now matches Excel in the following edge cases:
- Unary minus now leaves a positive number below 2^-1026 (about 1.4E-309) unchanged, and
SIGNnow returns 0 for it. POWERand^now return#NUM!for a negative base raised to an exponent of 2^32-1 (4294967295) or more in magnitude, and so doSERIESSUM,FVandPVin the matching cases.MODnow matches Excel where the quotient is large or the dividend tiny:MOD(-1E-20,1)is now 0; previously it was 1.
- Unary minus now leaves a positive number below 2^-1026 (about 1.4E-309) unchanged, and
- In Google Sheets mode, the following now behave as in Google Sheets:
AND,ORandXORnow return the first error among their arguments, even one that follows text that would otherwise make them return#VALUE!.LEN,TEXTJOINandJOINnow take a number as a cell displays it:LEN(1E+20)is now 5 rather than 21.0^0,POW(0,0)andPOWER(0,0)now return 1 rather than#NUM!.- A lambda used as a value, as in
=LAMBDA(x,x*10)in a cell,LAMBDA(x,x)+1orISERROR(LAMBDA(x,x)), is now#N/A; previously these returned#CALC!,#VALUE!and FALSE.
- When a selection has ranges but no
activeCell,WorkbookView.getSelectionandsetSelectionnow takeA1as the active cell if a range contains it, else the first range’s top-left cell, as Excel does. PreviouslygetSelectionalways reportedA1, andsetSelectionalways took the first range’s top-left cell.
Performance
Section titled “Performance”- Loading a workbook is faster: finding the dependencies of its formulas takes less time.
v18.3.1 (2026-10-28)
Section titled “v18.3.1 (2026-10-28)”Bug fixes
Section titled “Bug fixes”- Recalculation, data tables and spills:
- In a workbook loaded in manual calculation mode, a formula cell with no saved value now holds an empty string until a recalculation reaches it, as in Excel; previously its value was
null. - Turning
Model.deferDataTablesoff, or setting a workbook’scalcModefrom"autoNoTable"to"auto", now immediately recomputes the data tables left stale while deferred, as Excel does; previously they stayed stale. SettingcalcModeto"manual"instead leaves them to the next recalculation, which now recomputes them. - A dynamic array formula that was blocked by a value now spills again once that cell is written back to empty, as it already did when the cell was cleared.
- In a workbook loaded in manual calculation mode, a formula cell with no saved value now holds an empty string until a recalculation reaches it, as in Excel; previously its value was
- Outside Google Sheets mode, a defined name holding a relative reference with a sheet prefix, such as
Sheet1!B1rather thanSheet1!$B$1, now resolves relative to the cell that uses the name, as in Excel, rather than to the address as written. - References are now written with the sheet or workbook name quoted where Excel quotes it:
- A sheet name containing a period, on its own, as in
'TS1.5'!B7, or as the last sheet of a sheet span, as in'Feb:R1X.5'!A1. - A sheet or workbook name containing a character above U+00AF that Excel accepts only inside quotes, such as
». - A sheet name containing a period, such as
TS1.5,RC.xorSheet1.x.y, in a reference rewritten by renaming a sheet or moving cells. - A new sheet name that needs quotes, such as
TRUE, after a range operator with whitespace before its colon, as inAlpha :'TRUE'!A1, in a reference rewritten by renaming a sheet or moving cells to another sheet.
- A sheet name containing a period, on its own, as in
- Formulas now accept the following references, as Excel does:
- A range from a cell to a whole column or row, such as
SUM(B2:$B:$B)orSUM(A1:B:B), which now spans the whole columns or rows of both ends; previously it evaluated to#NAME?. - A structured reference whose column name contains a backtick, as Excel writes it, such as
W[[a`b]]orW[@[a`b]].
- A range from a cell to a whole column or row, such as
- References in formulas are now rewritten as Excel does when cells are moved, rows or columns are inserted or deleted, or a sheet is renamed:
- Moving cells so that one edge of a range lands inside that range now adjusts references to the range; previously they became
#REF!or pointed at the wrong rows or columns. - Moving part of a range to another sheet now contracts references to the range past the rows or columns moved, on the sheet they read; previously the whole reference moved to the other sheet.
- Renaming a sheet whose name starts with a digit, or moving cells off one, now keeps a range such as
MyName:1!A1a range when a line break comes before the name; previously it became a 3-D reference. - In Google Sheets mode, a row insert, delete or move now moves the starting row of a partial column range such as
C2:D, as Google Sheets does, and so does inserting or deleting cells across its columns; column edits move a partial row range’s starting column the same way.
- Moving cells so that one edge of a range lands inside that range now adjusts references to the range; previously they became
LETvariables andLAMBDAparameters now behave as in Excel in the following cases:- A
LETvariable orLAMBDAargument bound to another name, asyinLET(x,1,y,x,y), now takes that name’s value where it is bound, not#NAME?or whatever the name means where the variable is used. - A lambda body now reads only the
LETvariables bound before the lambda is defined: not those bound after it, nor those in scope where it is called, so a lambda in a defined name no longer sees theLETvariables of a formula that calls it. - A formula that calls a lambda bound to a
LETvariable, or returned by aLETinside anIF,CHOOSEor other expression, or that passes a lambda in a defined name to another lambda, asreadB1inLAMBDA(fun,SUM(MAP(A1:A2,fun)))(readB1), now recalculates when a cell that the lambda body reads changes. - A
LAMBDAparameter used as a range endpoint or an intersection operand, as inLAMBDA(from,to,SUM(from:to)), now takes its argument instead of evaluating to#NAME?. A range between twoLETorLAMBDAvariables, such asa:binLET(a,A5,b,B5,SUM(a:b)), now spans the cells they name; previously Apiary reada:bas the columnsA:B, which a column insert then rewrote. - A
LETvariable namedrorcnow takes its value instead of evaluating to blank. ALETvariable orLAMBDAparameter named like a cell address, such asA1, now takes its value instead of evaluating to#NAME?, and the other cell addresses in a formula that binds such a name evaluate to#NAME?. Apiary does not support an address that is also an R1C1 reference, such asC2, in such a formula.
- A
- Functions given a lambda as a value now treat it as Excel does:
ERROR.TYPEof a lambda, such asERROR.TYPE(LAMBDA(x,x*10)), now returns#N/Arather than 3.IFwith a lambda as its condition, such asIF(LAMBDA(x,x),1,2), now returns#VALUE!instead of taking the first branch.COUNTAnow counts lambdas.SWITCHover an array of lambdas now takes its default for each, as it does for a single lambda, instead of spilling#CALC!.- Outside Google Sheets mode,
FILTERwith a lambda in its include argument now returns that lambda instead of#VALUE!, soLET(f,FILTER({1;2},LAMBDA(x,x*10)),f(5))is 50.
- Pivot table measures shown with Show Values As now match Excel in these cases:
- A measure shown as Difference From, % Of, % Difference From, Running Total In, % Running Total In or Rank with a base field on neither axis now shows
#N/Ain every cell; previously its subtotals showed raw sums and its other cells were empty. - A measure shown as Difference From, % Of or % Difference From with a hidden fixed base item now shows
#N/Ain the subtotals on both axes; previously those across from the base field showed raw sums. - In a measure shown as Running Total In, % Running Total In or Rank, the grand-total cell of a subtotal nested inside the base field now accumulates or ranks like the cells beside it; previously it showed 0,
#DIV/0!or a tied rank. - In a measure shown as Rank, a grand-total cell whose value is an error or empty now shows empty, and the grand-total cells beside it now rank without it; previously it ranked as 0, which could shift their ranks.
- A measure shown as Difference From, % Of, % Difference From, Running Total In, % Running Total In or Rank with a base field on neither axis now shows
- Number formatting and parsing:
- Number formats now round as Excel does:
0.00shows 1.005 as1.01, andGeneralshows 4403928373.5 as4403928374. - Number formats now write numbers of 1E+21 or more in full:
#,###shows 2^70 as1,180,591,620,717,410,000,000rather than a garbled1,.18,059,162,071,741,13e,+21. - In a format that shows the time, a time that rounds up to midnight now shows the next day’s date.
- In a format with a locale tag, month and day names now match Excel in many locales.
- Text with a pound, yen or rupee sign no longer converts to a number, as in Excel and Google Sheets with US English settings:
VALUE("£6182")is now#VALUE!. - A number literal in a formula with more than 15 significant digits now reads as its first 15 digits followed by zeros, as in Excel and Google Sheets:
1*(12345678901234567-12345678901234500)is now 0; previously it was 68. - In Google Sheets mode,
TEXT, andTEXTJOINandJOINshowing a number in its cell number format, now round a tie at the 16th significant digit away from zero, as Google Sheets does:TEXT(10^15+5,"0")is1000000000000010.
- Number formats now round as Excel does:
- In Google Sheets mode, the following functions now return
#NAME?, as they do in Google Sheets, andfunctionSignaturesno longer lists them in that mode:- the dynamic-array functions
DROP,EXPAND,SORTBY,TAKEandTRIMRANGE TEXTAFTER,TEXTBEFOREandTEXTSPLITAGGREGATE,AMORDEGRC,ECMA.CEILING,INFO,ISOMITTEDandUSDOLLAR
- the dynamic-array functions
CHOOSE,IFERROR,IFNAandIFnow handle an omitted argument and a blank cell as Excel does:CHOOSEnow returns a chosen omitted argument as blank, not 0, and passes it on, soISOMITTED(CHOOSE(1,))isTRUE.IFERRORandIFNAnow pass on an omitted value too, not only an omitted fallback.- Outside Google Sheets mode, where
CHOOSEhas an array index, orIFERRORorIFNAan array or a range of more than one cell as first argument, a blank cell from any of their arguments, or an omittedIFERRORorIFNAfallback, is now 0 in the result instead of blank. CHOOSEwith a 1x1 array index such as{1}now returns an array, as with a larger array index, instead of the chosen argument.- In GRID sheet and Canvas modes, an
IFwith an array condition now returns 0 rather than blank when the element it chooses is a blank cell or an omitted argument, as it already did in Excel mode.
- Text functions:
- Outside Google Sheets mode,
CHARandCODEnow use Windows Excel’s character set for codes 128 to 159, soCHAR(128)is € rather than an invisible control character, andCODE("€")is 128 rather than 63. - Outside Google Sheets mode,
LEN,MID,FIND,SEARCHandREPLACEnow count a character above U+FFFF, such as an emoji, as one character, as Excel does. UNICODEof text starting with a lone high surrogate (the first half of a character above U+FFFF, whichMIDBcan return) now returns#VALUE!rather than the surrogate’s code unit, as Excel does.SEARCHBnow finds a match that starts at the low surrogate (second half) of a character above U+FFFF, asFINDBand Excel do.TEXTSPLITnow returns#VALUE!at once for any empty delimiter, a blank cell in a delimiter range included, as Excel does. Previously it took seconds and gigabytes of memory to return#VALUE!for some, and returned a value for others, as inTEXTSPLIT(",,,",{",",""}).
- Outside Google Sheets mode,
- Math and engineering functions:
- Outside Google Sheets mode,
SUMPRODUCTnow returns the first error in the first array that has one, as Excel does, rather than an error in a later array or, for arrays that differ in size,#VALUE!. - Outside Google Sheets mode,
MROUND,QUOTIENT,ISODD,ISEVEN,COMPLEXandCONVERTnow return#VALUE!for a boolean in a numeric argument, andMROUND,QUOTIENT,COMPLEXandCONVERTnow return#N/Afor an omitted numeric argument, as Excel does. - In Google Sheets mode, the following now count a boolean as 1 or 0, as Google Sheets does, where they previously returned
#VALUE!: the arguments ofDELTA,GESTEP,ERF,ERFC,QUOTIENT,ISODDandISEVEN; the number argument ofDEC2BIN,DEC2OCTandDEC2HEX; and the places argument of the base-conversion functions, such asDEC2BINandHEX2OCT. - In Google Sheets mode,
ERFnow reads an omitted upper limit as 0, as Google Sheets does, where it previously returned#N/A.
- Outside Google Sheets mode,
BESSELKandBESSELYnow match Excel at these edges:BESSELKandBESSELYnow return#NUM!for an order above 10 million instead of taking seconds or returning a value.BESSELKnow returns 0 for x above about 705.117, as Excel does at orders 0, 1 and 10 million, instead of a tiny nonzero value or#NUM!.BESSELKnow matches Excel at orders 2 and up for x near 700, where it was previously off by up to a factor of 10^24.BESSELYat orders 2 and up now matches Excel more closely, where it could previously differ in the last digit or two.
GCDandLCMnow match Excel and Google Sheets at these edges:LCMnow returns 0 rather than#NUM!when more than one of its arguments is zero, as inLCM(0,0).- Outside Google Sheets mode,
GCDandLCMnow return#NUM!for an argument orLCMresult above 2^53 (9007199254740992), and#VALUE!when every argument is a blank cell, apart from any omitted ones. - In Google Sheets mode,
GCDandLCMnow match Google Sheets for arguments, orLCMresults, above 2^53.
- Complex-number functions:
- IM functions such as
IMSQRTandIMDIVnow write each part of their result with 15 significant digits, as Excel does, soIMSQRT("5+12j")is3+2jrather than2.9999999999999996+2j. IMPOWER(0,0)is now#NUM!rather than 1, a blank power now counts as 0 rather than giving#VALUE!, and an error in the power, such as#DIV/0!, is now returned rather than#VALUE!.IMCOTof a tiny real number such as"1E-16"now returns a number rather than#NUM!; in Google Sheets mode it andIMCOT(0)now return#DIV/0!, as in Google Sheets.IMCOT,IMSECandIMPOWERnow match Excel’s last digit in more cases, such asIMSEC(1.5)andIMPOWER("8-2j",9.2).- In Google Sheets mode,
IMLNnow writes its result with suffixifor ajargument, as Google Sheets does.
- IM functions such as
- Outside Google Sheets mode, the following functions now return
#NUM!, as Excel does, where the first error in their arrays is#NULL!or#GETTING_DATA, which they previously returned as is:CORRELCOVARIANCE.P,COVARIANCE.SandCOVARSLOPEandINTERCEPTFORECASTandFORECAST.LINEARSUMXMY2,SUMX2MY2andSUMX2PY2PROB
- Bond functions:
- Under basis 0 (US 30/360),
COUPDAYBS,COUPDAYSNC,PRICEandYIELDnow match Excel when a coupon or settlement date falls on the last day of February. - Under basis 1 (actual/actual),
COUPDAYSnow matches Excel on more bonds whose coupon dates fall near the end of a month, and where the coupon period starts before March 1900. - Under bases 2 (actual/360) and 3 (actual/365),
PRICEandYIELDnow match Excel. PRICEandYIELDnow match Excel when settlement falls within the last coupon period.YIELDnow solves to full precision.PRICEandYIELDnow return#NUM!for very large yields where Excel does, rather than a number.YIELDnow returns a negative yield for a price above what the security would fetch at a zero yield, instead of about 0.PRICEMATandYIELDMATnow match Excel under basis 1.DURATIONandMDURATIONnow match Excel under every basis.
- Under basis 0 (US 30/360),
- Date and time functions:
- Outside Google Sheets mode,
DAY,MONTH,YEAR,WEEKDAY,ISOWEEKNUM,DAYS,DAYS360,NETWORKDAYS,NETWORKDAYS.INTL,WORKDAY,WORKDAY.INTLandWEEKNUMnow return#NUM!for a date serial or holiday below 0 or past 9999-12-31 (serial 2958465), as Excel does. DAYSnow counts whole days, soDAYS(43832.233,1)is 43831 rather than 43831.233.DAY,MONTH,YEARandWEEKDAY, and outside Google Sheets modeDAYS360, now round the serial to the nearest second before taking its date, as Excel does, soDAY(DATE(2025,1,1)+0.9999999)is 2.
- Outside Google Sheets mode,
- In Google Sheets mode,
VSTACKandHSTACKnow keep an error argument as one element of the stack, as Google Sheets does:VSTACK(NA(),1)spills#N/Aand 1 rather than one#N/A.
Performance
Section titled “Performance”- Recalculating a data table whose results read many individual cells through dynamic references, such as
OFFSETorINDIRECT, is substantially faster. - Recalculation is faster where many formulas read the same large range, such as
SUMIFSformulas over one table. - Formula parsing is slightly faster.
- The following functions are now much faster over a whole column or row, or another range with many blank cells at its end:
PRODUCTCORREL,COVAR,COVARIANCE.PandCOVARIANCE.SCONCAT,CONCATENATE, andTEXTJOINwith every delimiter emptyMATCHwith match type 1
v18.3.0 (2026-10-22)
Section titled “v18.3.0 (2026-10-22)”Features
Section titled “Features”- New functions:
HARMEAN,REGEXTEST,SHEET,SHEETSandTRIMMEAN, and Excel’sREGEXEXTRACTandREGEXREPLACE. ADDRESSnow supportsa1= FALSE to return an R1C1 reference, such asR5C3orR[-1]C; previously it would return#VALUE!.Model.unsupportedFunctions()lists functions used in the model which Apiary cannot evaluate, each with the cells and names calling it, andModel.cellsWithoutImplementationholds the cells keeping their cached values.Workbook.editCellsedits several cells in one call, with the same checks aseditCell. If any of the edits would be refused, such as a write into a merged non-anchor cell or a pivot table’s output, no cell is changed. Defined names and what-if data tables are still edited one at a time witheditCell.
API changes
Section titled “API changes”Model.recalculateacceptsincludeStaleDeferred: true. It recalculates the deferred cells asincludeDeferreddoes, but recomputes only those data tables that were already stale or that the recalculation dirties, each once.includeDeferredstill recomputes every data table.- A formula cell’s
ft(formula type) can now be'cse', for a legacy Ctrl-Shift-Enter array formula, or'plain', for a formula loaded from a file without an array mark. In an Excel workbook,nullnow means a dynamic-array formula, and an edit that sets a formula leavesftnullinstead of inferring'a'. - New
Workbook.setArrayFormula(range, formula)creates a legacy Ctrl-Shift-Enter array formula over a fixed range, andWorkbook.editCellnow throws when asked to create one. - Model serialization is now version 11; payloads serialized by earlier versions still load.
Bug fixes
Section titled “Bug fixes”- A cell calling a function Apiary cannot evaluate, such as an add-in or user-defined function, now keeps the value its file cached when it loads, instead of recalculating to
#NAME?and passing that on to its dependents. As in Excel, it is recalculated, with the call evaluating to#NAME?, once a change reaches it from a cell it depends on, rows, columns or cells are inserted or deleted on its sheet, or the model is recalculated in full. - An unqualified name in a defined name’s formula, such as
fing==f, now refers to the workbook-levelfwherevergis used, as in Excel, and is#NAME?rather than#REF!if there is none. Before, Apiary could resolve it to a sheet-localfinstead, such as one on the calling cell’s sheet. To refer to a sheet-local name there, qualify it (Sheet2!f). - A workbook now keeps its
type,id,update_time,versionandcloud_connectionthroughserializeModelanddeserializeModel, and an Excel or Google Sheets workbook now recalculates in its own mode afterwards. - Numbers now compare as in Excel and Google Sheets:
- The comparison operators (
=,<>,<,<=,>and>=), the criteria ofCOUNTIF,SUMIF, the other*IFand*IFSfunctions and the database functions, and the cases ofSWITCHnow treat two numbers that agree to 15 significant digits as equal. In Google Sheets mode, so do lookups,SORTandSORTBY. - Outside Google Sheets mode,
XLOOKUP,XMATCH,SORTandSORTBYnow compare numbers exactly, andWorkbook.sortCellsdoes so in every mode. - In Google Sheets mode, comparisons, criteria,
SWITCH, lookups,SORTandSORTBYnow treat a nonzero number smaller in magnitude than 2^-1022 (about 2.2E-308) as 0.
- The comparison operators (
- A formula’s numeric result is now stored as the double it computed, as in Excel, rather than rounded to 15 significant digits, so a result below about 1E-294 or near the largest double no longer becomes 0 or infinite.
- Outside Google Sheets mode, a result smaller in magnitude than about 2.2E-308 is now 0, as in Excel, when it comes from
*,/,%or^, or from a product or quotient inside a function such asPRODUCT,SUMPRODUCTorAVERAGE; a sum still keeps it. - A
+or-whose result is some 15 digits smaller than its left operand now rounds it to exactly zero only when it is the last operation of a formula, a defined name’s formula or aLAMBDAbody, as in Excel:=1+2^-52-1is still 0, but=1+2^-52-1+0is now 2.22044604925031E-16 and=1+2^-52-1=0is now FALSE.MMULTno longer rounds a near-zero sum to zero, and in Google Sheets mode no+or-does. - In Google Sheets mode,
&and other conversions of a number to text now match Google Sheets just below a power of ten: 9.99999999999999E-5 now becomes0.0000999999999999999, not0.0001, and in&andCONCATENATE, 9.99999999999999E+4 becomes100000., ending with a point as Google Sheets does at any exponent below 14. - Array and non-array formulas now behave as in Excel:
- A formula loaded from a file without an array mark now evaluates by Excel’s rules for such formulas: an array result shows only its first value and does not spill, and a range where a single value is expected is intersected with the formula’s row or column. So
LOOKUP(2,1/(A1:A9<>""),B1:B9)now finds the last match. This also holds for a model serialized by an earlier version. - A legacy Ctrl-Shift-Enter (CSE) array formula now keeps its fixed output range, and is written back out as a CSE array formula. Previously it was read as a dynamic-array formula. A model serialized by an earlier version holds such a formula as a dynamic-array formula.
- In an Excel workbook, an entered formula now evaluates as a dynamic-array formula in every case, so a formula such as
LET(x,C1:C2,x*10)now spills. - An entered formula that an array reaches only indirectly, such as through
LET,LAMBDAor an array-valued defined name, is now exported as an array formula, so it no longer collapses to a single value when the file is reopened. - Filling a formula loaded without an array mark, or a legacy array formula, now makes copies of the same kind.
- A single-cell array formula, such as
SUM(C2:C3*1)saved by Excel as a dynamic-array formula, now stays an array formula when inserting or deleting rows, columns or cells rewrites its references. - A single-cell array formula moved by inserting or deleting rows or columns,
moveCellsorreorderRowsnow exports its recalculated value, and one moved bymoveCellswhose spill shrinks to a single value now exports that value and leaves its former spill cells blank. - Between a move that defers recalculation, such as
moveCells(from, to, false), and the next recalculation,clearCellsandcopySheetno longer throw on a moved array formula,copySheetnow copies its value, and a value written over it now exports as that value instead of the formula’s old one. - An array formula referencing a blank cell with a number format now results in 0 in an Excel workbook.
- A formula loaded from a file without an array mark now evaluates by Excel’s rules for such formulas: an array result shows only its first value and does not spill, and a range where a single value is expected is intersected with the formula’s row or column. So
- Pivot tables:
- A blank header cell in a pivot table’s source now names its field
Field<n>by column position, as in Excel, where it previously named it''and two blank headers were refused as duplicates. So a refresh after a delete that empties the whole source range now empties the pivot table instead of throwing. A field that an earlier version saved as''is dropped from the pivot table on the next refresh, as a renamed header is. - Deleting entire rows or columns that contain a pivot table’s whole source range now keeps the source range, as Excel does, so the next refresh empties the pivot table; previously the range was cleared and the pivot table kept its old data.
- A pivot table whose report-filter area shrinks when it refreshes on load, because a filter field’s source column is gone, no longer leaves that field’s caption and selected-item cells stranded above it.
- Pivot table Sum, Average and Product totals now combine their values in the order Excel does, which can change the result where rounding loses a small value beside a large one, or where a partial product comes very near 0.
- A blank header cell in a pivot table’s source now names its field
- Data tables and deferred recalculation:
- A what-if data table read from a file now keeps the values saved for its result cells until a recalculation computes the table, as other formula cells do. Previously they were discarded on load: the table was computed during the load, or its cells stayed blank.
- A data table that reads another data table’s results now updates after an edit in more of the cases where it previously kept a stale result: where it also depends directly on the edited cell, and where it reads those results through a spill they resize, or through
INDEXorOFFSET. ValueSnapshot.applyTonow leaves deferred cells, including data tables inautoNoTableworkbooks, deferred asmodel.recalculate()does; pass{ includeDeferred: true }to also recalculate them.
Workbook.editCellwith a named style that does not exist now throws before changing anything. It used to write the cell, or remove the data table, first.Model.goalSeeknow tries the values Excel’s Goal Seek tries. It also now:- stops once the target is within the maximum change of the goal, rather than applying the maximum change to the changing cell;
- gives up after max(100, maximum iterations) steps, rather than after the maximum iterations (one step under maximum iterations 1);
- returns the changing cell’s value when the target already meets the goal, rather than 0;
- searches on past a value where the target is an error, a pole, a flat stretch, or so far from the goal that their difference overflows, as Excel does.
- Rounding and number formatting:
ROUNDnow matches Excel on a number exactly halfway between two 15-digit numbers:ROUND(10^15+5,0)is now1000000000000000, not1000000000000010.FIXEDandDOLLARnow round such a number the same way at a negative number of decimals.- In Google Sheets mode,
FIXEDandDOLLARnow return the text#NUM!at -309 decimals or fewer, as Google Sheets does. FIXEDandDOLLARnow write no trailing decimal point when the decimals are a fraction between 0 and 1:FIXED(1.25,0.9999997)is now"1", not"1.".
- The following count arguments now read a number less than about 2.4E-7 below a positive integer, or above a negative one, as that integer, as Excel does, so
LEFT("abcdef",0.9999998)is now"a", not"":- the character counts of
LEFT,LEFTB,RIGHTandRIGHTB - the row and column numbers of
INDEX - the places of
ROUND - the decimals of
FIXEDandDOLLAR - the position and length of
REPLACEandREPLACEB - the starting position of
FIND,FINDB,SEARCHandSEARCHB
- the character counts of
FINDandSEARCHstarting positions:SEARCHandSEARCHBnow read a starting position further below an integer as its integer part, asFINDdoes, not rounded up:SEARCH("a","aaa",1.9999997)is now 1, not 2.FIND,FINDB,SEARCHandSEARCHBnow return#VALUE!for a starting position that reads as less than 1, such as 0.5, as Excel does. PreviouslySEARCH("a","abc",0.5)was 1, andFIND("","abc",0)was 0.
SEARCH, andMATCH,VLOOKUP,HLOOKUP,XLOOKUPandXMATCHin their wildcard modes, now read a~in a pattern as Excel does:- A
~now makes any following character literal, and a trailing~is ignored. Before, a~escaped only a following?or*and was otherwise a literal~. - The rest of a pattern after an escaped
?or*is now compared too. Before, it was skipped when the text had a fullwidth?or*(the forms used in Chinese and Japanese text) at that position, soSEARCH("~?x","?y")found a match.
- A
TEXTBEFOREandTEXTAFTERnow read unusual arguments as Excel does:- They floor a fractional
instance_num, which they previously rounded away from zero. - They return
#VALUE!for aninstance_numbeyond the number of delimiters the text could hold, where they previously returned#N/Aorif_not_found. - They truncate
match_modeandmatch_endand return#VALUE!unless the result is 0 or 1, where previously any nonzero value counted as 1.
- They floor a fractional
- Other text functions:
TEXTSPLITwith both delimiters omitted now returns#VALUE!instead of the text unsplit.SUBSTITUTEnow returns the text unchanged whenold_textis empty, instead of insertingnew_textbetween every character, and now truncates a fractionalinstance_numinstead of matching nothing.ARABICnow accepts a leading minus sign, soARABIC("-I")is -1 rather than#VALUE!.- Outside Google Sheets mode,
ROMANnow truncates before its range check, soROMAN(3999.9)isMMMCMXCIXandROMAN(-0.5)is empty text, rather than#VALUE!. - In Google Sheets mode,
ROMANnow returns#VALUE!for a number below 1, such as 0, rather than empty text. - In GRID sheet and Canvas modes,
REGEXEXTRACTandREGEXREPLACEnow follow Excel rather than Google Sheets:REGEXEXTRACTreturns the whole match unlessreturn_modeis 2, and aREGEXREPLACEreplacement uses Excel’s$nand\syntax. A formula in those modes that relied on Google Sheets’ behaviour now needsreturn_mode2 to get the capture groups, and\\or$$for a literal\or$in a replacement. - In Google Sheets mode,
NUMBERVALUEnow returns#NAME?, as it does in Google Sheets, andfunctionSignaturesno longer lists it in that mode.
- Trigonometric and hyperbolic functions now match Excel at these edges:
SIN,COSandTANreturn#NUM!for angles of magnitude 2^27 (134217728) or more outside Google Sheets mode, andCOT,CSCandSECdo so in every mode.COTHandCSCHkeep their precision near 0, andASINHandACOSHreturn#NUM!for arguments of magnitude about 1.34E+154 or more.
- Matrix functions and sums now match Excel and Google Sheets at these edges:
MDETERMis correct for 4x4 and larger matrices. Outside Google Sheets mode,MINVERSEinverts a matrix that is singular only up to rounding error; in Google Sheets mode, as in Google Sheets,MINVERSEreturns#NUM!andMDETERMreturns 0 for a nearly singular matrix, even an invertible one such as {1E-12,0;0,1}.- Outside Google Sheets mode,
MINVERSEreturns#NUM!where its computation overflows, andMINVERSEandMDETERMtreat as 0 any intermediate result smaller in magnitude than about 2.2E-308. - Outside Google Sheets mode,
SUM,AVERAGE,AVERAGEA,SUBTOTALandAGGREGATEreturn 0 where the final addition of their sum cancels almost exactly, rather than the tiny remainder, andPERCENTOFover such a total returns#DIV/0!.
- Other math functions:
BASEnow ignores the fraction of its number, and returns#NUM!for a number of 2^53 (9007199254740992) or more, or a min_length above 255.MODnow returns#NUM!for a quotient of 1125900000000 or more, as Excel and Google Sheets do, rather than a remainder.FACTDOUBLEnow returns#NUM!below -1 and truncates its argument before capping it at 300.COMBINandCOMBINAnow return the correctly rounded binomial coefficient, closer to Excel, and no longer overflow for a result near the largest number or hang for arguments such asCOMBIN(1E+15,5E+14); nor doesCOMBINAoverflow for a large first argument.- As in Excel,
COMBINandCOMBINAnow return#NUM!for a negative fractional argument, such asCOMBIN(-0.5,0), and for ann(inCOMBINA, ann+k-1) of 2^31-1 (2147483647) or more, withnandktruncated first. SERIESSUMnow returns#VALUE!for text among the coefficients, and in Google Sheets mode for a text coefficient on its own; outside Google Sheets mode it now returns#NUM!for 0 to a power of 0 or less, and counts a negative x to a fractional power as 0.BITLSHIFTandBITRSHIFTnow return 0 where Excel does, instead of#NUM!: for a left shift of 49 to 53 places, and for any shift of the number 0. Google Sheets mode keeps returning#NUM!.
- Statistical functions:
LINESTandTRENDnow match Excel with redundant or constant x columns, and in other cases where they previously returned#VALUE!or wrong values.T.INV.2TandTINVnow return a negative value for a probability between 1 and 2, andT.INV,T.INV.2TandTINVreturn#DIV/0!for a probability of 0 with one degree of freedom, as Excel does, rather than#NUM!. Google Sheets mode still returns#NUM!in both cases.SKEWandSKEW.Pwith fewer than 3 numbers or with equal numbers whose computed spread is zero, andCOVARIANCE.PandCOVARwith no pair of numbers, now return#DIV/0!as Excel does, where they previously returned#NUM!or 0.SKEWandSKEW.Pof numbers large enough to overflow, andSKEW,SKEW.PandDEVSQof numbers small enough for an intermediate result to fall below about 2.2E-308, now return what Excel does, such as#DIV/0!forSKEW(0,0,1E-160)and 0 forDEVSQ(0,0,1E-160).SKEWof everyday numbers, such asSKEW(6,11,14,8), now also matches Excel more closely.BINOM.DIST,BINOMDISTandBINOM.DIST.RANGEnow match Excel more closely: they return accurate results for large numbers of trials, where they previously returned#NUM!or lost precision, and#NUM!for 2147483647 trials or more, as Excel does.STDEVA,STDEVPA,VARAandVARPAnow count a logical in a reference as 1 or 0. In an array, they now skip text, as Excel does, except in Google Sheets mode, where they now count a logical as 1 or 0 and still count text as 0, as Google Sheets does.
- Date and time functions:
TIMEnow floors fractional arguments, soTIME(0.9,0,0)is 0 andTIME(0,0,-0.9)is#NUM!, and returns#NUM!for an argument above 32767. In Google Sheets mode it truncates them toward zero instead, soTIME(0,0,-0.9)is 0, and accepts larger arguments.WEEKNUMwith an explicitly empty date now returns#N/Arather than#VALUE!. In Google Sheets mode it now reads the empty date as serial 0, as Google Sheets does.
TRANSPOSEof a single value now returns that value, not a one-element array, soSUMandTYPEover it agree with Excel.ADDRESSnow writes!$A$1(''!$A$1in Google Sheets mode) for an empty sheet name, and outside Google Sheets mode no longer quotes a workbook-qualified name such as[Book1]Sheet1.ISERRnow returns FALSE for an#N/Aread from a cell.- Outside Google Sheets mode,
IFERRORandIFNAnow return a referenced fallback as its value rather than as a reference, as Excel does, soISREF(IFERROR(1/0,A1))is FALSE.
Performance
Section titled “Performance”- The first recalculation in a process is about 25 ms faster again, undoing a slowdown in 18.2.0, when
SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,AVERAGEIFS,MINIFSorMAXIFSmatches a criterion against text, unless the text has very many distinct characters, such as long CJK text. DSUMand the other database functions are faster with a text criterion that has no wildcard.
Documentation
Section titled “Documentation”- New page “Formula types and array semantics” describes how Excel and Apiary evaluate plain, legacy array and dynamic-array formulas, and where Apiary differs from Excel.
- The
Workbookclass documentation no longer claims each workbook has its own dependency graph.
v18.2.1 (2026-10-13)
Section titled “v18.2.1 (2026-10-13)”Bug fixes
Section titled “Bug fixes”-
FISHERINVnow returns ±1 for a large value, as Excel does, where it returned#NUM!. -
ISODDandISEVENof a negative number are now correct:ISODD(-3)andISEVEN(-2.1)were FALSE. -
SUBTOTALwith the COUNTA function (function_num 3 or 103) now counts text, booleans and errors, not just numbers. -
Converting a text time in the 12 AM hour, such as
TIMEVALUE("12:30 AM")or"12:30 AM"+0, now yields the time past midnight instead of past noon. -
NUMBERVALUEnow:- reads a number in parentheses as negative, where it returned
#VALUE! - converts date text with a month name, such as
"1 Jul 2016", where it returned#VALUE! - returns
#VALUE!for an empty separator argument, where it took the default separator - returns
#VALUE!for a group separator other than whitespace after the decimal separator, where it ignored it
- reads a number in parentheses as negative, where it returned
-
GAMMAnow matches Excel to full precision. It was off from about the sixth significant digit near 0 and the negative integers (GAMMA(-1E-10)was -10000036000.7, not -10000000000.6), and from about the eighth elsewhere. -
BETA.INVandBETAINVnow return#NUM!when the lower and upper bounds are equal, as Excel does, rather than returning the bound. -
Loading a workbook now succeeds where many formulas repeat an expression that references a range and contains an inline
LAMBDA, such asSUM(BYROW(A1:A100,LAMBDA(r,SUM(r)))); it failed with “Unhandled AST node type: param”. -
The database functions (
DSUM,DGET,DAVERAGEand their siblings) now match headers and criteria as Excel does:- a field name or criteria header matches a database header as text, case-insensitively, so
"true"matches the booleanTRUEand"1"the number1 - a condition under a criteria header that is blank or names no database column is ignored if it is
TRUEor a nonzero number, and otherwise makes its criteria row match no rows DGETskips matching rows whose field value is blank- an empty criterion matches any text and no blank, and a criterion starting with
=or<>matches exactly rather than as a prefix - criteria given as an array rather than a reference return
#VALUE!, or the error in the array’s first cell
In Google Sheets mode, any condition under a criteria header naming no database column now makes its criteria row match no rows; that mode still counts blank field values in
DGET, matches no rows for any condition under a blank criteria header, and accepts array criteria. - a field name or criteria header matches a database header as text, case-insensitively, so
-
A pivot table label filter stored as a pattern now reads a
~as escaping the character after it, as Excel does, even without a*or?: “equals~d” keeps the labeldrather than~d. -
SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,AVERAGEIFS,MINIFSandMAXIFSnow read a~in a criterion as Excel does: where the criterion also has a*or?, the~escapes the character after it, soSUMIF(A:A,"~d*",B:B)sums the rows beginningd, not those beginning~d. -
PRODUCTnow returns an error in its ranges rather than skipping it, ignores an omitted argument, and returns#NUM!rather than#VALUE!or 0 when the product overflows. -
SUMPRODUCTnow returns an error even after a zero, text or boolean earlier in its row, and counts TRUE as 0 rather than 1, except in Google Sheets mode, where it now counts numeric text as its number. -
Approximate
VLOOKUPandHLOOKUPover unsorted data that ends in blank rows or columns now find the same row as Excel. -
XLOOKUPwith a single value or one-cell range as the lookup array now returns the whole return array on a match when that is one row or column, and#VALUE!when it is two-dimensional. -
LEFT,RIGHT,LEFTBandRIGHTBnow count an emoji or other character outside the Basic Multilingual Plane as one character rather than two, except in Google Sheets mode. -
PROPERnow treats CJK characters as letters, soPROPER("ロaロb")isロaロbrather thanロAロB. -
PROPERnow leavesßas it is rather than expanding it toSS, except in Google Sheets mode, where Google Sheets expands it. -
PROPERnow turnsİinto plainIat the start of a word and plainielsewhere, capitalizesΐandΰasΪandΫ, and treats a circled letter such asⒶas a non-letter, soPROPER("Ⓐa")isⓐA, except in Google Sheets mode. -
CONVERTnow raises a prefix on a squared or cubed unit to that power, so thatkm2is1e6m2, and scales a prefixed temperature before applying the unit’s offset, so that12MKis12e6K. Google Sheets mode now ignores a prefix on kelvin, as Google Sheets does. -
CONVERTnow returns a temperature converted to its own unit unchanged, and converts small temperatures between Rankine and kelvin without losing them, as Excel does. -
CONVERTnow acceptshweightas a name for the long hundredweight, and passes an error in its first argument through instead of returning#VALUE!. -
CELLnow handles theformat,parenthesesandwidthinfo types. -
CELL("color")now reads the cell’s number format instead of always returning 0. -
CELL("col")of a multi-column range now returns its first column, not its last. -
CELLwith an unknown info type and no reference now returns#VALUE!, not#REF!. -
CELLwith a reference argument that is not a reference, such as a number or text, now returns#VALUE!, not#REF!or information about the cell that the text names. -
CELL("filename")now returns[<workbook name>]<sheet name>instead of an error, so the common formula that reads the current sheet’s name works. -
DATEnow matches Excel at the edges of its range, except in Google Sheets mode:- it reads a day outside -32768..32767 as 32767, so
DATE(1900,1,-100000)is 32767 rather than#NUM! - it returns
#NUM!for a month outside -32767..32766, a year above 9999, or a month that carries the year below 1900 or above 9999, even where the day brings the date back into range
- it reads a day outside -32768..32767 as 32767, so
-
DATEwith a month of -12 or below now lands in the right year:DATE(2000,-12,1)is 1 December 1998, not 1999. -
Date text such as
"1Jul2016","1/Jul2016","Jul 2016"and"Jul 1, 2016"now converts to a date, as in Excel, instead of returning#VALUE!. -
A month name, a day and a year without a comma after the day, such as
"Jun 22 2018", now returns#VALUE!, as in Excel, instead of converting to a date.
Performance
Section titled “Performance”- Recalculating a workbook in which many formulas reference a large range is much faster, both in full and after editing cells inside the range.
- Recalculation is faster in workbooks where formulas often turn out to need cells not yet calculated, through
IForSWITCHbranches,OFFSETorINDIRECT.
v18.2.0 (2026-10-08)
Section titled “v18.2.0 (2026-10-08)”Features
Section titled “Features”- Outside Google Sheets mode,
GETPIVOTDATAaccepts Excel’s older text form, such asGETPIVOTDATA(A3,"Region[North] Product[Apples]").
API changes
Section titled “API changes”- With
recalcErrorson, loading a workbook no longer recalculates a cell saved as#REF!whose formula can only evaluate to#REF!(such as=#REF!-B7), and so no longer recalculates the cells downstream of it. A dependent saved as an error is still recalculated, as any saved error is, but one saved as a value now keeps that value: withA1=1-#REF!saved as#REF!andB1=A1+1saved as 5,B1now stays 5, where it became#REF!before.
Bug fixes
Section titled “Bug fixes”-
Workbook.reorderRowsandreorderColumnsnow move a pivot table’s source range with the data they displace, and a displaced pivot table with its cells. Before, the source range stayed on the old cells, so the next refresh read the wrong rows, and the pivot table could be written over other cells. The check that refuses a reorder tearing a pivot table apart now refuses the same reorders as Excel. -
Workbook.insertCellsanddeleteCellsover a block spanning a pivot table source range’s full width (or height) now move, grow or shrink that range with the data, as a whole-row or whole-column edit does. Before, the range stayed put. -
GETPIVOTDATAnow finds a value from criteria that skip row fields, such as an inner row field alone when it matches a single row, or a middle row field alone, which finds its subtotal. -
BYROW,BYCOL,MAP,REDUCEandSCANover a range now recalculate when a value in the range changes. A lambda helper in a cell inside its own range is now reported circular when evaluating it reads that cell’s value; one whose lambda uses only the range’s size or position, or inspects it withISREF,ISFORMULAorFORMULATEXT, is not. -
Text converted to a number now converts as in Excel:
- It keeps only its first 15 significant digits, whether converted by an operator, a function’s number argument,
VALUEorNUMBERVALUE:VALUE("1234567890123459")is now 1234567890123450 rather than 1234567890123460. In Google Sheets mode,VALUEinstead returns#VALUE!for text that is only digits and has more than 15 significant digits, as Google Sheets does; an operator on the same text converts it, cut to 15 digits. VALUEandNUMBERVALUEnow return#VALUE!for hexadecimal, octal, binary orInfinitytext, andVALUEfor whitespace-only text:VALUE("0x10")was 16.VALUE,SUMIF-style criteria and functions such asCUMIPMTnow read text numbers with a currency symbol, thousands separators or a percent sign, as operators already did, and"1,23"+2is now#VALUE!rather than 125.- Operators and number arguments now refuse a date or time with spaces before it, as in
" 2024-01-10 "+0, and text with a tab or newline around it;VALUEandDATEVALUEstill accept a padded date or time. Google Sheets mode accepts both, and also"1$"and"0,005", as Google Sheets does. - Text with a no-break space next to a number, as in
("5"&UNICHAR(160)&"%")+0, no longer converts; Google Sheets mode is unchanged. "18-Jan-2019","18 Jan 2019"and"22-June-2018"now convert to dates, andDATEVALUE("29-Feb-1900")is now 60, or#VALUE!in Google Sheets mode.
- It keeps only its first 15 significant digits, whether converted by an operator, a function’s number argument,
-
In Excel mode,
CHAR(255.5)now returns the same character asCHAR(255), rather than#VALUE!. -
Outside Google Sheets mode,
CLEANnow also removes the control characters U+0080 to U+009F, andUNICHARnow returns#N/Afor a Unicode noncharacter or a surrogate code point, rather than the character. -
In Google Sheets mode,
CONCATnow takes exactly two arguments, as in Google Sheets:CONCAT("a")andCONCAT("a","b","c")are now#N/Arather thanaandabc.CONCATENATEstill takes one or more, and now more than 255 of them. The operator functionsADD,MINUS,MULTIPLY,DIVIDE,POW,EQ,NE,GT,GTE,LT,LTE,UMINUSandUNARY_PERCENTnow give#N/Afor extra arguments, which they used to ignore. -
REPTnow returns#VALUE!for a result longer than 32767 characters in Excel mode and 32000 in Google Sheets mode, where it allowed up to 524287. -
ISERRis now FALSE for every#N/A, including one from a failedMATCHorVLOOKUP, as in Excel. -
HOURandMINUTEnow round to the nearest second, soHOUR(0.999999)is 0 rather than 23. -
WORKDAYandWORKDAY.INTLno longer count a holiday falling on the start date, which moved the result one workday too far. -
SUMX2MY2,SUMX2PY2andSUMXMY2now return the first error in their arrays instead of skipping it, andMMULTnow returns it instead of#VALUE!, unless text or a boolean comes before it. -
GEOMEAN,PERCENTILE,PERCENTRANK,QUARTILEandRANK, and their variants, now return the first error value in their data instead of skipping it, as Excel does. -
GEOMEANnow returns the error from a reference it cannot resolve, such as#REF!for a nonexistent sheet, instead of#NUM!. -
SMALLandLARGEnow take a single value, including an omitted argument (as 0), as a data set of one number, as Excel does. In Google Sheets mode, a boolean or text value is still#NUM!, as in Google Sheets. -
QUARTILEandQUARTILE.INCnow truncate a quart between 4 and 5 to 4, as Excel does. -
CUMIPMTandCUMPRINCnow return#NUM!for a pv of 0 or less, and sum only the whole periods between fractional start and end periods, as Excel does. Outside Google Sheets mode, they now also return#NUM!for a type other than 0 or 1,#VALUE!for a boolean argument and#N/Afor an empty one; in Google Sheets mode,CUMPRINCkeeps its old sum over fractional periods. -
IPMTandPPMTnow accept a period up to, but not including, nper + 1, where they returned#NUM!for any period above nper. -
Outside Google Sheets mode,
FVandPVnow return#NUM!or#DIV/0!for a rate of -1 where Excel does. For a rate below -1 and a fractional number of periods they now return the odd root Excel takes instead of an error:FV(-3,1/3,1)is -0.7533. -
PMTno longer loses precision, or returns#NUM!, for a tiny rate. -
Outside Google Sheets mode,
MIRRnow returns#DIV/0!for any reinvest rate of -1, and for a finance rate of -1 with a negative cash flow after the first. Where a rate below -1 gives Excel a result below -1,MIRRnow returns that result instead of#NUM!. -
Outside Google Sheets mode,
XNPVnow returns#NUM!for a rate of 0 or less, for a date that is 0, negative or before the first date, and for a non-numeric cash flow or date after the first, as Excel does. -
In Google Sheets mode,
XNPVnow refuses any date before the first date given, whatever its cash flow, and accepts the serial 0 as the first date. -
XIRRnow accepts the date 0, and returns#NUM!for a blank date, as Excel does. Outside Google Sheets mode, it also returns#VALUE!for a boolean guess,#NUM!for a negative date, and#N/Afor an empty amounts or dates argument or a single cash flow; and for an amount given as one value or one cell, it returns#VALUE!for boolean or text dates, or the error given as the dates. -
XIRRnow finds a rate of return below -99.9%, such as for a loss of 72.5% over 60 days, where it returned#NUM!. -
XIRR, andXNPVoutside Google Sheets mode, now return#NUM!, not#VALUE!, for a non-finite rate or guess, whichModel.writecan store in a cell.
Performance
Section titled “Performance”SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,AVERAGEIFS,MINIFSandMAXIFSrecalculate faster where many formulas test one criteria column for equality with a number or with text that has no wildcards.- Formulas that apply operators or functions element by element over large arrays, such as
ROW(A2:A50000)<>0or(A1:A50000="x")*(B1:B50000>3), recalculate faster, some several times faster. So do functions that build or transform an array, such asROWandCOLUMNover a tall range andTOCOL, andFILTERwith several conditions in Google Sheets mode. - With
recalcErrorson, workbooks with many broken references, such as=#REF!-B7, load faster.
v18.1.0 (2026-10-02)
Section titled “v18.1.0 (2026-10-02)”Features
Section titled “Features”Workbook.editCell(anchor, { dt })creates or replaces a what-if data table on a live workbook, from the samedtmetadata as a JSF cell, and{ dt: null }removes one. As in Excel, it replaces content in the result range and keeps its styles, and throws anEditBlockedErrorif the range touches any part of a merged cell, a pivot table or a worksheet table, or partially overlaps the result of another data table or of an array formula.- Support the accrued interest functions
ACCRINTandACCRINTM.
API changes
Section titled “API changes”WorkSheet.drawingsis now typed as the JSFDrawing[]from@jsfkit/types, which is what a workbook loaded from XLSX or JSF already had. A sheet loaded from CSF now converts its drawings to that form too. So read a drawing’s position fromanchor.fromandanchor.toinstead oftop_leftandbottom_right, and its chart fromcontent[0].chartIdinstead ofchart_id.
Bug fixes
Section titled “Bug fixes”- Deleting rows or columns that end inside a block of equally sized or styled rows or columns no longer moves the size or style of that block onto the rows or columns before the deleted ones.
ANDandORnow return an error that follows the value deciding their result, as Excel does:AND(A1:B1)with A1 = 0 and B1 =#DIV/0!is now#DIV/0!rather thanFALSE.serializeModelno longer throws afterModel.removeWorkbookremoves a workbook with a formula that calls an unknown function or references a missing name, sheet or workbook. Attaching a workbook no longer adds an empty entry to the serialized model.TOROWandTOCOLnow return#VALUE!for anignorevalue other than 0, 1, 2 or 3, as Excel and Google Sheets do, rather than returning the array unchanged.SEQUENCEnow truncates fractional rows and columns, soSEQUENCE(0.3)is#CALC!rather than{1}(#NUM!in Google Sheets mode); a negative size such as-0.5stays#VALUE!(#NUM!in Google Sheets mode).SEQUENCEandRANDARRAYnow return#VALUE!(except in Google Sheets mode), andEXPAND#NUM!, for more rows than a sheet has, rather than building the array.EXPANDnow reads an omitted array as 0, soEXPAND(,2,2,"x")is{0,"x";"x","x"}rather than#VALUE!.CHOOSECOLSandCHOOSEROWSnow truncate a fractional index, rather than returning 0.- An exported pivot table no longer needs repair in Excel when one field is both on an axis and in Values.
- When a recalculation fails before evaluating any cell, the next
Model.recalculate()now recomputes the cells changed before it and their dependents, which used to stay stale. - A pivot table set to show a caption for error values, but with no caption given, now leaves its error cells blank, as Excel does, instead of showing the errors.
CEILINGwith a zero or empty significance, andFLOOR(0,0), now return 0 instead of#DIV/0!.FLOOR,CEILING,ROUND,ROUNDDOWN,ROUNDUP,TRUNC,MROUND,INTand their variants no longer lose an increment to float noise, soFLOOR.MATH(7.1,0.1)is 7.1 rather than 7 andROUNDDOWN(8.94-8,2)is 0.94 rather than 0.93.FLOOR,CEILING,ROUND,ROUNDDOWN,ROUNDUP,TRUNCand the.MATHvariants now round a result past 15 significant digits to 15, as Excel does, soFLOOR(2^53+2,1)is 9007199254740990 rather than 9007199254740994.ROUNDnow rounds up only from a half at 15 significant digits, soROUND(0.5-1E-12,0)is 0 rather than 1.ROUND,ROUNDDOWN,ROUNDUPandTRUNCnow truncate a fractional digit count toward zero, soROUND(123456,-3.2)is 123000 rather than 120000.ROUND,ROUNDDOWN,ROUNDUPandTRUNCnow handle a digit count beyond float precision as Excel does: a positive count returns the number rather than#NUM!, and a negative count 0 (#NUM!fromROUNDUP) rather than the number, soROUND(123,-400)is 0 andROUNDUP(123,-400)is#NUM!, where both were 123.- In Google Sheets mode, an empty
FLOORorCEILINGsignificance now reads as 1, andROUND,ROUNDDOWN,ROUNDUPandTRUNCnow return#NUM!at a digit count beyond float precision, as Google Sheets does. MROUNDnow returns#NUM!rather than#VALUE!when the quotient is out of range, as inMROUND(1E+300,1E-10).- A number converted to text, as by
&or a text parameter, now renders as Excel does:1E+20&""is1E+20rather than100000000000000020000, and1E-7&""is0.0000001rather than1e-7. In Google Sheets mode, it renders as Google Sheets’&does. - An overflow in a single-element array, as in
{9E+307}*10, is now#NUM!as in a larger array, and an overflowing element of an array a function returns, as fromMMULT({9E+307},{10}), converts to text as#NUM!rather thanInfinity. - Drawings in a workbook loaded from XLSX or JSF now follow their cells when rows, columns or cells are inserted, deleted or moved, rather than staying where they were.
- A cell value loaded from XLSX no longer includes the phonetic guide (furigana) text of the cell. The same fix applies to the text of legacy notes.
- An XLSX workbook with OLAP cube (MDX) metadata on cell values now loads. Before, the load failed with the error
Can't reach meta-value. - A pivot table cell that a Show Data As mode leaves blank now keeps the measure’s number format when that format is not General, as Excel does.
- In a workbook loaded from XLSX or JSF (any
typebutnative),FORMULATEXTnow updates after a formula edit,ROW(B5)and the like now follow a sheet rename or a cell move, and a name using a context-dependent name (such asRowTimesTen=RowName*10withRowName=ROW()) now evaluates in the using cell’s context rather than to#VALUE!, as all three already did in anativeworkbook. DOLLARDEandDOLLARFRnow return#DIV/0!, not#NUM!, for a fraction between 0 and 1, as Excel does.DBnow returns#NUM!for a month greater than 12, as Excel does.RATEnow returns#NUM!where Excel does, rather than finding a rate by retrying from another guess; in Google Sheets mode, it still retries, as Google Sheets does.RATEnow takes any nonzerotypeas 1, and finds a rate from a guess of 0 or near 0, or a guess at a double root, as Excel does.- A
LETvariable bound to an empty argument, asyinLET(x, 20, y, , x + y), or to an argument that passes one on, asIF(TRUE, )does, is now an omitted argument, as in Excel: it evaluates as blank, andISOMITTEDreturnsTRUEfor it. It evaluated as#NAME?before. IF,IFERRORandIFNAnow pass an omitted argument on, soISOMITTED(IF(TRUE, ))isTRUE.POISSONandPOISSON.DISTnow return a probability for a mean above about 709, where they returned#NUM!or 0, and their cumulative form now finishes quickly for any x. For a negative mean they now return e^-mean where x truncates to 0.POISSON.DISTandNEGBINOM.DISTnow return#NUM!for a first argument between -1 and 0, where they returned a probability.
Performance
Section titled “Performance”- Setting the sizes or styles of many rows or columns one at a time is faster: a row or column whose size or style changes now joins the neighbouring rows or columns with the same size and style as one entry in the sheet’s
rowsandcolumns, where before each could stay its own entry, slowing every later edit and lookup. Entries stored apart in a file saved by an earlier version stay apart until the size or style of a row or column at their edge changes. - Recalculation is faster where many formulas read the same large range: they now share its values instead of each getting a copy.
v18.0.0 (2026-09-24)
Section titled “v18.0.0 (2026-09-24)”Breaking changes
Section titled “Breaking changes”- Node.js 22 or later is required. Node 18 and 20, both past end-of-life, are no longer supported.
PivotTable.jsf,PivotCache.jsfand thePivotTableconstructor are no longer in the published type declarations. Read a pivot table through its getters instead, such asname,fields,measures,layoutFormandlocation, and create one withWorkbook.pivots.add.- The deprecated
Model.analyzeFormulais removed; callModel.analyzeAndFixFormulainstead, which takes the same arguments and returns the same result. - When a workbook is loaded into a model that already contains one,
Model.getWorkbooks()now lists the loaded workbook’s external reference stubs before it rather than after it. - A loaded workbook now keeps the error values it was saved with, as Excel does, instead of recalculating them: error-heavy workbooks load much faster, but those errors carry no
detailmessage ororiginreference. To recalculate them, pass the load optionrecalcErrors: trueto the method that loads the workbook, such asModel.fromXLSX,Model.fromJSForModel.addWorkbook. - Formula cells saved without a value are now computed at load, with the formulas that depend on them; they read
nullbefore. The new load optionrecalcUncached(defaulttrue) controls this. A cell whose references cannot be resolved is also recalculated when a lateraddWorkbooksupplies what was missing. - A workbook saved in manual calculation mode now keeps its saved values on load, as in Excel: for it, the load options
recalcVolatilesandrecalcUncacheddefault tofalse, so a formula cell with no saved value readsnulluntil the next recalculation. Passtruefor either to recalculate on load. - Formula cells that reference an external workbook are no longer always recomputed at load; they follow the same load options as the workbook’s other formula cells. Pass
recalcAll: trueto compute them all.
Features
Section titled “Features”- Pivot tables can now be created, edited and deleted with
Workbook.pivots.add,updateanddelete. Insideupdate, thePivotTable.fields,PivotField.itemsandPivotTable.measureshandles place, rename, filter and sort fields, rename, hide and collapse items, and add, change and remove measures. - New model events
renamesheetandremovesheet, emitted before the recalculation that follows the change. - A model event listener that throws no longer stops the other listeners or the call that emitted the event; its error goes to the new
listenererrorevent.recalcEventcallbacks are unaffected. Workbook.setRowHeightandWorkbook.setColumnWidthnow acceptnullto remove a row’s or column’s own size, so it falls back to the sheet default; a styled column instead gets the sheet default width as its own.- New functions:
AMORDEGRC,AMORLINC,ARRAYTOTEXT,AVEDEV,DEVSQ,MULTINOMIAL,PERCENTOF,PROB,VALUETOTEXT,VARP,VDB,Z.TESTandZTEST. As in Google Sheets,VALUETOTEXTandARRAYTOTEXTare unavailable in Google Sheets mode.
API changes
Section titled “API changes”- New getter
PivotTable.cache. Newly exported:PivotField,PivotItem,PivotMeasure,PivotAddOptions, thePivotFieldFiltertypes,PivotNameConflictError,PivotSourceInvalidError, andisDateFormat, the pivot cache’s test for a date number format.
Bug fixes
Section titled “Bug fixes”-
COMPLEXnow treats a blank suffix cell asi, as Excel does, instead of returning#NAME?. An invalid suffix now returns#VALUE!. -
In Excel and GRID sheet modes,
IFSandSWITCHnow return 0, as Excel does, rather than blank, when the value they choose is an omitted argument, or a blank cell in an array result. -
Adding, removing, reordering or copying a sheet no longer leaves
FORMULATEXTshowing the old text of a formula that was edited since the last recalculation. -
Removing a sheet after editing a cell whose formula applies a lambda over more than 10,000 rows (with
BYROW, say) no longer aborts the recalculation, which left that cell blank and recorded a “Recalculation failed” error. -
Loading an XLSX file now:
- keeps charts among a sheet’s drawings, so a structural edit accounts for them
- reads pivot table values summarized by
StdDevporVarp, which loaded as sums - honors iterative calculation settings written as
true, as LibreOffice writes them - never treats a workbook with external links as a Google Sheets export
-
VAR.P,STDEVPandSTDEV.Pnow return#DIV/0!instead of 0 given no numbers, as doVARPAandSTDEVPAgiven no values, andSUBTOTAL,AGGREGATE,DVARPandDSTDEVPwhen they compute a population variance or standard deviation.The variance and standard deviation functions (
VAR.P,VAR.S,STDEV.P,STDEV.Sand their older andAforms) now return#NUM!when the values or their deviations are too large to sum or square, instead of 0,#DIV/0!or a non-finite number. -
A formula that reads a cell both directly and through
OFFSETorINDIRECT, such as=OFFSET(B1,0,E1) + A1while theOFFSETresolves toA1, keeps depending on that cell after theOFFSETorINDIRECTretargets away from it; previously editing the cell left the formula on its old value until a full recalculation, and moving the cell left the formula naming the cell’s old address. -
A formula Apiary cannot parse, in a cell or a defined name, now exports from
Workbook.toJSFandtoXLSXasIFERROR(__xludf.DUMMYFUNCTION("<the formula>"), <its value>), which Excel opens, keeping the value it had in Apiary (a number to its first 15 significant digits). PreviouslytoXLSXcould write such a formula in a form that Excel refuses to open, or computes differently, and dropped such a defined name. -
A pivot table with both a label filter and a value or top-N filter now applies the label filter first, as Excel does; previously it followed the filters’ stored evaluation order, which could keep different items.
-
A pivot table measure shown as Running Total In or % Running Total In now leaves the grand totals across its base field blank, as Excel does; previously they showed the final running total, or 100%.
-
VLOOKUPandHLOOKUPnow return#REF!instead of#N/Awhen the search key is found but the column or row index is past the range, as Excel does; a key that is not found is#N/Awhatever the index. -
BESSELInow matches Excel; it was far off for a negative or large x. In Excel mode,BESSELJnow matches Excel too, where it differed in the 5th or 6th significant digit for x ≥ 8; GRID sheet and Canvas modes keep its more accurate value.BESSELI,BESSELJandBESSELYnow return#NUM!for an order or an x beyond Excel’s limits, as Excel does, instead of computing a value.In Google Sheets mode,
BESSELI,BESSELJ,BESSELKandBESSELYnow return#NAME?, as in Google Sheets, andfunctionSignaturesno longer lists them. -
A formula that reads a table by its name alone, such as
SUM(Table1), no longer resolves to a stale reference on the old sheet after the table’s sheet is renamed. -
toJSFno longer throws when deferred cells are stale, such as the data-table cells of anautoNoTableworkbook; it exports their saved values, as Excel does. To export fresh values, callmodel.recalculate({ includeDeferred: true })first. -
In Google Sheets mode,
QUERYon a table’s name or one of its columns now reads only the table’s data rows, as Google Sheets does, instead of adding the table’s header row as the query’s header. -
Arithmetic that overflows, in an operator or in a function such as
SUMorAVERAGE, now returns#NUM!, as in Excel, instead ofInfinity,-Infinityor#DIV/0!. -
The database functions now match a criterion containing a
~as Excel does. In a criterion that also contains a*or?, a~escapes the character after it; in one that contains neither, a~is a literal tilde. -
A defined name created with
Workbook.setDefinedNamenow follows the cells it refers to when they are moved, and formulas that refer to it resolve to it, as in Excel. -
A formula loaded from JSF in R1C1 form now quotes a 3-D reference whose last sheet is named
TRUEorFALSE, as in'Jan:TRUE'!A1; unquoted, Excel does not read it as a reference. -
Exporting to XLSX now writes calls to
ENCODEURL,FILTERXML,WEBSERVICE,STOCKHISTORY,TRANSLATEandPYwith the_xlfn.prefix Excel requires, so they no longer open in Excel as#NAME?.
Performance
Section titled “Performance”- Recalculating with iterative calculation enabled is much faster where formulas that are part of, or depend on, a circular reference read large ranges.
SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,AVERAGEIFS,MINIFSandMAXIFSare faster when a criterion has no wildcard.- Recalculation is faster when many formulas read the same range.
- Loading a workbook that references external workbooks no longer optimizes the dependency graph and recalculates the model twice.
v17.1.0 (2026-09-11)
Section titled “v17.1.0 (2026-09-11)”Features
Section titled “Features”- Support 3-D references such as
=SUM(Sheet1:Sheet3!A1): a function that accepts one computes over every sheet the reference covers, as in Excel, and recalculates when a covered cell is edited, or when adding, moving, copying, removing or renaming a sheet changes which sheets are covered. Such a formula previously evaluated to#NAME?or#REF!, and one loaded from an XLSX file lost its sheet span, arriving as=SUM(Sheet1:'Sheet3'!A1). - A workbook view can now freeze, unfreeze, and read a sheet’s panes (Excel’s “freeze panes”) through
WorkbookView.freezePanes,unfreezePanes,getFrozenPanes, andhasFrozenPanes. Frozen panes are read from XLSX files on load and written on export.
API changes
Section titled “API changes”- New public getter
Table.styleexposes the table’s visual style. Use it instead ofTable.jsf.style; thejsfis internal and absent from the published type declarations.
Bug fixes
Section titled “Bug fixes”Workbook.renameSheetnow refuses a name Excel refuses, asaddSheetandcopySheetalready did: longer than 31 characters, containing any of: \ / ? * [ ], or starting or ending with an apostrophe. All three now accept an apostrophe within a name. A name of nothing but whitespace is refused too, since no reference to such a sheet can be written. Loading a file is unaffected.- Copying a sheet now derives a name
addSheetwould accept; before, the copy’s name could run past 31 characters, or keep a refused character from a source sheet loaded under such a name. - Defined names and sheet names that collide with JavaScript object property names (
__proto__,constructor, …) now behave like any others, instead of silently vanishing on creation or misreporting as existing. Model serialization is now version 10; earlier payloads still load. YEARFRACwith basis 0 (US 30/360) now returns a whole number of years when the start and end dates are both the last day of February, matching Excel:YEARFRAC("2022-02-28", "2023-02-28", 0)is 1, not 0.994444…- A formula cell that calls a function the engine does not implement, or names a defined name the file does not define, now keeps its saved value, matching Excel; previously such a cell could be recalculated to
#NAME?when its workbook was added, and again on every lateraddWorkbookinto the same model. INDEX(range, ...)no longer reports a false circular dependency through cells ofrangethe formula never uses; previously a workbook with such formulas computed cleanly in Excel while Apiary evaluated part of the false cycle as0.- Formula cells that depend on a pivot table, through
GETPIVOTDATAor a plain reference to an output cell, now update after a cache refresh whose only change is to the fields (a source-column rename, reorder, drop or add); previously they kept results computed from the old fields until something else forced a recalculation. - A cell whose text is the name of a JavaScript object property (
constructor,toString,valueOf,hasOwnProperty) now survives CSF import as that text; previously it was mistaken for an error name and replaced with the inherited function object, after whichModel.readValuethrew. - A formula cell that the file cached no value for now reads
#NAME?or#REF!when nothing its formula names resolves, instead of possibly reading blank; it still recovers to a real value if a lateraddWorkbooksupplies what was missing. - A workbook that references a defined name in another (typically external) workbook no longer fails to load with an internal assertion error when the referencing sheet defines a sheet-scoped name by that name.
- In a model with several workbooks, a formula reading a defined name now depends on the name it actually evaluates to, so editing that name recalculates the formula, and editing a like-named one in another workbook no longer does.
- A workbook-prefixed defined name, such as
[other.xlsx]total, is taken from that workbook’s own scope, never from the sheet the referencing formula sits on. - A reference naming a workbook, such as
=[other.xlsx]!total, now yields#NAME?when that workbook has no such name, instead of silently reading a like-named one from the referencing workbook. - An unprefixed defined name is no longer claimed by a like-named sheet in a different workbook. In an Excel-mode workbook, a name defined only in another workbook now stays unresolved, matching the
#NAME?it evaluates to. - A sheet-qualified reference into another workbook, such as
[other.xlsx]Data!A1, now looks forDatain that workbook only, instead of possibly landing on a like-named sheet of some other workbook. - A cell reading a sheet-scoped defined name no longer keeps a stale value after a sheet is removed.
- Moving cells no longer fails with an internal assertion error when a formula in another workbook of the model references the range being moved.
- A defined name in an external workbook that reads a sheet, name, or table the external workbook does not have no longer silently resolves to one belonging to the workbook that referenced it, and reports
#REF!or#NAME?instead. - A defined name in an external workbook is now evaluated in the referencing workbook’s mode, so Excel-only functions in such a name no longer report
#NAME?. - A reference to a sheet whose name reads as something else (a cell address such as
A1, an R1C1-style name such asRorR1C1, a name leading with a digit such as2020, orTRUE/FALSE) is now written with quotes, as Excel writes it. Workbook.fromXLSXandWorkbook.fromXLSXFilenow include Excel-only functions such asANCHORARRAYin workbooks. This matches the existing behaviour ofModel.fromXLSXandModel.addWorkbookFromXLSX.- An XLSX file exported from Google Sheets now loads as a Google Sheets workbook and evaluates under Google Sheets rules, instead of Excel’s. A file with external workbook references is still loaded as an Excel workbook.
- A range whose two endpoints name different sheets, such as
=SUM(A1:Sheet2!B2)written anywhere but onSheet2, now returns#VALUE!as Excel does; previously the right endpoint’s sheet prefix was ignored. - A string literal longer than 255 characters now evaluates to that string instead of
#NAME?. Excel stores such a literal as a call to its internal_LONGTEXTfunction over shorter chunks. - A formula that mixes an unresolvable reference with an
OFFSETcall now keeps its saved value when another workbook is added to the model, instead of being recomputed even though the added workbook resolves none of its references. - A formula that was edited away from a reference it could not resolve now keeps its value when a workbook is added to the model; previously the addition recomputed the formula and overwrote whatever the cell held.
- Adding, removing, reordering or copying a sheet in one workbook no longer disturbs a pending recalculation in another workbook of the same model, which could leave a formula in that workbook holding a stale value.
- The
TYPE_*value-type constants can now be imported; they were declared as exports but wereundefinedat runtime. - A defined name now follows
Workbook.moveCells, as a cell formula already did: one pointing at the moved cells is repointed at their new location, and one pointing at cells the move overwrote breaks to#REF!. Previously such a name kept pointing at the emptied source, so a formula reading it got the wrong value. - A formula optimized by subexpression extraction (done by default on load) was not updated when cells referenced by the subexpression were moved. Now it is. This affects
moveCells,reorderRows,reorderColumnsandTable.insertRow. Row and column inserts and deletes were never affected. - A cell value of
NaNor±Infinitynow exports as#NUM!, in XLSX, JSF, CSF and pivot cache records. None of those formats can represent a non-finite number, and Excel rejects a file that spells one out. ^andPOWERnow return the real negative root for a negative base whose exponent is the reciprocal of an odd integer, as Excel does; previously they returned NaN.=(-0.32)^(1/5)is now -0.796214341106995. Every other negative base with a non-integer exponent is#NUM!.POWERnow returns the same results as the^operator for a zero base.POWER(0,-1)is#DIV/0!in Excel mode and#NUM!in Google Sheets mode; it returned Infinity before.- A formula with a number literal that has a trailing exponent but no fractional digit, such as
1.E5, no longer has the exponent misread as a cell reference and offset when exported to JSF, which stores formulas in R1C1 notation:1.E5used to become1.R[4]C[4]. Workbook.addSheetwith no name no longer picks a name (Sheet2) that case-insensitively matches an existing sheet’s name (sheet2). It now picksSheet3in that case.- Renaming a table no longer disconnects formulas in other workbooks from it: after a case-only rename,
=ROWS('[source.xlsx]'!People)kept reading 2 where appending a row should have made it 3. Workbook.moveCellswith references that name no sheet, such asmoveCells('B1', 'D1'), now repoints dependent formulas as a sheet-qualified move does: a dependent=B1+1becomes=D1+1instead of staying on the emptied cell. Whole-column and whole-row references already did. A caller that also applied the returnedRewriteFormulato the moving workbook was accidentally correct for such a move, and would now turn those repointed dependents into#REF!.- A row number with leading zeros is now recognized:
A01reads asA1, and01:1as1:1. PreviouslyA01was mistaken for a name and the anchored and whole-row forms failed to parse. These references are now rewritten on edits like any others, and written back without the zeros. A sheet namedA01is now quoted in a reference prefix, so it is not mistaken for a cell. - An insert, delete, move or rename no longer changes what a formula names:
- A cross-sheet move writes the destination sheet’s prefix instead of dropping it.
- A delete above a range pinned to the last row or column moves only its near edge.
- A trim range (
A1.:B2) keeps its trim operator. - A Google Sheets partial or inverted span (
B2:D,D:B) shifts and clips with the edit. - A defined name beside a sheet prefix (
g3:d:'2'!R777) stays a name.
- A tab in ordinary formula whitespace is now a syntax error, as is a carriage return after a space, the last position that still accepted one; Excel repairs such a workbook by deleting the formula’s cell. Between a structured reference’s brackets a tab is whitespace, as in Excel.
- A defined name whose formula was given with a leading
=, as inWorkbook.setDefinedName('Total', '=Sheet1!$B$5'), made Excel repair an exported workbook and drop the name, leaving every cell that read it#NAME?. - A reference to a sheet whose name contains
$was exported unquoted, and Excel’s repair deleted the formula. Such a cell now exports as its cached value, with the formula text kept in a placeholder call. - Writing a value into a table’s header cell left the cell disagreeing with the column name, and Excel repaired the exported table. The header cell now takes the column’s name.
- A spilled-range reference inside an array constant, as in
=SUM({A1#,B1}), kept its#on export, which Excel repairs. - A string literal longer than 255 characters now exports as Excel writes one, an
_xlfn._LONGTEXTcall over chunks. Written out in full, one of 4,096 characters or more made Excel offer to repair the workbook, and one that had arrived in such a call exported as an unresolvable_LONGTEXT, reading#NAME?. - A pivot table with a
stdDevPorvarPdata field now exports with OOXML’sstdDevpandvarptokens; the JSF names written before are not valid OOXML. - A date styled
m/d/yy,d-mmm-yy,d-mmm,mmm-yyorm/d/yy h:mmexported as a custom format. It now uses the Excel built-in format. - A workbook set to manual calculation asks for a full recalculation on load, and the export dropped that request. It is now included.
- A cell using Excel’s built-in date-time format (
m/d/yy h:mm) now loads from an XLSX file with its date part; the format was read as the time-onlyh:mm:ss.
Documentation
Section titled “Documentation”- The
Workbook.moveCellsdocumentation now says theRewriteFormulait returns is for the model’s other workbooks, sincemoveCellshas already adjusted the moving workbook’s own formulas. - A new “Supported functions” page lists the spreadsheet functions Apiary supports, with their argument names.
- A new “Internal defined names” page documents the cached formulas and extracted subexpressions Apiary stores as defined names, and how a caller should treat them.
Dependencies
Section titled “Dependencies”@grid-is/waspiary^1.17.0→^1.19.0@grid-is/jsf2xlsx^2.3.3→^2.4.1@borgar/xlsx-convert^5.6.1→^5.7.0@borgar/fx~5.0.0→^5.1.2@jsfkit/types^2.5.0→^2.6.0
Performance
Section titled “Performance”- Editing a formula no longer leaks memory, and removing a workbook now releases nearly all of the memory it occupied. The dependency graph kept a record of every edge it ever removed, and each record pinned the cells at both ends along with the parsed formula it came from.
v17.0.0 (2026-07-10)
Section titled “v17.0.0 (2026-07-10)”The stable 17.0 release. Its headline feature, pivot-table support, landed in the beta series and is stabilized here.
Upgrading from 16.x
Section titled “Upgrading from 16.x”The significant breaking changes across the whole 17.0 line are:
- Edit guards. Edits that would corrupt a what-if data table, a pivot table’s output, a worksheet table, or a merged cell now throw instead of silently damaging the model. Every refusal is an exported subclass of a common
EditBlockedError, so oneinstanceofcatches them all:DataTableEditBlockedError,PivotEditBlockedError,PivotCellWriteBlockedError,PivotOutputOverlapsDataError,PivotDuplicateSourceHeadersError,TableEditBlockedError,SortCellsBlockedError, andReorderMergeBlockedError. - Writes are persistent. Transient writes and
resetare gone: write throughWorkbook.editCelland capture/revert withValueSnapshot. Removed with them areModel.reset/writes/writeState, the named-values feature, range writes, writes to defined names, and theGRID.SCENARIO()function (now#NAME?). - Default style slot.
StyleManagerreserves style index 0 as the workbook default, sostyles.lengthstarts at 1. - Renames.
moveRows/moveColumns→reorderRows/reorderColumns(the old names are gone);RecalcUreachedEvent→RecalcUnreachedEvent; theRewriteFormulacallback now takes sheet/workbook context. - Externals round-trip in full.
Workbook.toJSF()now preserves all input-provided external cells, not only the referenced ones, so round-tripped JSF contains strictly more external cells than before. - The
Cellvalue setter no longer accepts aLambda. Callers must coerce a possible lambda to an error value themselves. - Internals are no longer typed. Members documented
@internal— among themCell.userZ— are stripped from the published TypeScript declarations, so code reaching into engine plumbing will now see a type error.
Full detail is in the v17.0.0-alpha.0, -beta.0, and -beta.1 notes below and at https://docs.grid.is/apiary/.
Breaking changes since v17.0.0-beta.1
Section titled “Breaking changes since v17.0.0-beta.1”Workbook.toJSF()now preserves all input-provided external cells (andrefreshError) underexternals[].sheets[].cells, not only those the host’s dependency graph referenced, so round-tripped JSF contains strictly more external cells than before.- The
Cellvalue setter (cell.v = …) now takesMaybeBoxed<CellValue>and no longer accepts aLambda; callers must coerce a possible lambda to an error value themselves (previously the setter coerced it to#CALC!). - The pivot layout cluster is now internal:
PivotLayout(and itsPivotCellWrite,ComputeOptions, andColumnSubtotaltypes) is no longer exported. It was materialize/repaint plumbing; the renderer-facing surface remainsPivotTable.rowRoles/colRoles/pageAreaand the already-materialized cells. - The
SubtotalRow/AggregateSubtotalRow/SeparatorSubtotalRow/BlankSubtotalRowtypes are no longer exported. They were reachable only through the now-internalPivotLayout; renderers should readPivotTable.rowRoles/colRolesinstead. PivotCacheis now a type-only export: no public API returns or accepts a constructed instance, so the value export offered only a constructor and lifecycle methods that bypass the pivot manager.- The
moveRows/moveColumnsnames, deprecated as aliases whenreorderRows/reorderColumnswere introduced earlier in the 17.0 line, are removed; themove*names stay reserved for a possible future overwrite-move variant (the row/column counterpart ofmoveCells, which overwrites the destination instead of reflowing). - The edit-guard error classes are renamed to the package’s
*Errorconvention (PivotEditBlockedError,PivotCellWriteBlockedError,PivotOutputOverlapsDataError,DataTableEditBlockedError) and now extend a common exported base classEditBlockedError, so a host can catch every guard refusal with oneinstanceof. Payloads are made consistent and richer: each carriessheetName,PivotEditBlockedErrorgains anoperationdiscriminator, andPivotCellWriteBlockedError.cellRefis now a plain A1 string. Refusals that previously threw bareErrornow throw typed subclasses:PivotDuplicateSourceHeadersError(duplicate pivot-source headers),TableEditBlockedError(the worksheet-table-protectinginsertCells/deleteCellsrefusals),MergedCellWriteBlockedError(a write througheditCellto a merged non-anchor cell), andSheetEdgeBlockedError(an insert that would push non-empty cells or a drawing off the sheet edge).Workbook.editCellandWorkbook.sortCellsthrow the now-exportedSheetNotFoundErrorfor a missing sheet, and the newWorkbook.fillthrowsPivotEditBlockedError(operation'fill') over a pivot’s output. - Public getters that returned live internal objects now return copies and/or
readonly-typed values, so mutating a returned value can no longer corrupt engine state:PivotTable.getOutputBounds()/getProtectedBounds()(copies typedReadonly<OutputBounds>),PivotTable.dataFields(areadonlycopy), andStyleManager.defaultStyle(a copy;setDefaultStylestores one). - Members documented
@internalno longer appear in the published TypeScript declarations (dist/index.d.ts/index.d.cts); code referencing engine plumbing such asPivotCache._permuteRecordsorPivotManager._cacheswill now see a type error. PivotColRole.kindis typed'subtotal'(the only value the engine produces); drop any comparison to'subheading'.
Features since v17.0.0-beta.1
Section titled “Features since v17.0.0-beta.1”- Formula parsing now handles Excel’s structured-reference
@this-row forms (Tbl[@col],Tbl[@[col]],Tbl[@x:y]). A bareTbl[@](this row, all columns) evaluates to#VALUE!, matching Excel. - New
AGGREGATEfunction: all 19 aggregations and options 0–7, in both the reference form (function_num1–13) and the array form (14–19). Matching Excel, only the array form accepts array expressions: the reference form returns#VALUE!for an argument such asA1:A3*2or2*A1, and for an extra argument that is not a further range (a scalar such as=AGGREGATE(9,4,A1:A3,2)returns#VALUE!; an array such as{2,3}spills a per-element#VALUE!). LikeSUBTOTAL, it skips range cells whose own formula nests aSUBTOTAL/AGGREGATEcall, so subtotals-of-subtotals are not double-counted. - New
MODE,MODE.SNGL, andMODE.MULTfunctions (previously#NAME?);MODE.MULTspills a vertical array of all values tied at the highest count. - New
Workbook.fill(source, destination, type?)editing operation models Excel’s fill-handle drag — values, formulas (relative references shifted), and formatting projected across the destination, with Excel’s series/date/text-number semantics andFillTypemodes. Text series follow Excel’s rules: quarter labels wrap modulo 4 (Q3→Q4, Q1, Q2, notQ5, Q6), an ordinal like1st Periodrecomputes its suffix per value (2nd, 3rd, 4th Period), text with more than one number steps only its last run (A1B2→A1B3, A1B4), and zero-padded seeds keep their padding. See the docs for the full rules. - New
Workbook.sortCells(sheetName, range, sortKeys, options?)editing operation sorts rows within a range by one or more key columns, matching Excel’s Data > Sort: cell contents, formulas, and styles move with their rows (relative references adjust for the row change; absolute and sheet-prefixed references stay put), row heights stay at their positions, hidden rows are left in place, and blanks sort to the bottom. Merged cells and multi-row spill ranges are refused, as in Excel; so is a spill reaching into the range from outside. So is a sort that would disturb a worksheet table — touching its header or totals rows, crossing its data-body boundary, or leaving some of its columns unsorted — and one overlapping a pivot table’s output or a what-if data table’s result cells. Every refusal throws a subclass ofEditBlockedError, so a host can catch them all with oneinstanceof:SortCellsBlockedError(carrying areason) for the merge and spill cases, andTableEditBlockedError/PivotEditBlockedError/DataTableEditBlockedError(each carrying the structure’s name and anoperation) for the three structures. A whole-column or whole-row selection is clamped to the contiguous data region rather than scanning the sheet’s full height.
API changes since v17.0.0-beta.1
Section titled “API changes since v17.0.0-beta.1”PivotManager(the type ofworkbook.pivots) is now exported from the package entry, the last pivot-cluster manager type that was not already named there.- The
Dimensiontype ('row' | 'col', the axis parameter of pivot range-adjustment methods) and theFILL_TYPESlist (the validWorkbook.fill()modes) are now exported, andfunctions.CHOOSE/functions.SWITCHare restored to the publicfunctionsmap. - The error classes
SheetNotFoundError,NameNotFoundError, andWorkbookNotFoundError— long documented in@throwsannotations — are now exported, so hosts caninstanceofthem instead of string-matching;NameNotFoundErrorgains adefinedNamefield. isPivotLookupErrornow narrows correctly as a type guard on a value typedunknown(its true branch narrows toPivotLookupErrorinstead ofnever).
Bug fixes since v17.0.0-beta.1
Section titled “Bug fixes since v17.0.0-beta.1”- Excel’s
@implicit-intersection operator no longer evaluates to#NAME?:=@A1:A3,=B1*@A1:A3, and the like now collapse the range to the value in the intersecting row or column (or#VALUE!when there is none), matching Excel. CHOOSEandSWITCHnow short-circuit likeIF, matching Excel: a reference in an unchosen branch is not evaluated (no false circular-dependency error), and an array selector spills and broadcasts element-wise.CHOOSEnow truncates a fractionalindex_numtoward zero before selecting, so e.g.CHOOSE(3.5, …)returns the third value instead of#VALUE!, matching Excel.TEXTBEFORE/TEXTAFTERnow accept an array or rangedelimiter(matching any of them) instead of returning#VALUE!, counting occurrences non-overlapping exactly as the same scalar delimiter would; and an out-of-rangeinstance_numnow yields#VALUE!rather than#N/A.- A multi-cell range fed to an array-typed (but not range-typed) parameter in a non-array formula now dereferences to a matrix instead of being intersected to one cell, so
=SUM(MMULT(A1:B2,A1:B2))works, matching Excel. Matrix.ofTransposedof an empty matrix now yields an empty matrix instead of a corrupted one (width 1, height 0).- A formula entered by editing that needs array-context evaluation — a bare range in a scalar-wanting argument (e.g.
=TYPE(A1:A3)), or one that evaluates over an array without itself returning one (e.g.INDEX(range, SMALL(IF(…), k))) — is now classified as an array formula (t="array") and evaluated over the whole array, matching Excel, instead of being implicitly intersected. SUMPRODUCTnow returns#VALUE!for mismatched argument dimensions even when the cell counts match (e.g. a 1×2 row against a 2×1 column).SUBTOTALnow skips range cells whose own formula nests aSUBTOTAL/AGGREGATEcall, matching Excel, so subtotals-of-subtotals are not double-counted.FORMULATEXTno longer returns a stale formula after a value write removes the cell’s formula.- A broken cross-sheet reference now keeps its sheet qualifier —
=Sheet1!#REF!rather than=#REF!— when the referenced cell is deleted or pushed off-grid, matching Excel. - Structural edits (insert/delete/move/reorder of rows, columns, and cell blocks) now update references in charts, drawings, model errors, and view selections, which were previously left pointing at stale coordinates.
- Renaming or deleting a sheet now rewrites chart and
ModelErrorreferences (and drops a chart hosted on a deleted sheet), matching Excel. - A table referenced by its implicit defined name (e.g.
=ROWS(People)) no longer stays stale after a structural edit resizes or moves the table. - A table is now kept in sync when its sheet is renamed or removed (previously only pivot tables were), so
Table1[Col]no longer breaks to#REF!and the table’s[[#Data]]name does not linger on a nonexistent sheet. - A sheet-scoped defined name referencing a like-named name on another sheet no longer raises a false
#NAME?circular-dependency error. - Resizing one column or row no longer collapses the following columns or rows that shared its size (and drops their style).
- Resizing a column or row no longer silently changes its own formatting: size runs are merged only when both size and style match, so setting a column’s width or a row’s height to a neighbour’s size keeps its own style instead of adopting the neighbour’s.
Workbook.reorderRows/reorderColumnsno longer silently dissolve a merged range the move would cut through (one whose rows or columns would not all travel together); the reorder is refused before anything moves — throwing the new exportedReorderMergeBlockedError(anEditBlockedError, carrying theaxisand the merge’s A1 range) — matching Excel, which never cuts through part of a merged cell. Merges that travel whole with their rows/columns still do.- Copying a sheet now duplicates its tables and pivot tables (pivots sharing the source cache) and rewrites the copy’s own structured references, matching Excel; previously copied structured references were unresolvable and copied pivot output had no backing
PivotTable. - Copying a sheet then deleting the original no longer drops the copy’s chart.
- Copying a sheet no longer leaves phantom spilled cells that keep a stale value when the spill later shrinks on the copy.
- Cells holding an empty string or
#N/Aare no longer treated as blank when resolving an area, correctingcropTo: 'cells-with-non-blank-values'cropping and spilled-range reads over such cells. Model.metano longer inflates graph and volatile counts (manifested only with more than one workbook).Model.writenow honors theforceRecalcoption on a same-value write, asModel.writeMultiplealready did.- Chained what-if data tables now recompute in dependency order, so a data table that reads another’s output no longer locks in a stale result.
- Typing a value over the single result cell of a one-cell what-if data table now removes the table and keeps the value, matching Excel; previously the table survived, so the next recalculation overwrote the typed value. Clearing the cell already removed the table, and an edit covering only part of a larger result range is still refused.
- A what-if data table that cannot be recomputed (a broken input-cell reference, or an input cell spilled into by an array formula) now shows the error in every result cell instead of only the anchor with the rest blank, matching Excel.
ValueSnapshotcapture and apply no longer leave the reverted model diverging in several cases — dependents of mid-spill cells, an anchor that was scalar at capture but now spills, a defined name deleted between capture and apply, and a global-vs-sheet-scoped name collision.- Reverting a captured
ValueSnapshotno longer fails partway and leaves the model half-restored: when a cell captured as a plain value had since become part of a data table’s result range (or another guarded region),applyTocould throw the write guard mid-restore. Restores now bypass those guards, soapplyTocompletes atomically. Model.off()with no arguments, and removing one of severalmetrics/addsheetlisteners, no longer tear down the workbook-level event forwarders that other listeners still need.- A pivot table’s merged ranges are now recomputed on refresh instead of leaving the file’s saved merges in place, which garbled the refreshed output.
- On refresh, a pivot’s label cells are repainted the way Excel does — centered under
mergeItem, with stale system-white borders dropped from merge anchors — fixing misaligned subtotal and total labels. - Auto-materialized pivot caption cells no longer carry a baked-in font colour from third-party producers, so the header band is no longer broken up under a dark table style.
- A pivot’s calculated-field values are now recomputed from the current source on every refresh, so a data-only refresh no longer leaves an appended record’s value empty or an edited record’s value stale.
- A pivot value or top-N filter measured on a calculated field no longer empties the pivot table.
- A pivot calculated field that references another calculated field now composes the referenced field’s formula over the bucket’s summed source values, matching Excel, instead of summing its per-record values — so
B = A(withA = Qty*Price) reproducesA’s own cell (SUM(Qty)*SUM(Price)) rather thanSUM(Qty*Price). A reference cycle between calculated fields yields#NAME?. - Pivot value cells for degenerate groups now match Excel: an all-blank group renders blank under every aggregation (Average and Product previously
#DIV/0!and0), and a text-only group renders0for Count Numbers/Min/Max and#DIV/0!for StdDev/Var (all previously blank). - Deserializing a model no longer re-runs a pivot’s
refreshOnLoad, keeping stored pivot values through a JSF round-trip. - A native workbook serialized before 17.0 no longer loads with its first user-defined cell style promoted to the workbook default. Older payloads reserved no default slot, so the 17.0 read path — which treats style index 0 as the default — repainted every unstyled cell with that first style;
deserializeModelnow recognises these payloads by their serialization version and reinstates the reserved slot. Workbooks loaded from xlsx or Google Sheets were unaffected. - On a Values-on-column-axis pivot refresh, the first row-label header keeps the field name instead of being overwritten with the literal
Row Labels. - Refreshing a multi-value pivot whose row axis holds only the Values grouping (data-field names down the rows, no other row field) no longer overwrites the value-name labels with numbers; the
Sum of X/Sum of Ylabels stay in their own first column and the data begins one column to the right, matching Excel. - Refreshing a pivot table no longer turns a date-formatted source value outside Excel’s representable date range (a serial before 1899-12-30, or in year 10000 and later) into text; such values stay numeric in the cache, matching Excel, so sorting, grouping, aggregation, and export see a number rather than a string.
- A pivot table’s default row/column item order now sorts error items (such as
#DIV/0!) before blank items instead of interleaving them, matching Excel: ascending is numbers, then text, booleans, errors, and blanks last (descending is the exact reverse). - On refresh, a pivot’s stored
location/@refis rewritten to the materialized output range, matching Excel, so a stale ref no longer understates the pivot. PivotTable.getOutputBounds()now returns the correct rectangle for a row-axis Values layout, which previously mis-sized the exported<location ref>and made the pivot render#SPILL!on refresh.- A multi-value pivot with Values on the row axis and no column field now exports a definition matching Excel (value names nested as row blocks, no reserved value-caption row).
- A tabular outline-above pivot with data fields on the row axis now lays out the way Excel does — a separator row opening each value, data fields stacked beneath, each outer field’s subtotal split below its group.
- An exported pivot with data fields on the row axis and no other row fields no longer produces a file Excel repairs on open.
- An exported pivot with data fields on the column axis and non-compact outline row fields no longer serializes blank-separator and opener rows as
<i t="default">, which Excel repairs on open. - A pivot whose source loses a column referenced by a calculated field no longer exports a file Excel repairs; the dangling reference is rewritten to the
#NAME?literal, as Excel does. - A pivot calculated-field formula combining a double-quoted string literal (containing an apostrophe) with a single-quoted field name is no longer mis-tokenized.
- A pivot’s page-field (report-filter) caption cells are now protected from range and structural edits, consistently with single-cell writes.
- An exported pivot’s styles table now matches Excel’s two-
cellXfform, so unstyled cells no longer inherit the data cells’ explicit-General number format. - Exporting a pivot that re-materializes to a smaller footprint no longer leaves stray style-only cells behind in the vacated region.
- A pivot shrinking its footprint no longer leaves a stale number format on the freed cells.
- Show Values As subtotals now partition by outer group for an inner base field, matching Excel.
- A pivot’s axis-relative Show Values As modes (Difference From, % Of, % Difference From, Running Total, % Running Total, Rank) now transform the subtotals on the axis across from the base field instead of leaving them raw — e.g. with Region on rows, a Category-with-subtotals › Product column axis, and Difference From East, the Category Total column shows
30 − 4 = 26rather than the raw West sum, matching Excel. - The axis-relative Show Values As modes based on an outer row or column field now transform the subtotals nested inside that base field instead of leaving them raw, matching Excel.
- The axis-relative Show Values As modes now also transform the intersection cells where a row subtotal meets a column subtotal (both axes subtotaled), each like an ordinary cell of its block, instead of leaving them raw, matching Excel.
- A Show Values As Difference From / % Of / % Difference From whose fixed base item is then hidden on the base field now renders
#N/Aacross that whole field, matching Excel, instead of comparing against the wrong visible item or falling back to raw aggregates. - A pivot value shown as % Of a
(previous)/(next)base item on an outer axis now renders the no-neighbour boundary item’s subtotal cell as 100% (matching its detail cells and Excel) instead of blank; Difference From / % Difference From stay blank there, as before. - % of Parent Row Total / % of Parent Column Total on a single-level row (resp. column) axis now divide each cell by its column (resp. row) grand total, so the columns (resp. rows) sum to 100%, matching Excel; previously the two axes were swapped, so % of Parent Row Total behaved like % of Row Total.
- % of Parent Row Total / % of Parent Column Total on a multi-level axis now show 100% in every column (resp. row) of the grand-total line instead of a wrong fraction, matching Excel — that line is its own parent, so each cell is its own value over itself.
- % of Parent Total based on a field whose own subtotals are turned off now divides each inner subtotal and leaf by the base group’s true total, matching Excel: a non-SUM measure such as Average or Max divides by the real group aggregate rather than a sum of the inner values, and a base group containing an error is left blank (Excel propagates the error through the group total).
- A pivot’s parent-relative Show Values As modes (% of Parent Row Total, % of Parent Column Total, % of Parent Total) now transform the subtotals on the axis across from the base field, and the row-by-column subtotal intersections, instead of leaving them raw, matching Excel.
- The x14
hideValuesRowpivot extension now round-trips through load and export. - A declared pivot style’s subtotal shading and bold now land on the actual subtotal rows in data-on-rows and mid-position-Values layouts instead of on the data rows (
PivotTable.rowRoles/colRolespreviously reported the wrong rows). - Two pivot tables sharing one cache keep sharing it through a JSON round-trip of the exported JSF; previously the shared cache split into independent copies, so refreshing one no longer refreshed its siblings.
- A pivot refresh now detects overlap with another pivot table’s cell-less regions (report-filter and empty rows), and when it cannot complete it refuses atomically instead of leaving the model half-updated.
Workbook.moveCellsnow relocates a whole pivot table when the source range fully encompasses it, matching Excel’s cut/paste of an entire pivot table: the output moves to the destination (same sheet or another), stays live and refreshable, and the cache source is left untouched. Previously any move overlapping a pivot’s output was refused; a move covering only part of a pivot table, or landing on another, still is.- A pivot caption/label filter now matches the item’s number-formatted label instead of the raw value, and is a no-op on date fields (which Excel filters by date, not by label).
GETPIVOTDATAnow accepts a criterion naming a report-filter (page) field’s currently selected item, treating it as a no-op qualifier and returning the value instead of#REF!. Naming a non-selected item, or a page field showing(All), still returns#REF!— matching Excel, which never reaches through the page filter to a slice the pivot is not displaying.- A pivot table loaded with duplicate field-item references (e.g. from LibreOffice) no longer re-exports a file Excel repairs on open; the duplicate references are dropped on load, as Excel’s own repair does.
- An external sheet that is listed but never cached now keeps its
noSheetDatamarker through the JSF round-trip, matching Excel’s shape. alternateUrlsandpathMissingon external references are now preserved throughWorkbook.toJSF(), so a round-tripped file stays re-resolvable by Excel.Workbook.insertCells(range, shift)no longer breaks a referenced range to#REF!when the insert pushes it against the last row or column; the range’s leading edge advances and its trailing edge clips at the sheet edge, matching Excel.Workbook.insertRows/insertColumnsno longer silently destroy non-empty cells pushed past the bottom or right edge of the sheet; they refuse the edit (as Excel does) and, for an edge-touching reference, clip it instead of breaking it to#REF!— matching the boundedinsertCellspath, including whole-row/whole-column references.- Full-axis
Workbook.deleteRows/deleteColumnsno longer corrupt whole-row/whole-column references past the deleted band:=SUM(E:E)after deleting columns B:C now becomes=SUM(C:C)instead of=SUM(B:C). - A bounded
insertCells/deleteCellsoverlapping a what-if data table is now refused (like the full-row/column guards) instead of silently corrupting the table, and its references are adjusted when the whole block shifts. - A what-if data table whose input cell lives on another sheet no longer computes wrong results after rows are inserted on that sheet or the sheet is renamed.
- A what-if data table that reads another table’s output through an intermediate helper cell no longer shows stale results that never self-correct.
- Moving the rows or columns that hold a pivot table’s whole source range no longer permanently breaks the pivot table’s refresh; the cache’s source reference follows the moved block instead of being silently cleared.
- The
@implicit-intersection operator now works in native GRID-mode workbooks instead of evaluating to#NAME?(it already worked in Excel- and Google-mode workbooks). - Entering a formula that references circular defined names (e.g.
foo==bar,bar==foo) no longer crashes with a stack overflow; the cell evaluates to the usual circular-reference#NAME?error.
Performance since v17.0.0-beta.1
Section titled “Performance since v17.0.0-beta.1”- Data tables now recompute only when something they depend on changed (source formula, parameters, input cells, or any cell those transitively read), and at most once per recalculation, instead of on every recalculation. This holds even for a dynamic source reference (
INDIRECT/OFFSET), whose per-scenario resolved targets are recorded as the table’s dependencies — except inside an iterative-calculation cycle, where those targets cannot be tracked and the table still recomputes every time, conservatively.
Dependency upgrades since v17.0.0-beta.1
Section titled “Dependency upgrades since v17.0.0-beta.1”- Upgrade
@borgar/xlsx-convertto^5.6.1: cells carrying only formatting (a style but no value) are now preserved on load, so they survive a load/save round-trip. - Upgrade
@grid-is/waspiaryto^1.17.0, bringing reference-rewrite fixes (the cross-sheet#REF!qualifier, and whole-row/whole-column reference handling on insert and full-axis delete) and parser support for the structured-reference@this-row forms. - Upgrade
@grid-is/jsf2xlsxto^2.3.3, correcting the exported pivot styles table to Excel’s two-cellXfform, and fixing the round-trip of single-argumentIFNAandIFERROR. - Upgrade
@jsfkit/typesto^2.5.0for the external-reference field declarations the externals round-trip fixes depend on.
v17.0.0-beta.1 (2026-06-29)
Section titled “v17.0.0-beta.1 (2026-06-29)”The second 17.0 beta, with further pivot-table fixes following beta.0. Still subject to further breaking changes before the stable release.
Features
Section titled “Features”Workbook.deleteCells(range, shift)andWorkbook.insertCells(range, shift)implement Excel’s “Delete cells → shift up/left” and “Insert cells → shift down/right” for an arbitrary bounded rectangle (e.g.B3:C5), the bounded-rectangle counterparts to the existing full-row/columndeleteRows/deleteColumns/insertRows/insertColumns. Surviving cells slide in (delete) or displaced cells push out (insert) to fill or open the gap; this workbook’s own formulas are rewritten in place, with references to removed or pushed-off cells becoming#REF!, and aRewriteFormulacallback is returned to adjust other workbooks’ references. Overlapping tables, pivot tables, and merges move whole, grow, or dissolve per Excel’s rules; an edit that would tear a table or pivot table, or push non-empty content off the grid, is refused.- New
PivotTable.pageAreareports the geometry of the page-field (report filter) area that sits above a pivot’s output range, so a renderer can locate and style those cells. - New
PivotTable.rowRoles/PivotTable.colRoleslist which output rows and columns are subheadings or subtotals (as 0-based offsets from the output’s top-left, each with its kind and 1-based nesting level), so a renderer can apply a declared pivot style’s subheading and subtotal styling.
Bug fixes
Section titled “Bug fixes”- What-if data tables now evaluate each scenario with a full model recalculation (substitute the inputs, recalculate, read the source cell), matching Excel. Previously only the source formula’s own AST was re-evaluated, giving wrong results whenever it reached the input indirectly — through other formula cells,
INDIRECT/OFFSET, defined names, cross-sheet references, volatiles, spills, or iterative cycles. - A custom workbook theme now survives model serialization;
deserializeModelpreviously dropped it and reverted every workbook to the default palette. Already-serialized models recover their theme on load, since the data was present all along and only the read path was at fault. - A pivot table’s saved output cells are now kept as-is on load instead of being recomputed, matching Excel — computed output fills only the cells the file left empty. A cache flagged
refreshOnLoad="1"is instead rebuilt from its worksheet source and re-rendered once at load time. - Refreshing a pivot cache that holds a derived field (a calculated field or a derived grouped field) no longer throws when the source columns are reordered, renamed, added, or deleted. The derived fields are kept and reconciled against the new column set, matching Excel; previously the presence of any derived field, even one unrelated to the change, aborted the refresh.
- The single-data-field pivot header (“corner”) cell now matches modern Excel: the data field’s display name (e.g.
Sum of Sales) in every layout, the bare field name only on pivots carrying a legacy AutoFormat, and an empty cell when the data field has no name. Previously it wrote the bare cache-field name unconditionally. - “Show items with no data” (
showAll) densification on load now matches Excel: dense by default for ordinary discrete fields, listing only items that survive the pivot’s active filters (a caption filter is evaluated against the field’s full item set), and including orphan cache items as empty “no data” rows. - Pivots mixing compact and tabular row-field layouts now match Excel: an outer-compact mix collapses to one compact “Row Labels” column, an outer-tabular mix captions its first column “Row Labels”, and a tabular outer field writes its group label on the group’s first output row. Field-name row captions appear only on row-only pivots whose row fields are all tabular with
compactDataoff. - A pivot that places the Values pseudo-field at a mid position among its column fields now lays the data fields out as interleaved sub-blocks at that depth, with the field-button row in raw field order, the way Excel does. Previously any non-first Values position was treated as innermost.
- A Values-last multi-data pivot now serializes its
colItemswith the data field varying fastest (df-innermost interleaving), matching Excel. Previously even a dormant load→save rewrote them into a df-major order Excel does not use. - A Values-outer pivot’s column axis-label row no longer drops the
Valuesbutton: it now readsValuesahead of the real field buttons, matching Excel. - A multi-data pivot whose Values pseudo-field sits on the row axis now lays the data fields out as row blocks — a synthetic “Values” row-label column, with per-data-field subtotal and grand-total captions — matching Excel. Previously the row-axis Values position was ignored and the data fields were always emitted on the column axis.
- A multi-data pivot with no column field now omits the synthetic “Values” caption row — the data-field names form the header row — matching Excel, instead of emitting an extra caption row that pushed the body down.
- On refresh, a pivot’s header-row offsets (
firstHeaderRow/firstDataRow) are re-derived from its definition so the output lands where Excel’s refresh would, regardless of the stored offsets: an over-reserved blank header row is removed, and a multi-data pivot’s missing reserved Values header row is rebuilt (instead of collapsing to a single data field and inventinghideValuesRow="1"on export). - Pivot column widths are sized to the formatted value text (currency, percent, and date number formats) instead of the raw number’s digits, so formatted value columns are no longer too narrow.
- Date fields on a pivot’s row or column axis now keep their number format on refresh, so header cells show formatted dates instead of raw serials. Previously only data, subtotal, and grand-total cells kept the format through re-materialization.
- Pivots with report filters now emit their page-field cells — a caption/selected-item cell pair per page field, in Excel’s single-column stack or wrapped multi-column grid — in the rows above the output range. Previously the report-filter area was omitted entirely.
- A pivot page-field (report filter) selection cell now carries the field’s own number format, so a date filter shows a formatted date instead of a raw serial.
- Pivot structural cell styles now match Excel: the column-caption dropdown-button flag is stamped on the axis-label row and only when the pivot has a real column field, and row-label left alignment is written only when the row axis has at least one compact field (not on fully-tabular layouts).
- Classic (
gridDropZones) pivots now show field-name captions on their row and column axes instead of the modern literalRow Labels/Column Labels, matching Excel. - Pivot calculated-field formulas now evaluate through the engine’s normal formula path, removing divergences from Excel’s coercion semantics that the old per-record closure tree had drifted into (e.g. unary-plus coercion on text, and empty-string arithmetic).
- Exported pivots now stamp
showAll="0"on axis fields that were materialized sparse (“show items with no data” off). Excel reads an absentshowAllas dense, so refreshing a previously-exported pivot re-densified the layout to the full item set and overflowed the saved footprint. - A fully non-compact (tabular/outline) pivot now writes the table-level
compact="0" compactData="0"attributes on export. Excel derives the row-label caption from these, so without them an Apiary-saved tabular pivot came back retitled (field names →Row Labels) on its first refresh. - A grouped (date/range/discrete) pivot field now exports its full item universe in
<items>(backfilled from the field group’s items), not just the materialized subset. Excel rejects a grouped field whose items list omits any bucket. - A pivot field carrying an explicit
defaultSubtotal: falsewithoutsubtotalFunctionsno longer gets a contradictory trailing default-subtotal item on export — a combination Excel offers to “repair” once the field also carriesshowAll="0". - Exporting a page-fields-only single-data pivot no longer produces a file Excel offers to repair: its
<location>geometry (anchor, header/data offsets, and the blank separator below the page area) now matches what Excel writes. - Exported pivots now compress members repeated from the previous item of
<rowItems>/<colItems>into ther(“repeated item count”) attribute, the way Excel writes them. Without it, Excel drew phantom expand/collapse buttons on the blank continuation label cells of saved multi-row-field pivots until the table was refreshed. Workbook.deleteRows/deleteColumnsnow match Excel in guarding a pivot table’s output range. A deletion that partially overlaps a pivot table is refused (throwsPivotEditBlocked, like the analogousinsertRows/insertColumnsguard); a deletion that fully encompasses a pivot table removes it (and its cache if not also referenced by another pivot table).
Performance
Section titled “Performance”- Per-call function and operator timing now reads
performance.now()only when ametricsCallbackis set, removing a small per-call cost from the common no-metrics path (including hot inner loops). Behaviour is unchanged when metrics are enabled.
dist/now ships code-split output alongside theindex.{js,cjs,d.ts,d.cts}entry bundles. The public entry points (exports,main,module,types) and what you get fromimport '@grid-is/apiary'are unchanged; only consumers reaching pastexportsintodist/are affected, since chunk filenames are now content-hashed.
v17.0.0-beta.0 (2026-06-11)
Section titled “v17.0.0-beta.0 (2026-06-11)”First beta of the 17.0 major line. Pivot tables — the headline 17.0 feature — land here, in beta: some known kinks are still being ironed out. Subject to further breaking changes before the stable release.
Breaking changes
Section titled “Breaking changes”(Note, these are breaking change since v17.0.0-alpha.0; note also the breaking changes reported for that version.)
Operations on Workbook may now throw new errors when they conflict with pivot-table output:
PivotEditBlocked— thrown byinsertRows/insertColumnswhen the insert lands strictly inside a pivot’s output area, bymoveRows/moveColumnswhen the move’s source or destination crosses a pivot’s boundary, and bymoveCells/clearCellswhen the affected range overlaps pivot output. Excel’s GUI refuses the same operations (usually silently); Apiary throws so the host can surface the refusal.PivotCellWriteBlocked— thrown byWorkbook.editCell(and thewritewrappers around it) when the target cell falls inside a pivot’s output region.PivotOutputOverlapsData— thrown by a pivot cache refresh when the refreshed pivot’s output would grow onto populated cells previously outside it.
StyleManager now reserves styles index 0 as the workbook default style (XLSX cellXf 0). A workbook built from scratch seeds slot 0 with the empty style {}, so workbook.styles.length starts at 1 and the first style created via findOrCreate gets index 1 (previously 0). Exported JSF for styled built workbooks carries the {} default at styles[0]; unstyled built workbooks still export with no styles array. Consumers should resolve styles through cell s indices rather than assuming the first created style sits at index 0.
Features
Section titled “Features”-
Pivot table support (beta — some known kinks are still being ironed out):
- Evaluation matches Excel: all OOXML aggregations and
showDataAsmodes, calculated fields, grouped fields, label/value/top-N filters, custom-list andautoSortScopesorting. - The
GETPIVOTDATAspreadsheet function is now supported. PivotManager.refreshCacherefreshes a pivot cache from its source; structural edits adjust pivot output and cache source ranges; pivots round-trip through JSF and XLSX.- Look up pivots via
workbook.pivots.get(name, sheetName?)andworkbook.pivots.findAt(sheetName, row, col). - Visual pivot styling is not baked into cells: as in Excel, only the declared pivot style name is stored, and resolving it to an appearance is the renderer’s job.
- Evaluation matches Excel: all OOXML aggregations and
-
Formula parsing accepts more of Excel’s grammar (via
@grid-is/waspiary1.13.0): the@implicit-intersection prefix operator and the structured-reference@this-row shorthand, reference operators and calls after parenthesised references ((A1):B2,(A1)#,(A1)(B2), …), and workbook/sheet prefixes on structured references ([0]Sheet1!Table1[Col]). Calling a parenthesised non-lambda reference, e.g.(A1)(B2), evaluates to#REF!as in Excel.
API changes
Section titled “API changes”- New
StyleManager.defaultStylegetter andsetDefaultStyle(style)method read and replace the workbook default style (slot 0). The default cannot carryextendsStyle. - New
recalcAll?: booleanonAddWorkbookOptionsforces (or, whenfalse, suppresses) a full recalculation when the workbook is attached. The default honours the file’sfullCalcOnLoadsignal (see Bug fixes).
Bug fixes
Section titled “Bug fixes”<calcPr fullCalcOnLoad="1"/>(ECMA-376 §18.2.2) is now honoured: attaching a workbook that carries the flag (and is not in manual calculation mode) performs a full recalculation instead of trusting cached values as-is, matching Excel. Previously the flag was ignored, which could leave formula cells without a usable value when the writer emitted empty cached values. The flag is cleared after a successful full recalculation, as the spec requires.ValueSnapshot.applyTono longer leaves a stale cell node in the R-tree when it rebuilds a spill anchor that had been collapsed to a value cell beforehand; previously a later value write over the restored anchor tripped an internal invariant.- A formula written via
editCellto a blank-but-formatted cell that a pre-existing formula depends on now evaluates. Previously its dependency-graph vertex kept pointing at a throwaway cell object created at load, so reads returnednulland recalculation recorded an “Invariant violation” model error, with no recovery even via a full recalc.
v17.0.0-alpha.0 (2026-05-30)
Section titled “v17.0.0-alpha.0 (2026-05-30)”First alpha of the 17.0 major line. Subject to further breaking changes before the stable release; notably, pivot-table support is still to come.
Breaking changes
Section titled “Breaking changes”Structural edits and writes that would change part of a what-if data table now throw DataTableEditBlocked (exported from the package index) instead of silently corrupting the table. Excel refuses each of these edits in its UI (via a modal alert or a silent no-op); Apiary surfaces the refusal so the host application can decide how to react. The refused edits, matching Excel’s empirically verified rules, are:
- row/column inserts strictly inside a data table’s block (the range spanning its source cell, parameter cells, and result range);
- row/column deletes overlapping the block without covering the whole result range (covering it removes the table, like Excel);
moveCells/moveRows/moveColumnstaking part of the block (a move must contain all of it — the whole table then moves) or landing on part of the result range (landing on all of it removes the table);- writes (
editCelland thewritewrappers) targeting a result cell, andclearCellscovering part but not all of the result range.
The error carries dataTableRef (the A1 range of the table’s result cells) and operation (a stable DataTableBlockedOperation identifier of the refused edit kind, e.g. 'insert-rows' or 'move-cells-onto') for programmatic handling, e.g. a localized host-application message.
The source and parameter cells remain freely editable, and the table recomputes from the new values. Allowed structural edits now also maintain the table’s metadata — previously any insert/delete/move silently desynchronized it.
Transient writes and “reset” are gone, replaced by value snapshots (see below). Model.write, Model.writeMultiple, and Workbook.write still exist but are now thin wrappers around Workbook.editCell. They are persistent edits, not transient overlays. Notable differences in behaviour:
-
One state, no
reset. Each write mutates the workbook in place; there is no separate transient state to enumerate or roll back. To undo a value edit later, capture aValueSnapshotbeforehand and apply it to revert. -
No formula neutralization option. Writing a value to a formula cell now unconditionally replaces the formula with that value. The
neutralizeFormulaOnSingleCellWriteflag is removed. -
No range writes.
Model.write/Workbook.writeno longer accept a multi-cell range reference. To assign values to many cells, useModel.writeMultiple([[refA, val], [refB, val], …]); for a real spill, useWorkbook.editCellwith an array formula. -
No writes inside merges.
Model.writeetc. now wrapWorkbook.editCell, which throws when the target is a non-anchor cell of a merge range. Write to the merge anchor instead, or unmerge first. -
No writes to defined names.
Model.write/Model.writeMultiple/Workbook.writeacceptstring | A1Referenceand throw on a defined-name reference (including a string that parses to one):- The “named values” feature (writing a literal value as a defined name’s formula) is gone.
Workbook.editCellon a name reference now requires an explicitfformula - To write through a chained defined-name reference, resolve first:
const ref = Reference.from(name, { ctx: model }).resolveToNonName()and thenmodel.write(ref, v) - Existing workbooks with literal-formula defined names still load and evaluate read-only
- The “named values” feature (writing a literal value as a defined name’s formula) is gone.
The following public APIs are removed:
Model.reset,Workbook.reset,WorkSheet.reset.- The
'reset'model event. Model.writes,Workbook.writes,Model.writeState,Workbook.writeState, theModelStateTreetype, and theGRID.SCENARIO()spreadsheet function. No replacement: these all reported transient write state, which no longer exists.=GRID.SCENARIO()now resolves to#NAME?.- The
neutralizeFormulaOnSingleCellWriteandresetproperties ofWriteOptions. The surviving properties areforceRecalc,skipRecalc, andskipVolatiles. Cell._vandCell.resetValueBoxed(the per-cell reset value).
Serialization: the _rangeWrites and _spillResetState fields and the per-cell reset value are no longer written. v17.x still loads models serialized by older Apiary versions; the dropped fields ride along in the payload and are silently ignored. Older Apiary versions cannot load v17.x serializations.
The RecalcUreachedEvent type is renamed to RecalcUnreachedEvent (the old name was a typo). The event payload is unchanged.
Features
Section titled “Features”- Value snapshots:
ValueSnapshot.capture(model)returns a reusable snapshot of cell and defined-name values (boxed, so number formats are preserved) and spill matrices across every workbook; formulas are captured as fingerprints only.snapshot.applyTo(model, { skipRecalc?, includeDeferred? })restores values and spills, then triggers a recalc. Any cell or name whose formula changed (added, edited, or removed) since capture is skipped, and one WARNING-levelModelErrorlisting every affected cell/name is recorded per workbook. The check is per-position and best-effort: a structural edit that rearranges cells may produce a partially-warned, partially-corrupted revert.- Styles and structural edits (row/column inserts/deletes, sheet adds/removes/renames,
moveCells) are out of scope and invalidate the snapshot; applying after such edits may leave the model in an incorrect state with no per-cell WARNING. For guaranteed-correct reverts across formula or structural edits, serialise the model (binary, JSF, or XLSX) and restore from that.
API changes
Section titled “API changes”Workbook.editCellis the canonical cell-mutation entry point: it keeps recalc bookkeeping in sync automatically.Model.write,Model.writeMultiple, andWorkbook.writeare now thin wrappers around it.Cell.userZis now marked@internaland excluded from the public.d.tstype. To read the effective number format useCell.z, orCell.stylefor the fullStyleRelaxed(which includes the user-assigned format undernumberFormat). To set a number format useWorkbook.editCell({ z })orcell.style = { ... }. No runtime change.- The
RewriteFormulacallback returned by structural-edit methods (deleteRows/deleteColumns/insertRows/insertColumns/moveRows/moveColumns/moveCells) gains two context parameters:(formula, formulaSheet, formulaWorkbook) => string. Callers that apply the returned rewriter to formulas in another sheet or workbook must now pass that context. Previously the rewriter assumed every unprefixed reference lived in the mutation’s source sheet/workbook, so it corrupted formulas applied elsewhere;moveCellsin particular returned a rewriter that was a silent no-op across workbooks.
Bug fixes
Section titled “Bug fixes”- XLSX export font fix (via
@grid-is/jsf2xlsx2.2.0): the cell style cascade did not resolve fonts correctly, so cells could export with the wrong typeface.
Packaging
Section titled “Packaging”- Bumped Excel import/export dependencies:
@grid-is/jsf2xlsx^2.1.0→^2.2.0,@borgar/xlsx-convert^5.2.0→^5.3.0, and@jsfkit/types^2.2.0→^2.4.0.
v16.1.0
Section titled “v16.1.0”Features
Section titled “Features”Workbook.calcModeis now a public getter/setter. Reading it returns the workbook’s current calculation mode ('auto','autoNoTable', or'manual'). Setting it to'autoNoTable'also enables data-table deferral (equivalent toModel.deferDataTables = true).
Bug fixes
Section titled “Bug fixes”deleteRowsanddeleteColumnsno longer rewrite formula references on sheets other than the one being modified. Previously, unqualified references on every sheet were treated as if they lived on the target sheet.- Loading JSF containing external workbooks whose defined names carry no value no longer crashes with “Cannot set defined name with invalid name or empty formula”.
Model.writeMultiplenow correctly honours theskipVolatilesoption. Previously the option was silently ignored, causing volatile functions such asRAND()andNOW()to be recalculated even when the caller asked to skip them.
v16.0.2
Section titled “v16.0.2”API changes
Section titled “API changes”Cell.editandDefinedName.editare now@deprecatedaliases that forward to internal_editmethods. Consumers should useWorkbook.editCellinstead —editCellkeeps the dependency graph and recalc bookkeeping in sync, which the raweditpaths do not. No runtime behaviour change.
Bug fixes
Section titled “Bug fixes”Workbook.editCell({ v: '#N/A' })(andCell.edit) no longer coerces error-looking strings to error values —vis preserved as a string, matching theCellconstructor. Coercion still happens when the input includest: 'e'.Workbook.editCellno longer clears existingModelErrors on a cell before its per-branch validation guards run, so a formula edit rejected by the merge-follower guard now leaves prior errors intact.toJSFtyping fix:External.namesentries are typed asExternalDefinedName(wasJSFDefinedName, structurally compatible but wrong). Type-only fix, no behaviour change.
Performance
Section titled “Performance”Model.fromJSFexternal-load loop is no longer O(externals × main-cells)._addExternalsdefers per-externalrecalculate()andoptimize()to a single trailing pass; on a real 125-external, 18k-cell workbook,Model.fromJSFdrops from ~16s to ~2s. Callers passingrecalcVolatiles: false, recalcErrors: false(the round-trip harnesses) still see no recalc.
Packaging
Section titled “Packaging”numfmtmoved frompeerDependenciestodependencies(range unchanged,~3.2.2). Apiary usesnumfmtinternally and consumers never pass instances in or out of the API; the peer-dependency placement was incidental, and demoting it meansnpm install @grid-is/apiaryworks without consumers having to add a matchingnumfmtentry.@types/nodemoved fromdependenciesto optionalpeerDependencies(range:>=20.19.23 <21 || >=22.19.0 <23 || >=24.10.0). The publisheddist/index.d.tsstill references Node’sBuffer, so a TypeScript Node consumer needs@types/noderesolvable — but the consumer’s own pin now wins. Pure-JS consumers see no missing-peer warning thanks tooptional: true. The per-major minimum reflects the late-2025 DefinitelyTyped round that narrowedBuffer.bufferfromArrayBufferLiketoArrayBuffer; below that, our publicWorkbook.fromXLSX/Model.fromXlsxtypes are unsound.@borgar/xlsx-convertbumped to 5.2.0, picking up an XLSX color/theme parsing fix that surfaced throughModel.fromXlsxin an add-ins consumer (undefined is not an object (evaluating 'readColor(elm.children[0], …)')).
v16.0.1
Section titled “v16.0.1”API changes
Section titled “API changes”- Removed the
NamedStyleandStylere-exports from the package entry point. These were unintentionally exposed during the 16.0 cycle and were never meant to be part of Apiary’s public API; import them from@jsfkit/typesdirectly. Type-only change with no runtime impact.
Bug fixes
Section titled “Bug fixes”Modelnow emitsaddsheetwhen a sheet is added (previously dropped silently), and no longer emits a spuriousrecalcfor that operation.- Three independent deserialization-fidelity bugs in the binary cache, jointly affecting
autoNoTable-mode roundtrips, are fixed:Workbook._calcModeis restored from the payload (was silently reverting to'auto').Cells._dataTableCells(the data-table-anchor index) is repopulated, soiterDataTableCells()works on deserialized workbooks — unblocking theModel.deferDataTables = truesetter andModel.attachWorkbook’sautoNoTablehandling.Model._deferDataTablesand the deferral cell Sets (cellsToDefer,staleDeferredCells) are now serialized and restored, so a model in deferral mode no longer loses it through a roundtrip.
toJSFcompacts full-row/full-column spill ranges incell.F(A3:XFD3→3:3,B1:B1048576→B:B), eliminating spurious roundtrip diffs when a code path holds an expandedF.- Error subclasses now set their own
namefield, so stack traces,console.log,inspect(), and Sentry display the actual class name (e.g.FormulaSyntaxError) instead of the inheritedError. @jsfkit/typesmoved fromdevDependenciestodependenciesso it is installed transitively when consumersnpm install @grid-is/apiary. The publisheddist/index.d.{ts,cts}references@jsfkit/typesdirectly because those types appear in Apiary’s public API; consumers’ type-checking previously failed unless they happened to depend on@jsfkit/typesfor other reasons.typescriptmoved fromdependenciestodevDependencies. It is only used here as a CLI build tool (tsc --noEmit,tsup); nothing inlib/imports it and the published types do not reference it. There is no public-API reason for consumers to receive Apiary’s pinnedtypescriptversion transitively. Originally added todependenciesduring the apiary monorepo extraction without a deliberate placement choice.
Serialization
Section titled “Serialization”CURRENT_SERIALIZATION_VERSIONbumped 5 → 6 to carry the previously-omittedModel._deferDataTablesboolean and the two deferral cell Sets.MINIMUM_ACCEPTABLE_SERIALIZATION_VERSIONis unchanged — older payloads still load, with the new fields defaulted to off / empty.
Documentation
Section titled “Documentation”- JSF types referenced from the generated API reference (
NamedStyle,Note,Style,Theme,ThemeColorScheme,ThemeCustomColor,ThemeFontCollection,ThreadedComment) now link tojsfkit.github.ioinstead of being mirrored as local Apiary type-alias pages. EvaluateExpressionOptions.cropTois now documented.- Cleaned up a confusing JSF-normalizer docstring.
v16.0.0 (2026-04-30)
Section titled “v16.0.0 (2026-04-30)”Breaking changes
Section titled “Breaking changes”Workbook.viewsis now aViewManager(wasWorkbookView[] | undefined).Workbook.getViews()andWorkSheet.getViews()are removed;WorkSheet.viewsis now@internal. Per-sheet view state is now read/written viaworkbook.views.get(index).getSheetView(sheetName).Workbook.themeis now aThemeManager(always present), notTheme | undefined.WorkSheet.showGridLinesproperty removed; gridline visibility now lives onWorkSheet.views[].showGridLines(per-view).@jsfkit/types1.x → 2.x: colours are now structuredColorobjects instead of hex strings, themes are restructured, and several types are reshaped.Workbook.rowHeight(rowIndex, sheetName)now throwsSheetNotFoundErrorwhen the sheet does not exist (previously returned the default16silently), and consultssheet.defaults.rowHeightbefore falling back to the Excel default. The newWorkbook.columnWidth()follows the same precedence on its axis.WorkSheet.rowHeightsandWorkSheet.colWidthsfields removed. Per-row height and per-column width are now read fromWorkSheet.rowsandWorkSheet.columns(theGridSize[]span arrays).
Removed @deprecated symbols (each deprecation note already pointed at the replacement shown here):
Cell.hasValue()/DefinedName.hasValue()andCell.isBlank()/DefinedName.isBlank()removed. UsehasValueOrFormula().Workbook.getTables()andWorkbook.getTable(name)removed. Useworkbook.tables.getAll()andworkbook.tables.get(name).WorkSheet.getComments()andWorkSheet.getNotes()removed. Useworkbook.comments.get(sheetName)andworkbook.notes.get(sheetName).Model.fromDataremoved. UseModel.fromCsf.Workbook.lazyImportPromise,Model.lazyImportPromise,loadLazy(), and theloadLazyre-export removed. All four were already no-ops; just delete the calls.- A batch of
@deprecated @internalre-exports removed from the package entry point: formula-evaluation helpers (run,errorForCode,errorTable, …) and dependency graph helpers (cellToVertexId,wbNameToKey, …). Usecell.vertexId/definedName.vertexIdinstead ofcellToVertexId(cell).
(Several dependency-graph items previously marked @deprecated @internal (or not exported at all) are now part of the public API: VertexId, CellVertexId, NameVertexId, RangeVertexId, KnownVertexId, VertexIdSet. The helpers referenceToVertexId, vertexIdToCell, and vertexIdToReference are re-exported with @internal markers — available, but excluded from documentation.)
Features
Section titled “Features”- Views CRUD via
Workbook.views, with two new public typesViewManagerandWorkbookView. - Theme CRUD via
Workbook.theme(newThemeManager): read/write the theme as a whole, the 12-colour scheme, major/minor fonts, and custom colours. - Named-style CRUD via
styleManager.named(newNamedStylescollection), with case-insensitive lookup and original casing preserved onNamedStyle.name. Workbook.reorderSheet(name, newIndex)andWorkbook.copySheet(name, newName?).Tableis now a public type export with new methods (appendRow,insertRow,rename,rowRef) and a newdataRowCountgetter.TableManageris also exported as a type. (The runtime classes are not re-exported: instances come fromworkbook.tables.get(name)/workbook.tables.add().)- Public-API types now exported from the package entry point (previously reachable only via
Parameters<typeof model.write>[2]-style tricks):WriteOptions,AddWorkbookOptions,WorkbookOptions,ReplaceTableReferencesFn,ModelEventArgs,ModelEventType,ModelEventListener,EvaluateExpressionOptions,ModelMeta. NotesManagerandCommentsManagerare now exported so they show up in the generated public docs.Workbook.toXLSX(outputType?, options?)gains both arguments.outputTypeis'nodebuffer'(default) or'arraybuffer'for browser-compatible XLSX export.options(typed asToXLSXOptions) carries acompressionLevelsetting (DEFLATE 0–9), now defaulting to6, down from9— measurably faster export with negligible size impact on typical XLSX payloads.Workbook.columnWidth(columnIndex, sheetName)— analogous toWorkbook.rowHeight(). Returns the column width in pixels.Workbook.moveCellsandWorkbook.rewriteFormulasaccept an optionalrecalcNow(defaulttrue); passfalseto defer the trailingrecalculate().
API changes
Section titled “API changes”Workbook.editCell()added as the preferred name forwriteCellData()(which stays as a@deprecatedalias). Its parameter type simplifies to plainCellData, which now includes the optionalftfield.- Identifier casing standardized on uppercase
JSF/XLSX:Model.fromJSF,toJSF,csfToJSF,normalizeJSFForComparison,toXLSX,toXLSXFile,validateXLSXFilePath, plus typeXLSXOutputType. The old title-case spellings remain as@deprecatedaliases with identical signatures — no breaking change.
Bug fixes
Section titled “Bug fixes”moveCellsnow handles merged cells. Destination merges are dissolved silently (matching Excel Scripts and Office.js behavior), and cells moved out of a source merge no longer carry stale.Mmerge pointers.- Loading a workbook with a table whose name is not valid as a defined name (e.g.
T1,A1,R1C1) now sanitizes and uniquifies the name instead of dropping the table with aModelError. - Iterative calculation now converges correctly when the iterative set contains a spill; previously the calc ran to
maxIterationsevery time and dependents of unchanged spills could be flagged as false-positive circular references. getEntitiesno longer crashes on workbooks containing sheet-scoped defined names.SORTNsilently ignores out-of-bounds sort-column indices instead of returning#VALUE!, matching verified Google Sheets behavior.toJSFpreserves values in merged-cell “ghosts” (non-anchor cells);mergeCellskeeps styles on follower cells and only clears values, matching Excel.- Deserializing models serialized by older Apiary versions no longer crashes on missing
views/comments/notes/people/externals/rowsproperties or on thestable.csf->stable.jsfTable key rename. replaceTableReferencesnow correctly rewrites bare-name references to tables (not just bracketed structured references).- Dynamic dependencies of LAMBDA closures passed to
BYROW/BYCOL/MAP/REDUCE/SCANwithINDIRECT/OFFSET/ANCHORARRAYin the body are now tracked, so edits to referenced cells correctly trigger recalc. - Lambda parameter resolution during reference analysis no longer crashes on
INDIRECT/OFFSETinside directly-called lambdas or nested lambda scopes. toJSFno longer crashes on full-row or full-column spill ranges.rewriteFormulasskips unparseable formulas instead of throwing.- Loading CSF workbooks: the literal string
"#SPILL!"in a cell is no longer coerced to an error value, fixing an R-tree invariant violation that crasheddescribeWorkbookin some cases. - Cells in JSF with error-looking string values (
"#VALUE!","#N/A", …) but no explicitt: 'e'marker are preserved as strings, not coerced to errors.
Performance
Section titled “Performance”- Faster label detection in
describeWorkbookvia cheaper subordinate-style checks (weighted geomean ~23.5% faster, worst-case workbooks up to 43% faster).
Serialization
Section titled “Serialization”CURRENT_SERIALIZATION_VERSIONbumped 3 → 5 over the v16 cycle. The 3 → 4 step was a marker for shape changes that had already landed without a version bump (Tablecsf→jsfkey,cellsWithDynamicDependencies, notes/comments/views/tables), giving consumers that cached v3 payloads a proper signal to invalidate. The 4 → 5 step adds thenamedStylesfield and folds the legacy parallelrowHeights/colWidthsmaps into theWorkSheet.rows/WorkSheet.columnsspan arrays.MINIMUM_ACCEPTABLE_SERIALIZATION_VERSIONstays at 1 — older cached payloads still load.
Documentation
Section titled “Documentation”ModelError.toJSONAPI documentation cleaned up (the raw inferred-return-type property list is gone); the MDN link gets a readable link text.
Dependencies
Section titled “Dependencies”@jsfkit/types^1.4.1→^2.2.0(see Breaking changes for the structural shifts).@borgar/xlsx-convertand@grid-is/jsf2xlsxupgraded for XLSX import/export of named styles and the CSE array-formula flag, plus round-trip and repair-dialog fixes.@grid-is/waspiary→ 1.11.0 for structured-reference bare-name rewrite.
v16.0.0-rc.0 (2026-04-16)
Section titled “v16.0.0-rc.0 (2026-04-16)”First release candidate for the 16.0 major line. Covers the deprecation cleanup, API additions, and fixes landed since v16.0.0-alpha.0.
Breaking changes
Section titled “Breaking changes”All of the following remove symbols that were already @deprecated, with their deprecation notes pointing at the replacement shown here.
Cell.hasValue()/DefinedName.hasValue()andCell.isBlank()/DefinedName.isBlank()removed. UsehasValueOrFormula().Workbook.getTables()andWorkbook.getTable(name)removed. Useworkbook.tables.getAll()andworkbook.tables.get(name).WorkSheet.getComments()andWorkSheet.getNotes()removed. Useworkbook.comments.get(sheetName)andworkbook.notes.get(sheetName).Model.fromDataremoved. UseModel.fromCsf.Workbook.lazyImportPromise,Model.lazyImportPromise,loadLazy(), and theloadLazyre-export removed. All four were already no-ops; just delete the calls.- A batch of
@deprecated @internalre-exports removed from the package entry point: formula-evaluation helpers (run,errorForCode,errorTable, …) and dependency graph helpers (cellToVertexId,wbNameToKey, …). Usecell.vertexId/definedName.vertexIdinstead ofcellToVertexId(cell).
(Some dependency graph classes and utilities were previously marked @deprecated @internal but are now instead made public: VertexId, CellVertexId, NameVertexId, RangeVertexId, KnownVertexId, VertexIdSet, referenceToVertexId, vertexIdToCell, vertexIdToReference.)
Features
Section titled “Features”- Public-API types now exported from the package entry point (previously reachable only via
Parameters<typeof model.write>[2]-style tricks):WriteOptions,AddWorkbookOptions,WorkbookOptions,ReplaceTableReferencesFn,ModelEventArgs,ModelEventType,ModelEventListener,EvaluateExpressionOptions,ModelMeta. - Named-style CRUD via
styleManager.named(newNamedStylescollection), with case-insensitive lookup and original casing preserved onNamedStyle.name.
API changes
Section titled “API changes”Workbook.editCell()added as the preferred name forwriteCellData()(which stays as a@deprecatedalias). Its parameter type simplifies to plainCellData, which now includes the optionalftfield.- Identifier casing standardized on uppercase
JSF/XLSX:Model.fromJSF,toJSF,csfToJSF,normalizeJSFForComparison,toXLSX,toXLSXFile,validateXLSXFilePath, plus typesXLSXOutputMapandXLSXOutputType. The old title-case spellings remain as@deprecatedaliases with identical signatures — no breaking change.
Bug fixes
Section titled “Bug fixes”moveCellsnow handles merged cells. Destination merges are dissolved silently (matching Excel Scripts and Office.js behavior), and cells moved out of a source merge no longer carry stale.Mmerge pointers.- Loading a workbook with a table whose name is not valid as a defined name (e.g.
T1,A1,R1C1) now sanitizes and uniquifies the name instead of dropping the table with aModelError.
Serialization
Section titled “Serialization”CURRENT_SERIALIZATION_VERSIONbumped 3 → 4 as a marker for shape changes that had landed without a version bump (Tablecsf→jsfkey,cellsWithDynamicDependencies, notes/comments/views/tables), giving consumers that cached v3 payloads a proper signal to invalidate.CURRENT_SERIALIZATION_VERSIONbumped 4 → 5 for the named-styles addition.MINIMUM_ACCEPTABLE_SERIALIZATION_VERSIONstays at 1 — older cached payloads still load.
Documentation
Section titled “Documentation”ModelError.toJSONAPI documentation cleaned up (the raw inferred-return-type property list is gone); the MDN link gets a readable link text.
v16.0.0-alpha.0 (2026-04-08)
Section titled “v16.0.0-alpha.0 (2026-04-08)”First alpha of the 16.0 major line. Subject to further breaking changes before the stable release.
Breaking changes
Section titled “Breaking changes”Workbook.viewsis now aViewManager(wasWorkbookView[] | undefined).Workbook.getViews()andWorkSheet.getViews()are removed;WorkSheet.viewsis now@internal. Per-sheet view state is now read/written viaworkbook.views.get(index).getSheetView(sheetName).Workbook.themeis now aThemeManager(always present), notTheme | undefined.WorkSheet.showGridLinesproperty removed; gridline visibility now lives onWorkSheet.views[].showGridLines(per-view).@jsfkit/types1.x → 2.0.0: colours are now structuredColorobjects instead of hex strings, themes are restructured, andGridSizegains an optionalsize.
Features
Section titled “Features”- Views CRUD via
Workbook.views, with two new public typesViewManagerandWorkbookView. - Theme CRUD via
Workbook.theme(newThemeManager): read/write the theme as a whole, the 12-colour scheme, major/minor fonts, and custom colours. Workbook.reorderSheet(name, newIndex)andWorkbook.copySheet(name, newName?).Tableclass is now a public export with new methods (appendRow,insertRow,rename,rowRef) and a newdataRowCountgetter.TableManageris also exported.toXlsxaccepts an optionaloutputTypeargument ('nodebuffer'(default) or'arraybuffer') for browser-compatible XLSX export. New exportsXlsxOutputTypeandXlsxOutputMap.NotesManagerandCommentsManagerare now exported so they show up in the generated public docs.Workbook.moveCellsandWorkbook.rewriteFormulasaccept an optionalrecalcNow(defaulttrue); passfalseto defer the trailingrecalculate().
Bug fixes
Section titled “Bug fixes”getEntitiesno longer crashes on workbooks containing sheet-scoped defined names.SORTNsilently ignores out-of-bounds sort-column indices instead of returning#VALUE!, matching verified Google Sheets behavior.toJsfpreserves values in merged-cell “ghosts” (non-anchor cells);mergeCellskeeps styles on follower cells and only clears values, matching Excel.- Deserializing models serialized by older Apiary versions no longer crashes on missing
views/comments/notes/people/externals/rowsproperties or on thestable.csf->stable.jsfTable key rename. replaceTableReferencesnow correctly rewrites bare-name references to tables (not just bracketed structured references).- Dynamic dependencies of LAMBDA closures passed to
BYROW/BYCOL/MAP/REDUCE/SCANwithINDIRECT/OFFSET/ANCHORARRAYin the body are now tracked, so edits to referenced cells correctly trigger recalc. - Lambda parameter resolution during reference analysis no longer crashes on
INDIRECT/OFFSETinside directly-called lambdas or nested lambda scopes. toJsfno longer crashes on full-row or full-column spill ranges.rewriteFormulasskips unparseable formulas instead of throwing.- Loading CSF workbooks: the literal string
"#SPILL!"in a cell is no longer coerced to an error value, fixing an R-tree invariant violation that crasheddescribeWorkbookin some cases. - Cells in JSF with error-looking string values (
"#VALUE!","#N/A", …) but no explicitt: 'e'marker are preserved as strings, not coerced to errors.
Performance
Section titled “Performance”- Faster label detection in
describeWorkbookvia cheaper subordinate-style checks (weighted geomean ~23.5% faster, worst-case workbooks up to 43% faster).
Dependencies
Section titled “Dependencies”@jsfkit/types^1.4.1->^2.0.0(see Breaking changes).@grid-is/waspiary->1.11.0for structured-reference bare-name rewrite.
v15.5.0 (2026-03-05)
Section titled “v15.5.0 (2026-03-05)”Features
Section titled “Features”- Cell notes CRUD via
NotesManager(workbook.notes):add,get,getByCell,update,delete
Bug fixes
Section titled “Bug fixes”AND/OR/XOR/NOTevaluation with strings and other edge-case arguments, matching Excel and Google Sheets behaviorTEXTJOINandJOINnow apply cell display formats (e.g. date formats) in Google mode
Interface changes
Section titled “Interface changes”These are technically breaking, but very minor and in very recent features, so did not demand a major-version bump.
CommentsManagernow throwsSheetNotFoundError(instead of plainError) for missing sheets, with a differently worded message.WorkSheet.notestype changed toNote[](no longer possibly undefined)
Dependencies
Section titled “Dependencies”- Update
@grid-is/jsf2xlsxto ^1.3.2, updating default column width and fixing extraneousactiveTab="0"attributes inworkbookViewelements.
v15.4.0 (2026-03-02)
Section titled “v15.4.0 (2026-03-02)”Features
Section titled “Features”- Respect ‘autoNoTables’ from XLSX file, plus
deferDataTablesoption for manual control- And more generally, mark any formula cells to be deferred in recalculation
- Threaded comments CRUD via
CommentsManager(workbook.comments)
Bug fixes
Section titled “Bug fixes”MATCHreturned#N/Ainstead of#REF!in Excel missing-sheet edge casetoJsfexport expanded external workbooks to nonexistent cells if referenced in ranges- Error-like strings (e.g.
#REF!) not treated as error values when loading styled cells from CSF - Default (named) styles were not preserved in
toJsfexport
Performance
Section titled “Performance”- Much faster worst-case
describeWorkbook(label-detection) performance
Documentation
Section titled “Documentation”- Update quickstart to use
Model.fromXlsxFileinstead of manualxlsx-convert
v15.3.1 (2026-02-23)
Section titled “v15.3.1 (2026-02-23)”Bug fixes
Section titled “Bug fixes”- Preserve formula type (array or not) when result is 1x1, and classify correctly on edit
- Serialization did not preserve views, annotations, rows, and externals
- Remove Node.js util imports so consumers need not polyfill for browser compatibility
Documentation
Section titled “Documentation”- Add changelog to public docs
v15.3.0 (2026-02-19)
Section titled “v15.3.0 (2026-02-19)”Features
Section titled “Features”- Excel data tables
- Function
SORTN getViewsmethod inWorkSheetandWorkbook- Google Sheets
FLATTENfunction - Merge/unmerge cells
Bug fixes
Section titled “Bug fixes”- Make
HYPERLINK()-derived hyperlinks ephemeral - Preserve external workbooks in JSF roundtrip
- Update comment and note cell refs on move, insert, and delete
- Wrap
fs/promisesimports in try-catch to avoid bundler errors
Performance
Section titled “Performance”- Speed up
describeWorkbookby pre-filteringparseDatecalls
Documentation
Section titled “Documentation”- Pare down public API, marking stuff
@deprecatedand/or@internal - Use code blocks in Typedoc-generated docs
v15.2.0 (2026-02-10)
Section titled “v15.2.0 (2026-02-10)”Features
Section titled “Features”- Support JSF cell notes and threaded comments
Bug fixes
Section titled “Bug fixes”describeWorkbookcrash on cross-workbook ref to formula cell- Support
QUERYwithCOUNT(DISTINCT value)
v15.1.1 (2026-02-10)
Section titled “v15.1.1 (2026-02-10)”Bug fixes
Section titled “Bug fixes”- Missed dynamic dependencies in lambda-calling functions at init time
- Preserve cells with explicit empty styles through round-trip
- Strip
numberFormatFromFormulafrom exported styles intoJSF
v15.1.0 (2026-02-04)
Section titled “v15.1.0 (2026-02-04)”Features
Section titled “Features”toJsftoXlsx- Add
fromXlsxmethods for direct XLSX loading - Native
QUERYwithoutpg-mem(calling it “feat” because it supports a lot that the old one did not) - Output style indexes on rows and columns
Bug fixes
Section titled “Bug fixes”- Real workbooks override external workbooks with same name
- Fix invariant error on insert after row is deleted
- Export
ftproperty intoCSFfor array formulas - Init recalc was skipped if
!recalcVolatiles && !recalcErrors - Load XLSX files in Excel mode by default
- Respect
recalcVolatilesoption during initial recalculation - Preserve text strings that look like errors in JSF loading
ERROR.TYPEdid not support modern error codesdescribeWorkbookthrows on JSF workbook with externals- Upgrade
xlsx-convertto 3.6.4 for bugfixes - Fix
handlerRelsto work with absolute paths - Fix external refs order
- The
rproperty on cells is optional
Breaking changes
Section titled “Breaking changes”- Remove
Flagsmodule andflagsexport
v15.0.0 (2026-01-14)
Section titled “v15.0.0 (2026-01-14)”Features
Section titled “Features”- Public
Model.attachWorkbook, and documentaddWorkbookas just a wrapper - Export
StyleManager - Export
CellDatatype - Add support for JSF workbook/worksheet view props
- Specific
Errorsubclass for invalid reference strings - Add missing detail messages on
#CALC!errors
Bug fixes
Section titled “Bug fixes”- Google-mode early-date date function discrepancies
Matrix.expandmakes fully-populated matrices, causing performance disasters- Nonresolving name in
LOOKUPvector arg borks reference analysis - Propagate
markDirtyto all dependents of spill ranges - Blank spill anchor missing in
Cells.getCells - Don’t propagate recalc from unchanged spill matrix
numberFormatFromFormulapropagationSUMIFSetc. wrong forMatrixwith defaulted regions- Incorrect swap in Brent root-finding setup
QUERYwithnow()not treated as volatile if no dependencies- GSDV blocker in extended range should not block other columns
- Evaluate array expressions with mismatched row widths as
#VALUE! - Fix default column-width units discrepancy
- Fix
Tableserialization property name mismatch - Fix serialization invariant violation
- Fix errors in
toCSFexport structures - Fix GSDV spill range inference: use offset not absolute row
- Fix regression in
evaluateExpression - Preserve CSF style array when loading workbooks
Performance
Section titled “Performance”- Add
vertexIdtoCellandDefinedName - Non-volatile
OFFSETandINDIRECTandANCHORARRAYwith dynamic dependencies
Breaking changes
Section titled “Breaking changes”- CSF type should have
filename, notname - Change
NameReference.widthand.heightto beundefined, not throw
Dependencies
Section titled “Dependencies”- Update
fxto 5.0 - Upgrade Waspiary to 1.10.0 to permit
RandCnames
v15.0.0-rc3 (2026-01-08)
Section titled “v15.0.0-rc3 (2026-01-08)”Features
Section titled “Features”- Add missing detail messages on
#CALC!errors - Export
CellDatatype - Specific
Errorsubclass for invalid reference strings - Add support for JSF workbook/worksheet view props
- Preserve CSF style array when loading workbooks
Bug fixes
Section titled “Bug fixes”- GSDV blocker in extended range should not block other columns
numberFormatFromFormulapropagationSUMIFSetc. wrong forMatrixwith defaulted regions- Incorrect swap in Brent root-finding setup
QUERYwithnow()not treated as volatile if no dependencies- Evaluate array expressions with mismatched row widths as
#VALUE! - Fix GSDV spill range inference: use offset not absolute row
- Fix
Tableserialization property name mismatch - Fix serialization invariant violation
- Fix errors in
toCSFexport structures
v15.0.0-rc2 (2025-12-10)
Section titled “v15.0.0-rc2 (2025-12-10)”Bug fixes
Section titled “Bug fixes”- Blank spill anchor missing in
Cells.getCells - Don’t propagate recalc from unchanged spill matrix
- Fix regression in
evaluateExpression - CSF type should have
filename, notname
Performance
Section titled “Performance”- Non-volatile
OFFSETandINDIRECTandANCHORARRAYwith dynamic dependencies
v15.0.0-rc1 (2025-12-08)
Section titled “v15.0.0-rc1 (2025-12-08)”Features
Section titled “Features”- Public
Model.attachWorkbook, and documentaddWorkbookas just a wrapper - Export
StyleManager
Bug fixes
Section titled “Bug fixes”- Google-mode early-date date function discrepancies
Matrix.expandmakes fully-populated matrices, causing performance disasters- Non-resolving name in
LOOKUPvector arg borks reference analysis - Propagate
markDirtyto all dependents of spill ranges - Fix default column-width units discrepancy
Performance
Section titled “Performance”- Add
vertexIdtoCellandDefinedName
Breaking changes
Section titled “Breaking changes”- Change
NameReference.widthand.heightto beundefined, not throw
Dependencies
Section titled “Dependencies”- Update
fxto 5.0
v14.13.0 (2025-11-19)
Section titled “v14.13.0 (2025-11-19)”Features
Section titled “Features”- Read JSF directly
Bug fixes
Section titled “Bug fixes”- Bugs in row and column deletion and moves
- Support R1C1 syntax in
INDIRECT - Handle changed serialization
- Replace
ERROR_SPILL'foo'placeholder with proper detail message - Serialize model with lambdas in arrays
VLOOKUP/HLOOKUPcan return non-up-to-date value- Direct call to
UPLUSfails with internal error - Recalculate affected formulas if
clearGsdvclears cells
Breaking changes
Section titled “Breaking changes”- Remove
resetvalue fromDefinedNameandCellInterface
Dependencies
Section titled “Dependencies”- Switch to
@jsfkit/typesfor JSF type definitions
- Install from, and publish to, npmjs.com registry
v14.12.0 (2025-10-17)
Section titled “v14.12.0 (2025-10-17)”Breaking changes
Section titled “Breaking changes”- Make
FormulaErrorandReferencefully immutable
Features
Section titled “Features”- Store origin cell in
FormulaError - Add and improve error-value detail messages
- Add detail messages to
#SPILL!errors
Bug fixes
Section titled “Bug fixes”- Make
Cell.isBlank()closer to what its name implies - Minor bugs in reference intersection and add a test
- Rename and deprecate
Cellmethods
Documentation
Section titled “Documentation”- Deploy public API docs to GitHub Pages site
- Remove internal stuff from docs
- Mention case-insensitivity in name parameters
v14.11.0 (2025-10-15)
Section titled “v14.11.0 (2025-10-15)”Features
Section titled “Features”- Recalculate error-valued cells at init time
- Remove defined name
Bug fixes
Section titled “Bug fixes”- Wrong spill-operator result for blocked spill
QUERYcoerces dates to string by local timezone- Support structured references in
INDIRECT IFresult should not be marked dynamic- Make apiary bin work again
v14.10.1 (2025-10-01)
Section titled “v14.10.1 (2025-10-01)”Features
Section titled “Features”- Allow disabling common-subexpression extraction
Bug fixes
Section titled “Bug fixes”- Fix 1904 epoch bug when converting JSF to CSF
v14.10.0 (2025-09-25)
Section titled “v14.10.0 (2025-09-25)”Features
Section titled “Features”- Export
FormulaSyntaxErrorandEvaluationError - Add
Model.empty()convenience method - Export recalculation option symbols
Bug fixes
Section titled “Bug fixes”ADDRESSwith invalid coordinates yields wrong results or throws exception- Use source number format in
TO_TEXT - Set number format in
TO_*functions XLOOKUPthird argument wrongly forced up-to-dateROWetc. fail to update when name ref updates- False-pos circdep in
SUMIF/AVERAGEIFon target range extension Workbook.iterFormulaCellshad a bug and a redundant sibling- Update JSF support
Documentation
Section titled “Documentation”- Add a quick start guide
- Add Starlight-based documentation system
- Add tutorial for creating a spreadsheet from scratch
- Document some methods on the
Modelclass
v14.9.5 (2025-09-01)
Section titled “v14.9.5 (2025-09-01)”Features
Section titled “Features”- Add support for loading and converting JSF format
v14.9.3 (2025-08-12)
Section titled “v14.9.3 (2025-08-12)”Bug fixes
Section titled “Bug fixes”CONVERTfailed to handle reference value argument
Dependencies
Section titled “Dependencies”- Bump
fxfrom 4.11.2 to 4.12.0 [bot] - Bump
msgpackrfrom 1.11.4 to 1.11.5 [bot]
v14.9.2 (2025-07-23)
Section titled “v14.9.2 (2025-07-23)”Bug fixes
Section titled “Bug fixes”- Fix defined names causing slow iterative calculation
v14.9.1 (2025-07-22)
Section titled “v14.9.1 (2025-07-22)”Version revoked due to incorrect fix.
v14.9.0 (2025-06-27)
Section titled “v14.9.0 (2025-06-27)”- Detect parent labels
v14.8.7 (2025-06-26)
Section titled “v14.8.7 (2025-06-26)”Bug fixes
Section titled “Bug fixes”- Label sequence confusion from sparse values at island top/left
Dependencies
Section titled “Dependencies”- Bump
component-emitterfrom 1.3.0 to 2.0.0 - Upgrade
pg-memto 2.6.15
v14.8.6 (2025-06-12)
Section titled “v14.8.6 (2025-06-12)”Bug fixes
Section titled “Bug fixes”- Leftmost-column data mistaken for a label sequence
v14.8.5 (2025-06-08)
Section titled “v14.8.5 (2025-06-08)”Bug fixes
Section titled “Bug fixes”- Skip over gaps in mixed-type label subsequences
v14.8.4 (2025-06-08)
Section titled “v14.8.4 (2025-06-08)”- Improve island-based label detection