Skip to content
Apiary

Changelog

  • Workbook.setSheetHidden hides, very-hides or unhides a sheet, and emits a hidesheet event 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 in workbook.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 their id, 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) and setName(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 from workbook.charts once no graphic shows it.

    addChart and setChart refuse 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; addChart and setPosition refuse 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.

  • The following now throw the new exported RewriteRefusedError when a name cannot be written into a reference. Previously, they threw FormulaSyntaxError, wrongly implying that the original formula did not parse. Code that catches FormulaSyntaxError to handle a refused name should catch RewriteRefusedError instead.
    • The functions returned by getReplaceSheetReferencesFn, getReplaceTableReferencesFn and getReplaceWorkbookFn, for the new name.
    • Model.rewriteFormulaAfterMove, when to names a sheet, workbook or directory that cannot be written into a reference prefix, such as one containing [.
    • Workbook.moveCells.
  • A recalcEvent root event 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 metrics or recalcEvent listener, now throws the new RecalculationInProgressError. Previously, the change could give wrong results. Make the change from a recalc listener, or after Model.recalculate returns, instead.
  • Assigning to WorkSheet.hidden directly is deprecated; use Workbook.setSheetHidden.
  • WorkbookView.setActiveSheetName now refuses a hidden or very hidden sheet, with the new ActivateHiddenSheetError. Show the sheet with Workbook.setSheetHidden first.
  • Workbook.removeSheet now 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 empty Sheet1 in 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 with WorkbookView.setActiveSheetName first.
  • 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, Criteria or Database, which a workbook file stores with an _xlnm. prefix, instead of evaluating to #NAME?.
    • Formulas reading a defined name that calls INDIRECT now follow later changes to the cells the name reads after the name is given a new formula with editCell or setDefinedName. Previously, they kept their value until every formula was recalculated.
  • 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 as SEQUENCE(1), instead of keeping the error.
  • 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 CHOOSE or IF, 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 INDIRECT call of a range address or an OFFSET call, or returns one through a function such as IF, now spills the range.
    • In Google Sheets mode, a formula that is a LET call 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, OFFSET or LET through IFS or SWITCH now always evaluates to #VALUE!. Previously, some such formulas spilled the range or evaluated to the value in the formula’s row or column.
  • Circular references and recalculation:

    • When a recalculation fails partway, such as when a recalcEvent listener 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)/1000 filled down D2: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 INDIRECT with relative R1C1 text, such as INDIRECT("R[-1]C",FALSE), is now reported as a plain circular reference, without an “Unexpected error evaluating formula” error.
  • 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 λογοσ!A1 for sheet λογος, and a table name also with ss for ß, such as STRASSE[Wert] for table Straß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 as A_s!A1 finding 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, İ!A1 no longer finds a sheet named i̇ (i followed 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!A1 loads as =Sheet2!A1.
    • Renaming a sheet or table, inserting or deleting cells, rows or columns, moving cells, and the functions that getReplaceSheetReferencesFn and getReplaceWorkbookFn return now rewrite a reference written in such a form, such as Masse for table Maße. Removing a name doesn’t yet rewrite such a reference.
  • 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:I6 onto C5:E6 under SUM(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.moveCells cannot rewrite a formula for the move, as in a workbook whose name contains [, it now throws RewriteRefusedError and changes nothing. Previously, it threw FormulaSyntaxError after dissolving merges in the moved range, without moving the cells.
    • Workbook.moveCells now throws ArrayEditBlockedError when 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.moveCells moves 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 writes SUM(F3:G4), not SUM(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.:D or 2.:.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$3 now writes 1:1:$A$2:$B$3, not 1:1:#REF!:$B$3.
    • Renaming a table no longer renames a LET or LAMBDA variable 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 LET or LAMBDA starts 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 references now 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.
  • Formula parsing:

    • In a LET or LAMBDA, a variable directly before an unquoted sheet-qualified reference, as in LET(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 sheet R to Mar. The same holds for a variable written in another case, such as ΛX for λx.
    • A LET that binds the same name twice, or a LAMBDA that names a parameter twice, is now rejected, as in Excel, also where the names differ only in case or as Excel folds them, such as x and X, ß and SS, or fi and fi. Previously, one of the bindings was lost.
    • A name now finds a LET variable as Excel does, and as it already found a LAMBDA parameter: ẞ finds ß, but SS no 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 in A1:.Sheet2!B2.
    • A name ending in a dot before a colon, as in SUM(Revenue.:B2), now reads as the name Revenue. and a range operator, as in Excel, instead of as the name Revenue and 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 as Sales[It''s] or Sales[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 FormulaSyntaxError and of the ModelError recorded for such a formula, and the detail of the #NAME? that Model.readValue and Model.evaluateExpression return for one.
  • 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 as Sales[[@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?
  • 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.setArrayFormula over 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, and CELL asked 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 describeWorkbook now 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:

    • GETPIVOTDATA now 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 Total row, 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 GETPIVOTDATA reads 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 Total and 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 with defaultSubtotal: 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 Amt and North 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 as m/d/yyyy h:mm and times as h: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.add now 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, so ab sorts before a-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 Total row. 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.
  • 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.
  • GETPIVOTDATA now 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).
  • GETPIVOTDATA now returns what Excel returns in the following cases:

    • For a source field’s name that several value fields summarize, such as "Sales" with Count of Sales and Sum 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, GETPIVOTDATA now returns #REF! for the item’s blank label row. Previously, it returned 0.
  • In Google Sheets mode, the following GETPIVOTDATA results 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 form GETPIVOTDATA(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!.
  • Outside Google Sheets mode, the following comparisons of text now match Excel:

    • The <, <=, > and >= operators and criteria (in COUNTIF, SUMIF and others) now set hyphens, dashes and apostrophes aside, so "<25|x" no longer counts 25-34|y.
    • The comparison operators and criteria now read ß as ss, and = criteria now ignore soft hyphens, as the operators already did.
    • XMATCH, XLOOKUP and SWITCH now match text as = does, and SORT, SORTBY and 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 as MATCH and XLOOKUP now tell a fullwidth letter from its ASCII counterpart, and the ideographic space U+3000 from a space, as Excel for Windows does.
  • Outside Google Sheets mode, text longer than 255 characters is now handled as in Excel:

    • MATCH, VLOOKUP, HLOOKUP and LOOKUP now 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, MINIFS and MAXIFS now return #VALUE! for a criterion longer than 255 characters.
  • Outside Google Sheets mode, MATCH now 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.
  • MATCH now returns the error of an invalid match type rather than the #N/A of 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, COUNTIFS and 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 (DSUM and the like) now do so, as Excel does.
  • INDIRECT now 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#")).

  • INDIRECT now 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"), while Book1.xlsx!name is still read)
    • text with whitespace at either end; in Excel mode, trailing spaces and line feeds are still ignored, as in Excel
  • SUBTOTAL(2, …) and SUBTOTAL(102, …) now skip an error cell in their ranges, as COUNT does, instead of returning the error.

  • Count arguments and text functions:

    • LEFT, INDEX, ROUND and 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 do WEEKDAY and WEEKNUM for their return type.
    • Outside Google Sheets mode, SUBSTITUTE now 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 is FALSE, blank or a number above -0.9999999 and below 1, and reads TRUE as instance 1, as Google Sheets does. Previously, it returned #VALUE! in both cases.
    • Outside Google Sheets mode, MID, MIDB, REPLACE and REPLACEB now return #VALUE! for a start or length past Excel’s limit on it: 32767 for REPLACE and REPLACEB, and 2^31-1 (2147483647) for MID and MIDB, or 2^31 (2147483648) for the start of MID. Previously, they returned text.
  • Number formatting, as TEXT applies it, has the following fixes:

    • Fraction formats now floor the integer part and carry a fraction that rounds to 1: # ??/16 formats 2.96 as 2 15/16, not 3 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+00 for 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.
  • 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, TBILLPRICE and sixteen others) now return #VALUE! for a 3-D reference in any argument, as Excel does, rather than #REF!.
    • In Google Sheets mode, MIRR over 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, PV at a rate of -1 now returns #NUM!, as Google Sheets does, not #DIV/0!.
  • Outside Google Sheets mode, XIRR now 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.
  • 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 IF or another function that evaluates only the arguments it needs.
  • Workbook.charts is now a Record<string, Chart> of JSF charts (the Chart type of @jsfkit/types), keyed by the chartId that the drawings hosting them use, instead of an array of ChartCSF. Look a chart up by its drawing’s chartId instead of searching the array by id. 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 support require of an ES module.

  • Workbook.toXlsx and Workbook.toXlsxFile, deprecated in favour of Workbook.toXLSX and Workbook.toXLSXFile, are removed. Call toXLSX and toXLSXFile instead, with the same arguments.

    toXLSX and toXLSXFile no longer take an options argument, and the ToXLSXOptions type 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.editCell no longer accept z (the number format) or zf (a number format assigned by a formula). Set the number format as numberFormat in the style, s, instead; s replaces the cell’s whole style, so leaving out numberFormat clears the number format.

  • Cell.z is read-only.

  • The deprecated edit method of cells and defined names is removed. Use Workbook.editCell instead.

  • 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 as BYROW(A1:B2,_xleta.SUM). Other formulas are stored as typed.
  • New functions:
    • the odd-coupon bond functions ODDFPRICE, ODDFYIELD, ODDLPRICE and ODDLYIELD
    • the regression functions LOGEST, GROWTH, PEARSON, RSQ and STEYX
    • the statistical tests T.TEST, TTEST, F.TEST, FTEST, CHISQ.TEST and CHITEST
    • the forecasting functions FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY and FORECAST.ETS.STAT
    • GROUPBY and PIVOTBY
    • ASC and DBCS
    • AREAS, in Excel and Canvas modes
  • Workbook.unsupportedFeatures lists 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 through toJSF and model serialization. See “Round-trip losses” in the documentation.
  • Charts in JSF input are now kept, and written out by toJSF, toXLSX and toXLSXFile, along with the drawings that host them. Like CSF charts did, they follow sheet renames, copies and deletions, and row, column and cell edits.
  • The new formulaForDisplay function returns a formula without Excel’s storage prefixes, such as the _xlfn. before CONCAT or the _xleta. before a function name used as a value, for showing it as a formula bar would.
  • Workbook.setDefinedName now 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.
  • 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]!MyName now 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: false in 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 s as a style index now applies that style’s number format; previously the cell could keep its old one.
  • An s write that changes or removes a cell’s number format now recalculates the formulas that depend on the cell, as a z write did; previously an s write 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:B20 below the data or the #REF! that fills a data table whose input cell is spilled into. A whole-column spill such as =A:A+1 is 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) with MyFact defined as LAMBDA(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 INDEX from an expression over a range, such as INDEX(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 SUM over that range or an OFFSET or INDIRECT pointing 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']]])
  • 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 SWITCH or IFS call 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 a LAMBDA:
    • 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.setDefinedName or editCell, such a name is now stored with the first sheet’s name (Sheet1!$B$5), and so now survives export to XLSX. B5 becomes the relative Sheet1!B5; write $B$5 for an absolute reference. The rewritten formula loses its typed spacing and literal forms: SUM(A1:A3) + 1.50 is stored as SUM(Sheet1!A1:A3)+1.5. setDefinedName leaves the formula as written when its qualifyUnprefixedReferences option is false.
  • 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 OFFSET or INDIRECT now keeps its cached value when that cell is written or sorted.
  • 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, including AREAS and INDEX with area_num, read its areas, and most other functions return the error Excel returns. HSTACK, VSTACK, TOCOL, TOROW, CELL, SHEET and SHEETS do not support unions yet.
  • INDIRECT of 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 a SWITCH expression or result, and as an operand, where it was previously blank, #VALUE!, #N/A or 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, LET or @, 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 SINGLE call 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!.
    • INDIRECT now returns #REF! for text that names a trimmed range, such as "G1:.G9".
  • A LAMBDA parameter 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/A in Google Sheets mode
  • Lambdas and lambda helpers:
    • MAP, BYROW, BYCOL, SCAN, REDUCE and MAKEARRAY now return #VALUE! for an error in place of the lambda, as Excel does, rather than that error; BYROW and BYCOL now return #CALC! when the lambda is omitted. In Google Sheets mode, all six now return #N/A for anything but a lambda taking the right number of arguments, as Google Sheets does, except that MAKEARRAY returns an error it is given.
    • Outside Google Sheets mode, MAKEARRAY now 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/A for all of these; an array is called element by element, where a boolean element is #VALUE!.
    • Calling a LET variable 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, a LET variable now returns #N/A and a defined name #NAME?, as Google Sheets does.
    • In Excel mode, calling a LET variable, LAMBDA parameter 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, since SPLIT is a Google Sheets function; previously it returned #NAME?, or for a name such as ARRAYFORMULA called the Google Sheets function. And =LET(CUBEVALUE,LAMBDA(x,x*3),CUBEVALUE(2)) now returns #NAME?, since CUBEVALUE is an Excel function that Apiary does not support; previously it called the variable.
    • Calling a LET variable, 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, REDUCE and SCAN over a range now pass each cell to their LAMBDA as a reference, as Excel does, so ROW, ISREF and 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.
    • REDUCE and SCAN with no initial value over a one-element array that a formula computes, such as A2+0 or SEQUENCE(1), no longer report a circular reference when their LAMBDA reads the formula’s own cell: as in Excel, they return the element without calling the LAMBDA.
  • Lookup and array functions:
    • Outside Google Sheets mode, VLOOKUP and HLOOKUP now 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 or range_lookup, they use only the index’s first element.
    • INDIRECT with R1C1 text (a1 set to FALSE) now reads lower-case r and c, leading zeros (R01C01), whole rows and columns (R2, C[1], R or C alone), and relative references that wrap past the last row or column, as Excel does.
    • SORT and SORTBY now 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, UNIQUE now treats text values as duplicates when they differ only by Unicode case folding, such as Straße and STRASSE, or by control characters such as CHAR(1), unless one text is the other followed by control characters, as Excel does.
    • UNIQUE with exactly_once TRUE 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, WRAPCOLS and WRAPROWS now return a vector shorter than the wrap count as a single column or row of its own length, without padding, as Excel does.
    • MMULT now 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, SWITCH now matches a blank cell with 0 and not with empty text or FALSE, as Excel does.
    • In Google Sheets mode, SWITCH now matches a blank cell expression with empty text and FALSE, as Google Sheets does.
  • Outside Google Sheets mode, SWITCH now 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, DGET and the other ten) now match Excel in the following respects:
    • Outside Google Sheets mode, a range of more than one cell as field now returns #VALUE!, rather than one result per cell.
    • Outside Google Sheets mode, an array given as criteria now 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 field is 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!.
  • 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 as LEN, CONCAT and TEXTJOIN, and in the header labels of database functions such as DSUM.
    • Outside Google Sheets mode, TEXT under the General format now writes five significant digits, not six, at an exponent of 99 or more in magnitude, as Excel does.
    • TEXT under the General format now writes 1E+15, not 10E+14, when rounding carries, as Excel does.
    • TEXT under a date format now shows the right day for a time just short of midnight, as in TEXT(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 gives 123456789012345.
    • FIXED now honours its no_commas argument.
    • FIXED and DOLLAR now 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, TEXT now 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, FIXED with an empty decimals argument now uses 0 decimals, as Google Sheets does.
    • In Google Sheets mode, DOLLAR now writes a negative number with a leading minus, not in parentheses, as Google Sheets does.
    • In Google Sheets mode, FIXED, DOLLAR and TEXT now write numbers near the largest representable number as Google Sheets does, not as ∞ or a #NUM! error.
    • In Google Sheets mode, TEXT now rounds a number to 15 significant digits as Google Sheets does in a few more cases, such as just below 1.
    • TEXT with 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, ENCODEURL now returns #VALUE! where its result would be longer than 32766 characters, as Excel does.
    • NUMBERVALUE now 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, DECIMAL now 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, DATEVALUE and other conversions of date text now read a date with spaces on either side of a - or / separator, such as 2026 -01-01 or 4 - Feb - 26, as Excel does.
    • Outside Google Sheets mode, VALUE, NUMBERVALUE, DATEVALUE, TIMEVALUE and other conversions of text to a number, date or time now return #VALUE! for text longer than 255 characters, spaces included, as Excel does.
    • TEXTSPLIT of empty text, or of a blank cell, now returns #VALUE!, as Excel does.
    • TEXTSPLIT now 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, SPLIT now converts a part with leading spaces to a number or date, as Google Sheets does.
  • Math functions:
    • PI() now returns pi to full double precision, as Excel does, which corrects trigonometric functions near multiples of pi/2, such as TAN(PI()/2-1E-12), and the last digits of DEGREES, RADIANS and SQRTPI.
    • ACOT now keeps its precision for large arguments, such as ACOT(9999999999999).
    • Outside Google Sheets mode, 10^k and POWER(10,k) now match Excel in the last digit for a negative integer k and for k of 126, and so does x^k for any other x and an integer k.
    • Results that scale by a power of ten, such as those of ROUND, ROUNDUP, ROUNDDOWN, DOLLARDE, DOLLARFR, PERCENTRANK, POWER and 10^k, are now the same on Node 22 as on later Node versions, rather than sometimes differing in the last digit.
    • SUBTOTAL and AGGREGATE with function 6 (PRODUCT) now return 0 over cells with no numbers, as Excel does, rather than #VALUE!.
    • In Google Sheets mode, ROUND now 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, PPMT and IPMT now return Excel’s results where (1+rate)^nper overflows; previously they returned wrong results or, over many periods, never finished.
    • PPMT and IPMT no 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, CUMPRINC with a fractional start or end period now includes the last period, as Google Sheets does.
  • Outside Google Sheets mode, TBILLEQ, TBILLPRICE and TBILLYIELD now 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) and TBILLPRICE (over any term) now return 0 rather than #NUM!.
    • TBILLEQ over a term of exactly 366 days, which spans a 29 February, is no longer about 0.3% low.
  • Statistical functions:
    • Outside Google Sheets mode, CORREL now 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.S and COVAR now treat values too small to represent in full as 0, as Excel does.
    • Outside Google Sheets mode, SLOPE, INTERCEPT, FORECAST and FORECAST.LINEAR now return #NUM! where the values are spread too widely to compute with, as Excel does.
    • In Google Sheets mode, CORREL now returns #NUM! where its result is not a number, as Google Sheets does, rather than #DIV/0!.
    • In Google Sheets mode, SLOPE, INTERCEPT, FORECAST and FORECAST.LINEAR now return #N/A where no pair of values are both numbers, as Google Sheets does, rather than #DIV/0!.
    • NORM.S.INV, NORM.INV, LOGNORM.INV and 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.DIST and LOGNORM.DIST now return tiny lower-tail probabilities, down to about 2.2E-308, instead of 0.
    • NORM.DIST and NORM.S.DIST now 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.DIST and LOGNORM.DIST now 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.DIST with cumulative TRUE, T.DIST.RT, T.DIST.2T, TDIST, T.INV, T.INV.2T, TINV, F.DIST, F.DIST.RT, FDIST, F.INV, F.INV.RT and FINV now return #NUM! for degrees of freedom above 1E10, as Excel does.
    • CHISQ.INV.RT and CHIINV now 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.RT and CHIINV now 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.DIST now returns #NUM! where the square of its x overflows, as Excel does.
    • Outside Google Sheets mode, CHISQ.INV now 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.RT and FDIST now return 0 where the result is below that double, as Excel does.
    • Cumulative BINOM.DIST, BINOMDIST and NEGBINOM.DIST are 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.DIST and NEGBINOMDIST is now accurate at many trials, where it previously returned FALSE, #NUM! or 0. As in Excel, it now returns #NUM! where number_s is above about 9E15 times number_f, and #DIV/0! where the sum of the two overflows.
    • The densities of BETA.DIST, GAMMA.DIST, GAMMADIST, WEIBULL.DIST and WEIBULL at the bounds of their support, such as x = 0, now return 0 or #NUM! where Excel does.
    • GAMMA.INV, GAMMAINV, BETA.INV and BETAINV are 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.INV and BETAINV now return #NUM! at extreme alpha and beta, as Excel does. Outside Google Sheets mode, GAMMA.INV and GAMMAINV now return #NUM! as Excel does in a range of huge shapes that depends on the probability.
    • PERCENTRANK, PERCENTRANK.INC and PERCENTRANK.EXC now check for a significance below 1 only after their data and x, as Excel does, so that with such a significance, a single value equal to x returns 1, and an x outside the data, or an error in the data, returns #N/A or that error rather than #NUM!.
    • STDEV, VAR and 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 of 1E30, have the same small nonzero spread as in Excel. So do DSTDEV, DVAR and their variants, and SUBTOTAL and AGGREGATE with functions 7, 8, 10 and 11 (the standard deviations and variances). KURT and Z.TEST now take their standard deviation the same way.
  • Date and time functions:
    • Outside Google Sheets mode, WEEKDAY and WEEKNUM now read a fractional return_type as Excel does: truncated, unless within about 2.4E-7 of an integer.
    • Outside Google Sheets mode, WEEKNUM now returns #VALUE! for a logical value in a referenced cell or for date text with a year past 9999, and checks the date before return_type, as Excel does.
    • In Google Sheets mode, WEEKDAY and WEEKNUM now truncate a fractional return_type, and WEEKNUM now reads a logical return_type as 0 or 1, as Google Sheets does.
    • WORKDAY and WORKDAY.INTL with a day count of 0 now return the start date without checking the weekend and holidays arguments, as Excel does, so WORKDAY(45000,0,"test") is 45000.
    • WORKDAY.INTL with an invalid weekend argument and a single error, non-date text or logical value as holidays, 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!.
  • 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.readCells and Model.evaluateExpression with cropTo: 'cells-with-non-blank-values' are now much faster on an expression that evaluates to a tall array, such as =B:B*1 over a column with data far down.
  • Recalculating a formula that reads many cells through OFFSET or INDIRECT, such as in a MAP or BYROW lambda, 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 SUM or AVERAGE over the same range, as in C2/SUM(C$2:C$5001) filled down (outside Google Sheets mode), or in array formulas repeating a larger expression around it, as in G2-SUM(…) in every row: the aggregate is now computed once rather than once per row.
  • BYROW, BYCOL and MAKEARRAY now call their LAMBDA with less overhead, which makes them several times faster when that LAMBDA is cheap.
  • The description of the iterativeCalculation load option now says that omitting it uses the settings stored in the workbook rather than disabling iteration.
  • 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+1 and gamma = ROW(), all scoped to Sheet1, =alpha in A6 is now 8; previously it was #VALUE!. Outside Google Sheets mode, a reference without $ at the end of such a chain, such as Sheet1!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 as Sheet1!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 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.rewriteFormulaAfterMove with a destination in another workbook, no longer drop the directory from references into another workbook, such as C:\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/A and 1. The same goes for a formula that reads its own cell inside SHEETS, ISREF, ROW and 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:D6 spilled blanks instead of 10 and 20.
  • TAKE and DROP over 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, LET or REDUCE, as in =IF(TRUE,Jan:Mar!A1,0), or that is bound to a LET variable, is now #VALUE! rather than #REF!, as in Excel, and an enclosing IFERROR no longer catches it.
  • A LAMBDA parameter or LET variable nested in a LAMBDA body now shadows an outer parameter of the same name, so =LAMBDA(x,LAMBDA(x,x*2)(3)+x)(10) is 16 rather than 30.
  • IFERROR and IFNA now 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_name warning; 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_failed warning; 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 useAutoFormatting and applyWidthHeightFormats attributes 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.insertCells and deleteCells, no longer discard the saved values of a loaded pivot table elsewhere on the sheet.
  • 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 SIGN now returns 0 for it.
    • POWER and ^ now return #NUM! for a negative base raised to an exponent of 2^32-1 (4294967295) or more in magnitude, and so do SERIESSUM, FV and PV in the matching cases.
    • MOD now matches Excel where the quotient is large or the dividend tiny: MOD(-1E-20,1) is now 0; previously it was 1.
  • In Google Sheets mode, the following now behave as in Google Sheets:
    • AND, OR and XOR now return the first error among their arguments, even one that follows text that would otherwise make them return #VALUE!.
    • LEN, TEXTJOIN and JOIN now take a number as a cell displays it: LEN(1E+20) is now 5 rather than 21.
    • 0^0, POW(0,0) and POWER(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)+1 or ISERROR(LAMBDA(x,x)), is now #N/A; previously these returned #CALC!, #VALUE! and FALSE.
  • When a selection has ranges but no activeCell, WorkbookView.getSelection and setSelection now take A1 as the active cell if a range contains it, else the first range’s top-left cell, as Excel does. Previously getSelection always reported A1, and setSelection always took the first range’s top-left cell.
  • Loading a workbook is faster: finding the dependencies of its formulas takes less time.
  • 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.deferDataTables off, or setting a workbook’s calcMode from "autoNoTable" to "auto", now immediately recomputes the data tables left stale while deferred, as Excel does; previously they stayed stale. Setting calcMode to "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.
  • Outside Google Sheets mode, a defined name holding a relative reference with a sheet prefix, such as Sheet1!B1 rather than Sheet1!$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.x or Sheet1.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 in Alpha :'TRUE'!A1, in a reference rewritten by renaming a sheet or moving cells to another sheet.
  • 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) or SUM(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]] or W[@[a`b]].
  • 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!A1 a 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.
  • LET variables and LAMBDA parameters now behave as in Excel in the following cases:
    • A LET variable or LAMBDA argument bound to another name, as y in LET(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 LET variables 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 the LET variables of a formula that calls it.
    • A formula that calls a lambda bound to a LET variable, or returned by a LET inside an IF, CHOOSE or other expression, or that passes a lambda in a defined name to another lambda, as readB1 in LAMBDA(fun,SUM(MAP(A1:A2,fun)))(readB1), now recalculates when a cell that the lambda body reads changes.
    • A LAMBDA parameter used as a range endpoint or an intersection operand, as in LAMBDA(from,to,SUM(from:to)), now takes its argument instead of evaluating to #NAME?. A range between two LET or LAMBDA variables, such as a:b in LET(a,A5,b,B5,SUM(a:b)), now spans the cells they name; previously Apiary read a:b as the columns A:B, which a column insert then rewrote.
    • A LET variable named r or c now takes its value instead of evaluating to blank. A LET variable or LAMBDA parameter named like a cell address, such as A1, 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 as C2, in such a formula.
  • Functions given a lambda as a value now treat it as Excel does:
    • ERROR.TYPE of a lambda, such as ERROR.TYPE(LAMBDA(x,x*10)), now returns #N/A rather than 3.
    • IF with a lambda as its condition, such as IF(LAMBDA(x,x),1,2), now returns #VALUE! instead of taking the first branch.
    • COUNTA now counts lambdas.
    • SWITCH over 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, FILTER with a lambda in its include argument now returns that lambda instead of #VALUE!, so LET(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/A in 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/A in 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.
  • Number formatting and parsing:
    • Number formats now round as Excel does: 0.00 shows 1.005 as 1.01, and General shows 4403928373.5 as 4403928374.
    • Number formats now write numbers of 1E+21 or more in full: #,### shows 2^70 as 1,180,591,620,717,410,000,000 rather than a garbled 1,.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, and TEXTJOIN and JOIN showing 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") is 1000000000000010.
  • In Google Sheets mode, the following functions now return #NAME?, as they do in Google Sheets, and functionSignatures no longer lists them in that mode:
    • the dynamic-array functions DROP, EXPAND, SORTBY, TAKE and TRIMRANGE
    • TEXTAFTER, TEXTBEFORE and TEXTSPLIT
    • AGGREGATE, AMORDEGRC, ECMA.CEILING, INFO, ISOMITTED and USDOLLAR
  • CHOOSE, IFERROR, IFNA and IF now handle an omitted argument and a blank cell as Excel does:
    • CHOOSE now returns a chosen omitted argument as blank, not 0, and passes it on, so ISOMITTED(CHOOSE(1,)) is TRUE.
    • IFERROR and IFNA now pass on an omitted value too, not only an omitted fallback.
    • Outside Google Sheets mode, where CHOOSE has an array index, or IFERROR or IFNA an array or a range of more than one cell as first argument, a blank cell from any of their arguments, or an omitted IFERROR or IFNA fallback, is now 0 in the result instead of blank.
    • CHOOSE with 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 IF with 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, CHAR and CODE now use Windows Excel’s character set for codes 128 to 159, so CHAR(128) is € rather than an invisible control character, and CODE("€") is 128 rather than 63.
    • Outside Google Sheets mode, LEN, MID, FIND, SEARCH and REPLACE now count a character above U+FFFF, such as an emoji, as one character, as Excel does.
    • UNICODE of text starting with a lone high surrogate (the first half of a character above U+FFFF, which MIDB can return) now returns #VALUE! rather than the surrogate’s code unit, as Excel does.
    • SEARCHB now finds a match that starts at the low surrogate (second half) of a character above U+FFFF, as FINDB and Excel do.
    • TEXTSPLIT now 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 in TEXTSPLIT(",,,",{",",""}).
  • Math and engineering functions:
    • Outside Google Sheets mode, SUMPRODUCT now 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, COMPLEX and CONVERT now return #VALUE! for a boolean in a numeric argument, and MROUND, QUOTIENT, COMPLEX and CONVERT now return #N/A for 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 of DELTA, GESTEP, ERF, ERFC, QUOTIENT, ISODD and ISEVEN; the number argument of DEC2BIN, DEC2OCT and DEC2HEX; and the places argument of the base-conversion functions, such as DEC2BIN and HEX2OCT.
    • In Google Sheets mode, ERF now reads an omitted upper limit as 0, as Google Sheets does, where it previously returned #N/A.
  • BESSELK and BESSELY now match Excel at these edges:
    • BESSELK and BESSELY now return #NUM! for an order above 10 million instead of taking seconds or returning a value.
    • BESSELK now 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!.
    • BESSELK now matches Excel at orders 2 and up for x near 700, where it was previously off by up to a factor of 10^24.
    • BESSELY at orders 2 and up now matches Excel more closely, where it could previously differ in the last digit or two.
  • GCD and LCM now match Excel and Google Sheets at these edges:
    • LCM now returns 0 rather than #NUM! when more than one of its arguments is zero, as in LCM(0,0).
    • Outside Google Sheets mode, GCD and LCM now return #NUM! for an argument or LCM result above 2^53 (9007199254740992), and #VALUE! when every argument is a blank cell, apart from any omitted ones.
    • In Google Sheets mode, GCD and LCM now match Google Sheets for arguments, or LCM results, above 2^53.
  • Complex-number functions:
    • IM functions such as IMSQRT and IMDIV now write each part of their result with 15 significant digits, as Excel does, so IMSQRT("5+12j") is 3+2j rather than 2.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!.
    • IMCOT of a tiny real number such as "1E-16" now returns a number rather than #NUM!; in Google Sheets mode it and IMCOT(0) now return #DIV/0!, as in Google Sheets.
    • IMCOT, IMSEC and IMPOWER now match Excel’s last digit in more cases, such as IMSEC(1.5) and IMPOWER("8-2j",9.2).
    • In Google Sheets mode, IMLN now writes its result with suffix i for a j argument, as Google Sheets does.
  • 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:
    • CORREL
    • COVARIANCE.P, COVARIANCE.S and COVAR
    • SLOPE and INTERCEPT
    • FORECAST and FORECAST.LINEAR
    • SUMXMY2, SUMX2MY2 and SUMX2PY2
    • PROB
  • Bond functions:
    • Under basis 0 (US 30/360), COUPDAYBS, COUPDAYSNC, PRICE and YIELD now match Excel when a coupon or settlement date falls on the last day of February.
    • Under basis 1 (actual/actual), COUPDAYS now 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), PRICE and YIELD now match Excel.
    • PRICE and YIELD now match Excel when settlement falls within the last coupon period.
    • YIELD now solves to full precision.
    • PRICE and YIELD now return #NUM! for very large yields where Excel does, rather than a number.
    • YIELD now returns a negative yield for a price above what the security would fetch at a zero yield, instead of about 0.
    • PRICEMAT and YIELDMAT now match Excel under basis 1.
    • DURATION and MDURATION now match Excel under every basis.
  • Date and time functions:
    • Outside Google Sheets mode, DAY, MONTH, YEAR, WEEKDAY, ISOWEEKNUM, DAYS, DAYS360, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL and WEEKNUM now return #NUM! for a date serial or holiday below 0 or past 9999-12-31 (serial 2958465), as Excel does.
    • DAYS now counts whole days, so DAYS(43832.233,1) is 43831 rather than 43831.233.
    • DAY, MONTH, YEAR and WEEKDAY, and outside Google Sheets mode DAYS360, now round the serial to the nearest second before taking its date, as Excel does, so DAY(DATE(2025,1,1)+0.9999999) is 2.
  • In Google Sheets mode, VSTACK and HSTACK now keep an error argument as one element of the stack, as Google Sheets does: VSTACK(NA(),1) spills #N/A and 1 rather than one #N/A.
  • Recalculating a data table whose results read many individual cells through dynamic references, such as OFFSET or INDIRECT, is substantially faster.
  • Recalculation is faster where many formulas read the same large range, such as SUMIFS formulas 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:
    • PRODUCT
    • CORREL, COVAR, COVARIANCE.P and COVARIANCE.S
    • CONCAT, CONCATENATE, and TEXTJOIN with every delimiter empty
    • MATCH with match type 1
  • New functions: HARMEAN, REGEXTEST, SHEET, SHEETS and TRIMMEAN, and Excel’s REGEXEXTRACT and REGEXREPLACE.
  • ADDRESS now supports a1 = FALSE to return an R1C1 reference, such as R5C3 or R[-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, and Model.cellsWithoutImplementation holds the cells keeping their cached values.
  • Workbook.editCells edits several cells in one call, with the same checks as editCell. 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 with editCell.
  • Model.recalculate accepts includeStaleDeferred: true. It recalculates the deferred cells as includeDeferred does, but recomputes only those data tables that were already stale or that the recalculation dirties, each once. includeDeferred still 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, null now means a dynamic-array formula, and an edit that sets a formula leaves ft null instead of inferring 'a'.
  • New Workbook.setArrayFormula(range, formula) creates a legacy Ctrl-Shift-Enter array formula over a fixed range, and Workbook.editCell now throws when asked to create one.
  • Model serialization is now version 11; payloads serialized by earlier versions still load.
  • 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 f in g = =f, now refers to the workbook-level f wherever g is used, as in Excel, and is #NAME? rather than #REF! if there is none. Before, Apiary could resolve it to a sheet-local f instead, 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, version and cloud_connection through serializeModel and deserializeModel, 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 of COUNTIF, SUMIF, the other *IF and *IFS functions and the database functions, and the cases of SWITCH now treat two numbers that agree to 15 significant digits as equal. In Google Sheets mode, so do lookups, SORT and SORTBY.
    • Outside Google Sheets mode, XLOOKUP, XMATCH, SORT and SORTBY now compare numbers exactly, and Workbook.sortCells does so in every mode.
    • In Google Sheets mode, comparisons, criteria, SWITCH, lookups, SORT and SORTBY now treat a nonzero number smaller in magnitude than 2^-1022 (about 2.2E-308) as 0.
  • 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 as PRODUCT, SUMPRODUCT or AVERAGE; 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 a LAMBDA body, as in Excel: =1+2^-52-1 is still 0, but =1+2^-52-1+0 is now 2.22044604925031E-16 and =1+2^-52-1=0 is now FALSE. MMULT no 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 becomes 0.0000999999999999999, not 0.0001, and in & and CONCATENATE, 9.99999999999999E+4 becomes 100000., 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, LAMBDA or 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, moveCells or reorderRows now exports its recalculated value, and one moved by moveCells whose 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, clearCells and copySheet no longer throw on a moved array formula, copySheet now 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.
  • 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.
  • 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 INDEX or OFFSET.
    • ValueSnapshot.applyTo now leaves deferred cells, including data tables in autoNoTable workbooks, deferred as model.recalculate() does; pass { includeDeferred: true } to also recalculate them.
  • Workbook.editCell with 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.goalSeek now 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:
    • ROUND now matches Excel on a number exactly halfway between two 15-digit numbers: ROUND(10^15+5,0) is now 1000000000000000, not 1000000000000010. FIXED and DOLLAR now round such a number the same way at a negative number of decimals.
    • In Google Sheets mode, FIXED and DOLLAR now return the text #NUM! at -309 decimals or fewer, as Google Sheets does.
    • FIXED and DOLLAR now 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, RIGHT and RIGHTB
    • the row and column numbers of INDEX
    • the places of ROUND
    • the decimals of FIXED and DOLLAR
    • the position and length of REPLACE and REPLACEB
    • the starting position of FIND, FINDB, SEARCH and SEARCHB
  • FIND and SEARCH starting positions:
    • SEARCH and SEARCHB now read a starting position further below an integer as its integer part, as FIND does, not rounded up: SEARCH("a","aaa",1.9999997) is now 1, not 2.
    • FIND, FINDB, SEARCH and SEARCHB now return #VALUE! for a starting position that reads as less than 1, such as 0.5, as Excel does. Previously SEARCH("a","abc",0.5) was 1, and FIND("","abc",0) was 0.
  • SEARCH, and MATCH, VLOOKUP, HLOOKUP, XLOOKUP and XMATCH in 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, so SEARCH("~?x","?y") found a match.
  • TEXTBEFORE and TEXTAFTER now read unusual arguments as Excel does:
    • They floor a fractional instance_num, which they previously rounded away from zero.
    • They return #VALUE! for an instance_num beyond the number of delimiters the text could hold, where they previously returned #N/A or if_not_found.
    • They truncate match_mode and match_end and return #VALUE! unless the result is 0 or 1, where previously any nonzero value counted as 1.
  • Other text functions:
    • TEXTSPLIT with both delimiters omitted now returns #VALUE! instead of the text unsplit.
    • SUBSTITUTE now returns the text unchanged when old_text is empty, instead of inserting new_text between every character, and now truncates a fractional instance_num instead of matching nothing.
    • ARABIC now accepts a leading minus sign, so ARABIC("-I") is -1 rather than #VALUE!.
    • Outside Google Sheets mode, ROMAN now truncates before its range check, so ROMAN(3999.9) is MMMCMXCIX and ROMAN(-0.5) is empty text, rather than #VALUE!.
    • In Google Sheets mode, ROMAN now returns #VALUE! for a number below 1, such as 0, rather than empty text.
    • In GRID sheet and Canvas modes, REGEXEXTRACT and REGEXREPLACE now follow Excel rather than Google Sheets: REGEXEXTRACT returns the whole match unless return_mode is 2, and a REGEXREPLACE replacement uses Excel’s $n and \ syntax. A formula in those modes that relied on Google Sheets’ behaviour now needs return_mode 2 to get the capture groups, and \\ or $$ for a literal \ or $ in a replacement.
    • In Google Sheets mode, NUMBERVALUE now returns #NAME?, as it does in Google Sheets, and functionSignatures no longer lists it in that mode.
  • Trigonometric and hyperbolic functions now match Excel at these edges:
    • SIN, COS and TAN return #NUM! for angles of magnitude 2^27 (134217728) or more outside Google Sheets mode, and COT, CSC and SEC do so in every mode.
    • COTH and CSCH keep their precision near 0, and ASINH and ACOSH return #NUM! for arguments of magnitude about 1.34E+154 or more.
  • Matrix functions and sums now match Excel and Google Sheets at these edges:
    • MDETERM is correct for 4x4 and larger matrices. Outside Google Sheets mode, MINVERSE inverts a matrix that is singular only up to rounding error; in Google Sheets mode, as in Google Sheets, MINVERSE returns #NUM! and MDETERM returns 0 for a nearly singular matrix, even an invertible one such as {1E-12,0;0,1}.
    • Outside Google Sheets mode, MINVERSE returns #NUM! where its computation overflows, and MINVERSE and MDETERM treat as 0 any intermediate result smaller in magnitude than about 2.2E-308.
    • Outside Google Sheets mode, SUM, AVERAGE, AVERAGEA, SUBTOTAL and AGGREGATE return 0 where the final addition of their sum cancels almost exactly, rather than the tiny remainder, and PERCENTOF over such a total returns #DIV/0!.
  • Other math functions:
    • BASE now ignores the fraction of its number, and returns #NUM! for a number of 2^53 (9007199254740992) or more, or a min_length above 255.
    • MOD now returns #NUM! for a quotient of 1125900000000 or more, as Excel and Google Sheets do, rather than a remainder.
    • FACTDOUBLE now returns #NUM! below -1 and truncates its argument before capping it at 300.
    • COMBIN and COMBINA now 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 as COMBIN(1E+15,5E+14); nor does COMBINA overflow for a large first argument.
    • As in Excel, COMBIN and COMBINA now return #NUM! for a negative fractional argument, such as COMBIN(-0.5,0), and for an n (in COMBINA, an n+k-1) of 2^31-1 (2147483647) or more, with n and k truncated first.
    • SERIESSUM now 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.
    • BITLSHIFT and BITRSHIFT now 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:
    • LINEST and TREND now match Excel with redundant or constant x columns, and in other cases where they previously returned #VALUE! or wrong values.
    • T.INV.2T and TINV now return a negative value for a probability between 1 and 2, and T.INV, T.INV.2T and TINV return #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.
    • SKEW and SKEW.P with fewer than 3 numbers or with equal numbers whose computed spread is zero, and COVARIANCE.P and COVAR with no pair of numbers, now return #DIV/0! as Excel does, where they previously returned #NUM! or 0.
    • SKEW and SKEW.P of numbers large enough to overflow, and SKEW, SKEW.P and DEVSQ of numbers small enough for an intermediate result to fall below about 2.2E-308, now return what Excel does, such as #DIV/0! for SKEW(0,0,1E-160) and 0 for DEVSQ(0,0,1E-160). SKEW of everyday numbers, such as SKEW(6,11,14,8), now also matches Excel more closely.
    • BINOM.DIST, BINOMDIST and BINOM.DIST.RANGE now 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, VARA and VARPA now 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:
    • TIME now floors fractional arguments, so TIME(0.9,0,0) is 0 and TIME(0,0,-0.9) is #NUM!, and returns #NUM! for an argument above 32767. In Google Sheets mode it truncates them toward zero instead, so TIME(0,0,-0.9) is 0, and accepts larger arguments.
    • WEEKNUM with an explicitly empty date now returns #N/A rather than #VALUE!. In Google Sheets mode it now reads the empty date as serial 0, as Google Sheets does.
  • TRANSPOSE of a single value now returns that value, not a one-element array, so SUM and TYPE over it agree with Excel.
  • ADDRESS now writes !$A$1 (''!$A$1 in Google Sheets mode) for an empty sheet name, and outside Google Sheets mode no longer quotes a workbook-qualified name such as [Book1]Sheet1.
  • ISERR now returns FALSE for an #N/A read from a cell.
  • Outside Google Sheets mode, IFERROR and IFNA now return a referenced fallback as its value rather than as a reference, as Excel does, so ISREF(IFERROR(1/0,A1)) is FALSE.
  • 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, MINIFS or MAXIFS matches a criterion against text, unless the text has very many distinct characters, such as long CJK text.
  • DSUM and the other database functions are faster with a text criterion that has no wildcard.
  • 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 Workbook class documentation no longer claims each workbook has its own dependency graph.
  • FISHERINV now returns ±1 for a large value, as Excel does, where it returned #NUM!.

  • ISODD and ISEVEN of a negative number are now correct: ISODD(-3) and ISEVEN(-2.1) were FALSE.

  • SUBTOTAL with 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.

  • NUMBERVALUE now:

    • 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
  • GAMMA now 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.INV and BETAINV now 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 as SUM(BYROW(A1:A100,LAMBDA(r,SUM(r)))); it failed with “Unhandled AST node type: param”.

  • The database functions (DSUM, DGET, DAVERAGE and 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 boolean TRUE and "1" the number 1
    • a condition under a criteria header that is blank or names no database column is ignored if it is TRUE or a nonzero number, and otherwise makes its criteria row match no rows
    • DGET skips 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 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 label d rather than ~d.

  • SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MINIFS and MAXIFS now read a ~ in a criterion as Excel does: where the criterion also has a * or ?, the ~ escapes the character after it, so SUMIF(A:A,"~d*",B:B) sums the rows beginning d, not those beginning ~d.

  • PRODUCT now 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.

  • SUMPRODUCT now 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 VLOOKUP and HLOOKUP over unsorted data that ends in blank rows or columns now find the same row as Excel.

  • XLOOKUP with 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, LEFTB and RIGHTB now count an emoji or other character outside the Basic Multilingual Plane as one character rather than two, except in Google Sheets mode.

  • PROPER now treats CJK characters as letters, so PROPER("ロaロb") is ロaロb rather than ロAロB.

  • PROPER now leaves ß as it is rather than expanding it to SS, except in Google Sheets mode, where Google Sheets expands it.

  • PROPER now turns İ into plain I at the start of a word and plain i elsewhere, capitalizes ΐ and ΰ as Ϊ and Ϋ, and treats a circled letter such as Ⓐ as a non-letter, so PROPER("Ⓐa") is ⓐA, except in Google Sheets mode.

  • CONVERT now raises a prefix on a squared or cubed unit to that power, so that km2 is 1e6 m2, and scales a prefixed temperature before applying the unit’s offset, so that 12 MK is 12e6 K. Google Sheets mode now ignores a prefix on kelvin, as Google Sheets does.

  • CONVERT now returns a temperature converted to its own unit unchanged, and converts small temperatures between Rankine and kelvin without losing them, as Excel does.

  • CONVERT now accepts hweight as a name for the long hundredweight, and passes an error in its first argument through instead of returning #VALUE!.

  • CELL now handles the format, parentheses and width info 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.

  • CELL with an unknown info type and no reference now returns #VALUE!, not #REF!.

  • CELL with 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.

  • DATE now 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
  • DATE with 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.

  • 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 IF or SWITCH branches, OFFSET or INDIRECT.
  • Outside Google Sheets mode, GETPIVOTDATA accepts Excel’s older text form, such as GETPIVOTDATA(A3,"Region[North] Product[Apples]").
  • With recalcErrors on, 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: with A1 =1-#REF! saved as #REF! and B1 =A1+1 saved as 5, B1 now stays 5, where it became #REF! before.
  • Workbook.reorderRows and reorderColumns now 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.insertCells and deleteCells over 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.

  • GETPIVOTDATA now 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, REDUCE and SCAN over 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 with ISREF, ISFORMULA or FORMULATEXT, 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, VALUE or NUMBERVALUE: VALUE("1234567890123459") is now 1234567890123450 rather than 1234567890123460. In Google Sheets mode, VALUE instead 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.
    • VALUE and NUMBERVALUE now return #VALUE! for hexadecimal, octal, binary or Infinity text, and VALUE for whitespace-only text: VALUE("0x10") was 16.
    • VALUE, SUMIF-style criteria and functions such as CUMIPMT now read text numbers with a currency symbol, thousands separators or a percent sign, as operators already did, and "1,23"+2 is 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; VALUE and DATEVALUE still 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, and DATEVALUE("29-Feb-1900") is now 60, or #VALUE! in Google Sheets mode.
  • In Excel mode, CHAR(255.5) now returns the same character as CHAR(255), rather than #VALUE!.

  • Outside Google Sheets mode, CLEAN now also removes the control characters U+0080 to U+009F, and UNICHAR now returns #N/A for a Unicode noncharacter or a surrogate code point, rather than the character.

  • In Google Sheets mode, CONCAT now takes exactly two arguments, as in Google Sheets: CONCAT("a") and CONCAT("a","b","c") are now #N/A rather than a and abc. CONCATENATE still takes one or more, and now more than 255 of them. The operator functions ADD, MINUS, MULTIPLY, DIVIDE, POW, EQ, NE, GT, GTE, LT, LTE, UMINUS and UNARY_PERCENT now give #N/A for extra arguments, which they used to ignore.

  • REPT now returns #VALUE! for a result longer than 32767 characters in Excel mode and 32000 in Google Sheets mode, where it allowed up to 524287.

  • ISERR is now FALSE for every #N/A, including one from a failed MATCH or VLOOKUP, as in Excel.

  • HOUR and MINUTE now round to the nearest second, so HOUR(0.999999) is 0 rather than 23.

  • WORKDAY and WORKDAY.INTL no longer count a holiday falling on the start date, which moved the result one workday too far.

  • SUMX2MY2, SUMX2PY2 and SUMXMY2 now return the first error in their arrays instead of skipping it, and MMULT now returns it instead of #VALUE!, unless text or a boolean comes before it.

  • GEOMEAN, PERCENTILE, PERCENTRANK, QUARTILE and RANK, and their variants, now return the first error value in their data instead of skipping it, as Excel does.

  • GEOMEAN now returns the error from a reference it cannot resolve, such as #REF! for a nonexistent sheet, instead of #NUM!.

  • SMALL and LARGE now 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.

  • QUARTILE and QUARTILE.INC now truncate a quart between 4 and 5 to 4, as Excel does.

  • CUMIPMT and CUMPRINC now 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/A for an empty one; in Google Sheets mode, CUMPRINC keeps its old sum over fractional periods.

  • IPMT and PPMT now accept a period up to, but not including, nper + 1, where they returned #NUM! for any period above nper.

  • Outside Google Sheets mode, FV and PV now 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.

  • PMT no longer loses precision, or returns #NUM!, for a tiny rate.

  • Outside Google Sheets mode, MIRR now 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, MIRR now returns that result instead of #NUM!.

  • Outside Google Sheets mode, XNPV now 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, XNPV now refuses any date before the first date given, whatever its cash flow, and accepts the serial 0 as the first date.

  • XIRR now 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/A for 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.

  • XIRR now finds a rate of return below -99.9%, such as for a loss of 72.5% over 60 days, where it returned #NUM!.

  • XIRR, and XNPV outside Google Sheets mode, now return #NUM!, not #VALUE!, for a non-finite rate or guess, which Model.write can store in a cell.

  • SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MINIFS and MAXIFS recalculate 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)<>0 or (A1:A50000="x")*(B1:B50000>3), recalculate faster, some several times faster. So do functions that build or transform an array, such as ROW and COLUMN over a tall range and TOCOL, and FILTER with several conditions in Google Sheets mode.
  • With recalcErrors on, workbooks with many broken references, such as =#REF!-B7, load faster.
  • Workbook.editCell(anchor, { dt }) creates or replaces a what-if data table on a live workbook, from the same dt metadata 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 an EditBlockedError if 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 ACCRINT and ACCRINTM.
  • WorkSheet.drawings is now typed as the JSF Drawing[] 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 from anchor.from and anchor.to instead of top_left and bottom_right, and its chart from content[0].chartId instead of chart_id.
  • 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.
  • AND and OR now 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 than FALSE.
  • serializeModel no longer throws after Model.removeWorkbook removes 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.
  • TOROW and TOCOL now return #VALUE! for an ignore value other than 0, 1, 2 or 3, as Excel and Google Sheets do, rather than returning the array unchanged.
  • SEQUENCE now truncates fractional rows and columns, so SEQUENCE(0.3) is #CALC! rather than {1} (#NUM! in Google Sheets mode); a negative size such as -0.5 stays #VALUE! (#NUM! in Google Sheets mode).
  • SEQUENCE and RANDARRAY now return #VALUE! (except in Google Sheets mode), and EXPAND #NUM!, for more rows than a sheet has, rather than building the array.
  • EXPAND now reads an omitted array as 0, so EXPAND(,2,2,"x") is {0,"x";"x","x"} rather than #VALUE!.
  • CHOOSECOLS and CHOOSEROWS now 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.
  • CEILING with a zero or empty significance, and FLOOR(0,0), now return 0 instead of #DIV/0!.
  • FLOOR, CEILING, ROUND, ROUNDDOWN, ROUNDUP, TRUNC, MROUND, INT and their variants no longer lose an increment to float noise, so FLOOR.MATH(7.1,0.1) is 7.1 rather than 7 and ROUNDDOWN(8.94-8,2) is 0.94 rather than 0.93.
  • FLOOR, CEILING, ROUND, ROUNDDOWN, ROUNDUP, TRUNC and the .MATH variants now round a result past 15 significant digits to 15, as Excel does, so FLOOR(2^53+2,1) is 9007199254740990 rather than 9007199254740994.
  • ROUND now rounds up only from a half at 15 significant digits, so ROUND(0.5-1E-12,0) is 0 rather than 1.
  • ROUND, ROUNDDOWN, ROUNDUP and TRUNC now truncate a fractional digit count toward zero, so ROUND(123456,-3.2) is 123000 rather than 120000.
  • ROUND, ROUNDDOWN, ROUNDUP and TRUNC now 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! from ROUNDUP) rather than the number, so ROUND(123,-400) is 0 and ROUNDUP(123,-400) is #NUM!, where both were 123.
  • In Google Sheets mode, an empty FLOOR or CEILING significance now reads as 1, and ROUND, ROUNDDOWN, ROUNDUP and TRUNC now return #NUM! at a digit count beyond float precision, as Google Sheets does.
  • MROUND now returns #NUM! rather than #VALUE! when the quotient is out of range, as in MROUND(1E+300,1E-10).
  • A number converted to text, as by & or a text parameter, now renders as Excel does: 1E+20&"" is 1E+20 rather than 100000000000000020000, and 1E-7&"" is 0.0000001 rather than 1e-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 from MMULT({9E+307},{10}), converts to text as #NUM! rather than Infinity.
  • 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 type but native), FORMULATEXT now 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 as RowTimesTen = RowName*10 with RowName = ROW()) now evaluates in the using cell’s context rather than to #VALUE!, as all three already did in a native workbook.
  • DOLLARDE and DOLLARFR now return #DIV/0!, not #NUM!, for a fraction between 0 and 1, as Excel does.
  • DB now returns #NUM! for a month greater than 12, as Excel does.
  • RATE now 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.
  • RATE now takes any nonzero type as 1, and finds a rate from a guess of 0 or near 0, or a guess at a double root, as Excel does.
  • A LET variable bound to an empty argument, as y in LET(x, 20, y, , x + y), or to an argument that passes one on, as IF(TRUE, ) does, is now an omitted argument, as in Excel: it evaluates as blank, and ISOMITTED returns TRUE for it. It evaluated as #NAME? before.
  • IF, IFERROR and IFNA now pass an omitted argument on, so ISOMITTED(IF(TRUE, )) is TRUE.
  • POISSON and POISSON.DIST now 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.DIST and NEGBINOM.DIST now return #NUM! for a first argument between -1 and 0, where they returned a probability.
  • 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 rows and columns, 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.
  • Node.js 22 or later is required. Node 18 and 20, both past end-of-life, are no longer supported.
  • PivotTable.jsf, PivotCache.jsf and the PivotTable constructor are no longer in the published type declarations. Read a pivot table through its getters instead, such as name, fields, measures, layoutForm and location, and create one with Workbook.pivots.add.
  • The deprecated Model.analyzeFormula is removed; call Model.analyzeAndFixFormula instead, 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 detail message or origin reference. To recalculate them, pass the load option recalcErrors: true to the method that loads the workbook, such as Model.fromXLSX, Model.fromJSF or Model.addWorkbook.
  • Formula cells saved without a value are now computed at load, with the formulas that depend on them; they read null before. The new load option recalcUncached (default true) controls this. A cell whose references cannot be resolved is also recalculated when a later addWorkbook supplies 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 recalcVolatiles and recalcUncached default to false, so a formula cell with no saved value reads null until the next recalculation. Pass true for 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: true to compute them all.
  • Pivot tables can now be created, edited and deleted with Workbook.pivots.add, update and delete. Inside update, the PivotTable.fields, PivotField.items and PivotTable.measures handles place, rename, filter and sort fields, rename, hide and collapse items, and add, change and remove measures.
  • New model events renamesheet and removesheet, 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 listenererror event. recalcEvent callbacks are unaffected.
  • Workbook.setRowHeight and Workbook.setColumnWidth now accept null to 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.TEST and ZTEST. As in Google Sheets, VALUETOTEXT and ARRAYTOTEXT are unavailable in Google Sheets mode.
  • New getter PivotTable.cache. Newly exported: PivotField, PivotItem, PivotMeasure, PivotAddOptions, the PivotFieldFilter types, PivotNameConflictError, PivotSourceInvalidError, and isDateFormat, the pivot cache’s test for a date number format.
  • COMPLEX now treats a blank suffix cell as i, as Excel does, instead of returning #NAME?. An invalid suffix now returns #VALUE!.

  • In Excel and GRID sheet modes, IFS and SWITCH now 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 FORMULATEXT showing 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 StdDevp or Varp, 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, STDEVP and STDEV.P now return #DIV/0! instead of 0 given no numbers, as do VARPA and STDEVPA given no values, and SUBTOTAL, AGGREGATE, DVARP and DSTDEVP when they compute a population variance or standard deviation.

    The variance and standard deviation functions (VAR.P, VAR.S, STDEV.P, STDEV.S and their older and A forms) 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 OFFSET or INDIRECT, such as =OFFSET(B1,0,E1) + A1 while the OFFSET resolves to A1, keeps depending on that cell after the OFFSET or INDIRECT retargets 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.toJSF and toXLSX as IFERROR(__xludf.DUMMYFUNCTION("<the formula>"), <its value>), which Excel opens, keeping the value it had in Apiary (a number to its first 15 significant digits). Previously toXLSX could 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%.

  • VLOOKUP and HLOOKUP now return #REF! instead of #N/A when 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/A whatever the index.

  • BESSELI now matches Excel; it was far off for a negative or large x. In Excel mode, BESSELJ now 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, BESSELJ and BESSELY now 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, BESSELK and BESSELY now return #NAME?, as in Google Sheets, and functionSignatures no 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.

  • toJSF no longer throws when deferred cells are stale, such as the data-table cells of an autoNoTable workbook; it exports their saved values, as Excel does. To export fresh values, call model.recalculate({ includeDeferred: true }) first.

  • In Google Sheets mode, QUERY on 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 SUM or AVERAGE, now returns #NUM!, as in Excel, instead of Infinity, -Infinity or #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.setDefinedName now 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 TRUE or FALSE, 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, TRANSLATE and PY with the _xlfn. prefix Excel requires, so they no longer open in Excel as #NAME?.

  • 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, MINIFS and MAXIFS are 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.
  • 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, and hasFrozenPanes. Frozen panes are read from XLSX files on load and written on export.
  • New public getter Table.style exposes the table’s visual style. Use it instead of Table.jsf.style; the jsf is internal and absent from the published type declarations.
  • Workbook.renameSheet now refuses a name Excel refuses, as addSheet and copySheet already 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 addSheet would 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.
  • YEARFRAC with 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 later addWorkbook into the same model.
  • INDEX(range, ...) no longer reports a false circular dependency through cells of range the formula never uses; previously a workbook with such formulas computed cleanly in Excel while Apiary evaluated part of the false cycle as 0.
  • Formula cells that depend on a pivot table, through GETPIVOTDATA or 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 which Model.readValue threw.
  • 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 later addWorkbook supplies 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 for Data in 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 as R or R1C1, a name leading with a digit such as 2020, or TRUE/FALSE) is now written with quotes, as Excel writes it.
  • Workbook.fromXLSX and Workbook.fromXLSXFile now include Excel-only functions such as ANCHORARRAY in workbooks. This matches the existing behaviour of Model.fromXLSX and Model.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 on Sheet2, 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 _LONGTEXT function over shorter chunks.
  • A formula that mixes an unresolvable reference with an OFFSET call 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 were undefined at 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, reorderColumns and Table.insertRow. Row and column inserts and deletes were never affected.
  • A cell value of NaN or ±Infinity now 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.
  • ^ and POWER now 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!.
  • POWER now 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.E5 used to become 1.R[4]C[4].
  • Workbook.addSheet with no name no longer picks a name (Sheet2) that case-insensitively matches an existing sheet’s name (sheet2). It now picks Sheet3 in 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.moveCells with references that name no sheet, such as moveCells('B1', 'D1'), now repoints dependent formulas as a sheet-qualified move does: a dependent =B1+1 becomes =D1+1 instead of staying on the emptied cell. Whole-column and whole-row references already did. A caller that also applied the returned RewriteFormula to 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: A01 reads as A1, and 01:1 as 1:1. Previously A01 was 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 named A01 is 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 in Workbook.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._LONGTEXT call 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 stdDevP or varP data field now exports with OOXML’s stdDevp and varp tokens; the JSF names written before are not valid OOXML.
  • A date styled m/d/yy, d-mmm-yy, d-mmm, mmm-yy or m/d/yy h:mm exported 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-only h:mm:ss.
  • The Workbook.moveCells documentation now says the RewriteFormula it returns is for the model’s other workbooks, since moveCells has 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.
  • @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
  • 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.

The stable 17.0 release. Its headline feature, pivot-table support, landed in the beta series and is stabilized here.

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 one instanceof catches them all: DataTableEditBlockedError, PivotEditBlockedError, PivotCellWriteBlockedError, PivotOutputOverlapsDataError, PivotDuplicateSourceHeadersError, TableEditBlockedError, SortCellsBlockedError, and ReorderMergeBlockedError.
  • Writes are persistent. Transient writes and reset are gone: write through Workbook.editCell and capture/revert with ValueSnapshot. Removed with them are Model.reset/writes/writeState, the named-values feature, range writes, writes to defined names, and the GRID.SCENARIO() function (now #NAME?).
  • Default style slot. StyleManager reserves style index 0 as the workbook default, so styles.length starts at 1.
  • Renames. moveRows/moveColumns → reorderRows/reorderColumns (the old names are gone); RecalcUreachedEvent → RecalcUnreachedEvent; the RewriteFormula callback 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 Cell value setter no longer accepts a Lambda. Callers must coerce a possible lambda to an error value themselves.
  • Internals are no longer typed. Members documented @internal — among them Cell.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/.

  • Workbook.toJSF() now preserves all input-provided external cells (and refreshError) under externals[].sheets[].cells, not only those the host’s dependency graph referenced, so round-tripped JSF contains strictly more external cells than before.
  • The Cell value setter (cell.v = …) now takes MaybeBoxed<CellValue> and no longer accepts a Lambda; 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 its PivotCellWrite, ComputeOptions, and ColumnSubtotal types) is no longer exported. It was materialize/repaint plumbing; the renderer-facing surface remains PivotTable.rowRoles/colRoles/pageArea and the already-materialized cells.
  • The SubtotalRow / AggregateSubtotalRow / SeparatorSubtotalRow / BlankSubtotalRow types are no longer exported. They were reachable only through the now-internal PivotLayout; renderers should read PivotTable.rowRoles / colRoles instead.
  • PivotCache is 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 / moveColumns names, deprecated as aliases when reorderRows / reorderColumns were introduced earlier in the 17.0 line, are removed; the move* names stay reserved for a possible future overwrite-move variant (the row/column counterpart of moveCells, which overwrites the destination instead of reflowing).
  • The edit-guard error classes are renamed to the package’s *Error convention (PivotEditBlockedError, PivotCellWriteBlockedError, PivotOutputOverlapsDataError, DataTableEditBlockedError) and now extend a common exported base class EditBlockedError, so a host can catch every guard refusal with one instanceof. Payloads are made consistent and richer: each carries sheetName, PivotEditBlockedError gains an operation discriminator, and PivotCellWriteBlockedError.cellRef is now a plain A1 string. Refusals that previously threw bare Error now throw typed subclasses: PivotDuplicateSourceHeadersError (duplicate pivot-source headers), TableEditBlockedError (the worksheet-table-protecting insertCells/deleteCells refusals), MergedCellWriteBlockedError (a write through editCell to a merged non-anchor cell), and SheetEdgeBlockedError (an insert that would push non-empty cells or a drawing off the sheet edge). Workbook.editCell and Workbook.sortCells throw the now-exported SheetNotFoundError for a missing sheet, and the new Workbook.fill throws PivotEditBlockedError (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 typed Readonly<OutputBounds>), PivotTable.dataFields (a readonly copy), and StyleManager.defaultStyle (a copy; setDefaultStyle stores one).
  • Members documented @internal no longer appear in the published TypeScript declarations (dist/index.d.ts / index.d.cts); code referencing engine plumbing such as PivotCache._permuteRecords or PivotManager._caches will now see a type error.
  • PivotColRole.kind is typed 'subtotal' (the only value the engine produces); drop any comparison to 'subheading'.
  • Formula parsing now handles Excel’s structured-reference @ this-row forms (Tbl[@col], Tbl[@[col]], Tbl[@x:y]). A bare Tbl[@] (this row, all columns) evaluates to #VALUE!, matching Excel.
  • New AGGREGATE function: all 19 aggregations and options 0–7, in both the reference form (function_num 1–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 as A1:A3*2 or 2*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!). Like SUBTOTAL, it skips range cells whose own formula nests a SUBTOTAL/AGGREGATE call, so subtotals-of-subtotals are not double-counted.
  • New MODE, MODE.SNGL, and MODE.MULT functions (previously #NAME?); MODE.MULT spills 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 and FillType modes. Text series follow Excel’s rules: quarter labels wrap modulo 4 (Q3 → Q4, Q1, Q2, not Q5, Q6), an ordinal like 1st Period recomputes 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 of EditBlockedError, so a host can catch them all with one instanceof: SortCellsBlockedError (carrying a reason) for the merge and spill cases, and TableEditBlockedError / PivotEditBlockedError / DataTableEditBlockedError (each carrying the structure’s name and an operation) 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.
  • PivotManager (the type of workbook.pivots) is now exported from the package entry, the last pivot-cluster manager type that was not already named there.
  • The Dimension type ('row' | 'col', the axis parameter of pivot range-adjustment methods) and the FILL_TYPES list (the valid Workbook.fill() modes) are now exported, and functions.CHOOSE / functions.SWITCH are restored to the public functions map.
  • The error classes SheetNotFoundError, NameNotFoundError, and WorkbookNotFoundError — long documented in @throws annotations — are now exported, so hosts can instanceof them instead of string-matching; NameNotFoundError gains a definedName field.
  • isPivotLookupError now narrows correctly as a type guard on a value typed unknown (its true branch narrows to PivotLookupError instead of never).
  • 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.
  • CHOOSE and SWITCH now short-circuit like IF, 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.
  • CHOOSE now truncates a fractional index_num toward zero before selecting, so e.g. CHOOSE(3.5, …) returns the third value instead of #VALUE!, matching Excel.
  • TEXTBEFORE / TEXTAFTER now accept an array or range delimiter (matching any of them) instead of returning #VALUE!, counting occurrences non-overlapping exactly as the same scalar delimiter would; and an out-of-range instance_num now 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.ofTransposed of 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.
  • SUMPRODUCT now returns #VALUE! for mismatched argument dimensions even when the cell counts match (e.g. a 1×2 row against a 2×1 column).
  • SUBTOTAL now skips range cells whose own formula nests a SUBTOTAL/AGGREGATE call, matching Excel, so subtotals-of-subtotals are not double-counted.
  • FORMULATEXT no 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 ModelError references (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/reorderColumns no 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 exported ReorderMergeBlockedError (an EditBlockedError, carrying the axis and 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/A are no longer treated as blank when resolving an area, correcting cropTo: 'cells-with-non-blank-values' cropping and spilled-range reads over such cells.
  • Model.meta no longer inflates graph and volatile counts (manifested only with more than one workbook).
  • Model.write now honors the forceRecalc option on a same-value write, as Model.writeMultiple already 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.
  • ValueSnapshot capture 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 ValueSnapshot no 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), applyTo could throw the write guard mid-restore. Restores now bypass those guards, so applyTo completes atomically.
  • Model.off() with no arguments, and removing one of several metrics/addsheet listeners, 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 (with A = Qty*Price) reproduces A’s own cell (SUM(Qty)*SUM(Price)) rather than SUM(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! and 0), and a text-only group renders 0 for 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; deserializeModel now 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 Y labels 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/@ref is 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-cellXf form, 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 = 26 rather 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/A across 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 hideValuesRow pivot 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/colRoles previously 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.moveCells now 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).
  • GETPIVOTDATA now 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 noSheetData marker through the JSF round-trip, matching Excel’s shape.
  • alternateUrls and pathMissing on external references are now preserved through Workbook.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/insertColumns no 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 bounded insertCells path, including whole-row/whole-column references.
  • Full-axis Workbook.deleteRows/deleteColumns no 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/deleteCells overlapping 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.
  • 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.
  • Upgrade @borgar/xlsx-convert to ^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/waspiary to ^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/jsf2xlsx to ^2.3.3, correcting the exported pivot styles table to Excel’s two-cellXf form, and fixing the round-trip of single-argument IFNA and IFERROR.
  • Upgrade @jsfkit/types to ^2.5.0 for the external-reference field declarations the externals round-trip fixes depend on.

The second 17.0 beta, with further pivot-table fixes following beta.0. Still subject to further breaking changes before the stable release.

  • Workbook.deleteCells(range, shift) and Workbook.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/column deleteRows/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 a RewriteFormula callback 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.pageArea reports 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.colRoles list 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.
  • 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; deserializeModel previously 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 compactData off.
  • 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 colItems with 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 Values button: it now reads Values ahead 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 inventing hideValuesRow="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 literal Row 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 absent showAll as 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: false without subtotalFunctions no longer gets a contradictory trailing default-subtotal item on export — a combination Excel offers to “repair” once the field also carries showAll="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 the r (“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/deleteColumns now match Excel in guarding a pivot table’s output range. A deletion that partially overlaps a pivot table is refused (throws PivotEditBlocked, like the analogous insertRows/insertColumns guard); a deletion that fully encompasses a pivot table removes it (and its cache if not also referenced by another pivot table).
  • Per-call function and operator timing now reads performance.now() only when a metricsCallback is 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 the index.{js,cjs,d.ts,d.cts} entry bundles. The public entry points (exports, main, module, types) and what you get from import '@grid-is/apiary' are unchanged; only consumers reaching past exports into dist/ are affected, since chunk filenames are now content-hashed.

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.

(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 by insertRows/insertColumns when the insert lands strictly inside a pivot’s output area, by moveRows/moveColumns when the move’s source or destination crosses a pivot’s boundary, and by moveCells/clearCells when 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 by Workbook.editCell (and the write wrappers 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.

  • Pivot table support (beta — some known kinks are still being ironed out):

    • Evaluation matches Excel: all OOXML aggregations and showDataAs modes, calculated fields, grouped fields, label/value/top-N filters, custom-list and autoSortScope sorting.
    • The GETPIVOTDATA spreadsheet function is now supported.
    • PivotManager.refreshCache refreshes 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?) and workbook.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.
  • Formula parsing accepts more of Excel’s grammar (via @grid-is/waspiary 1.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.

  • New StyleManager.defaultStyle getter and setDefaultStyle(style) method read and replace the workbook default style (slot 0). The default cannot carry extendsStyle.
  • New recalcAll?: boolean on AddWorkbookOptions forces (or, when false, suppresses) a full recalculation when the workbook is attached. The default honours the file’s fullCalcOnLoad signal (see 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.applyTo no 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 editCell to 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 returned null and recalculation recorded an “Invariant violation” model error, with no recovery even via a full recalc.

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.

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 / moveColumns taking 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 (editCell and the write wrappers) targeting a result cell, and clearCells covering 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 a ValueSnapshot beforehand 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 neutralizeFormulaOnSingleCellWrite flag is removed.

  • No range writes. Model.write / Workbook.write no longer accept a multi-cell range reference. To assign values to many cells, use Model.writeMultiple([[refA, val], [refB, val], …]); for a real spill, use Workbook.editCell with an array formula.

  • No writes inside merges. Model.write etc. now wrap Workbook.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.write accept string | A1Reference and 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.editCell on a name reference now requires an explicit f formula
    • To write through a chained defined-name reference, resolve first: const ref = Reference.from(name, { ctx: model }).resolveToNonName() and then model.write(ref, v)
    • Existing workbooks with literal-formula defined names still load and evaluate read-only

The following public APIs are removed:

  • Model.reset, Workbook.reset, WorkSheet.reset.
  • The 'reset' model event.
  • Model.writes, Workbook.writes, Model.writeState, Workbook.writeState, the ModelStateTree type, and the GRID.SCENARIO() spreadsheet function. No replacement: these all reported transient write state, which no longer exists. =GRID.SCENARIO() now resolves to #NAME?.
  • The neutralizeFormulaOnSingleCellWrite and reset properties of WriteOptions. The surviving properties are forceRecalc, skipRecalc, and skipVolatiles.
  • Cell._v and Cell.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.

  • 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-level ModelError listing 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.
  • Workbook.editCell is the canonical cell-mutation entry point: it keeps recalc bookkeeping in sync automatically. Model.write, Model.writeMultiple, and Workbook.write are now thin wrappers around it.
  • Cell.userZ is now marked @internal and excluded from the public .d.ts type. To read the effective number format use Cell.z, or Cell.style for the full StyleRelaxed (which includes the user-assigned format under numberFormat). To set a number format use Workbook.editCell({ z }) or cell.style = { ... }. No runtime change.
  • The RewriteFormula callback 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; moveCells in particular returned a rewriter that was a silent no-op across workbooks.
  • XLSX export font fix (via @grid-is/jsf2xlsx 2.2.0): the cell style cascade did not resolve fonts correctly, so cells could export with the wrong typeface.
  • 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.
  • Workbook.calcMode is 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 to Model.deferDataTables = true).
  • deleteRows and deleteColumns no 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.writeMultiple now correctly honours the skipVolatiles option. Previously the option was silently ignored, causing volatile functions such as RAND() and NOW() to be recalculated even when the caller asked to skip them.
  • Cell.edit and DefinedName.edit are now @deprecated aliases that forward to internal _edit methods. Consumers should use Workbook.editCell instead — editCell keeps the dependency graph and recalc bookkeeping in sync, which the raw edit paths do not. No runtime behaviour change.
  • Workbook.editCell({ v: '#N/A' }) (and Cell.edit) no longer coerces error-looking strings to error values — v is preserved as a string, matching the Cell constructor. Coercion still happens when the input includes t: 'e'.
  • Workbook.editCell no longer clears existing ModelErrors 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.
  • toJSF typing fix: External.names entries are typed as ExternalDefinedName (was JSFDefinedName, structurally compatible but wrong). Type-only fix, no behaviour change.
  • Model.fromJSF external-load loop is no longer O(externals × main-cells). _addExternals defers per-external recalculate() and optimize() to a single trailing pass; on a real 125-external, 18k-cell workbook, Model.fromJSF drops from ~16s to ~2s. Callers passing recalcVolatiles: false, recalcErrors: false (the round-trip harnesses) still see no recalc.
  • numfmt moved from peerDependencies to dependencies (range unchanged, ~3.2.2). Apiary uses numfmt internally and consumers never pass instances in or out of the API; the peer-dependency placement was incidental, and demoting it means npm install @grid-is/apiary works without consumers having to add a matching numfmt entry.
  • @types/node moved from dependencies to optional peerDependencies (range: >=20.19.23 <21 || >=22.19.0 <23 || >=24.10.0). The published dist/index.d.ts still references Node’s Buffer, so a TypeScript Node consumer needs @types/node resolvable — but the consumer’s own pin now wins. Pure-JS consumers see no missing-peer warning thanks to optional: true. The per-major minimum reflects the late-2025 DefinitelyTyped round that narrowed Buffer.buffer from ArrayBufferLike to ArrayBuffer; below that, our public Workbook.fromXLSX / Model.fromXlsx types are unsound.
  • @borgar/xlsx-convert bumped to 5.2.0, picking up an XLSX color/theme parsing fix that surfaced through Model.fromXlsx in an add-ins consumer (undefined is not an object (evaluating 'readColor(elm.children[0], …)')).
  • Removed the NamedStyle and Style re-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/types directly. Type-only change with no runtime impact.
  • Model now emits addsheet when a sheet is added (previously dropped silently), and no longer emits a spurious recalc for that operation.
  • Three independent deserialization-fidelity bugs in the binary cache, jointly affecting autoNoTable-mode roundtrips, are fixed:
    • Workbook._calcMode is restored from the payload (was silently reverting to 'auto').
    • Cells._dataTableCells (the data-table-anchor index) is repopulated, so iterDataTableCells() works on deserialized workbooks — unblocking the Model.deferDataTables = true setter and Model.attachWorkbook’s autoNoTable handling.
    • Model._deferDataTables and the deferral cell Sets (cellsToDefer, staleDeferredCells) are now serialized and restored, so a model in deferral mode no longer loses it through a roundtrip.
  • toJSF compacts full-row/full-column spill ranges in cell.F (A3:XFD3 → 3:3, B1:B1048576 → B:B), eliminating spurious roundtrip diffs when a code path holds an expanded F.
  • Error subclasses now set their own name field, so stack traces, console.log, inspect(), and Sentry display the actual class name (e.g. FormulaSyntaxError) instead of the inherited Error.
  • @jsfkit/types moved from devDependencies to dependencies so it is installed transitively when consumers npm install @grid-is/apiary. The published dist/index.d.{ts,cts} references @jsfkit/types directly because those types appear in Apiary’s public API; consumers’ type-checking previously failed unless they happened to depend on @jsfkit/types for other reasons.
  • typescript moved from dependencies to devDependencies. It is only used here as a CLI build tool (tsc --noEmit, tsup); nothing in lib/ imports it and the published types do not reference it. There is no public-API reason for consumers to receive Apiary’s pinned typescript version transitively. Originally added to dependencies during the apiary monorepo extraction without a deliberate placement choice.
  • CURRENT_SERIALIZATION_VERSION bumped 5 → 6 to carry the previously-omitted Model._deferDataTables boolean and the two deferral cell Sets. MINIMUM_ACCEPTABLE_SERIALIZATION_VERSION is unchanged — older payloads still load, with the new fields defaulted to off / empty.
  • JSF types referenced from the generated API reference (NamedStyle, Note, Style, Theme, ThemeColorScheme, ThemeCustomColor, ThemeFontCollection, ThreadedComment) now link to jsfkit.github.io instead of being mirrored as local Apiary type-alias pages.
  • EvaluateExpressionOptions.cropTo is now documented.
  • Cleaned up a confusing JSF-normalizer docstring.
  • Workbook.views is now a ViewManager (was WorkbookView[] | undefined). Workbook.getViews() and WorkSheet.getViews() are removed; WorkSheet.views is now @internal. Per-sheet view state is now read/written via workbook.views.get(index).getSheetView(sheetName).
  • Workbook.theme is now a ThemeManager (always present), not Theme | undefined.
  • WorkSheet.showGridLines property removed; gridline visibility now lives on WorkSheet.views[].showGridLines (per-view).
  • @jsfkit/types 1.x → 2.x: colours are now structured Color objects instead of hex strings, themes are restructured, and several types are reshaped.
  • Workbook.rowHeight(rowIndex, sheetName) now throws SheetNotFoundError when the sheet does not exist (previously returned the default 16 silently), and consults sheet.defaults.rowHeight before falling back to the Excel default. The new Workbook.columnWidth() follows the same precedence on its axis.
  • WorkSheet.rowHeights and WorkSheet.colWidths fields removed. Per-row height and per-column width are now read from WorkSheet.rows and WorkSheet.columns (the GridSize[] span arrays).

Removed @deprecated symbols (each deprecation note already pointed at the replacement shown here):

  • Cell.hasValue() / DefinedName.hasValue() and Cell.isBlank() / DefinedName.isBlank() removed. Use hasValueOrFormula().
  • Workbook.getTables() and Workbook.getTable(name) removed. Use workbook.tables.getAll() and workbook.tables.get(name).
  • WorkSheet.getComments() and WorkSheet.getNotes() removed. Use workbook.comments.get(sheetName) and workbook.notes.get(sheetName).
  • Model.fromData removed. Use Model.fromCsf.
  • Workbook.lazyImportPromise, Model.lazyImportPromise, loadLazy(), and the loadLazy re-export removed. All four were already no-ops; just delete the calls.
  • A batch of @deprecated @internal re-exports removed from the package entry point: formula-evaluation helpers (run, errorForCode, errorTable, …) and dependency graph helpers (cellToVertexId, wbNameToKey, …). Use cell.vertexId / definedName.vertexId instead of cellToVertexId(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.)

  • Views CRUD via Workbook.views, with two new public types ViewManager and WorkbookView.
  • Theme CRUD via Workbook.theme (new ThemeManager): read/write the theme as a whole, the 12-colour scheme, major/minor fonts, and custom colours.
  • Named-style CRUD via styleManager.named (new NamedStyles collection), with case-insensitive lookup and original casing preserved on NamedStyle.name.
  • Workbook.reorderSheet(name, newIndex) and Workbook.copySheet(name, newName?).
  • Table is now a public type export with new methods (appendRow, insertRow, rename, rowRef) and a new dataRowCount getter. TableManager is also exported as a type. (The runtime classes are not re-exported: instances come from workbook.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.
  • NotesManager and CommentsManager are now exported so they show up in the generated public docs.
  • Workbook.toXLSX(outputType?, options?) gains both arguments. outputType is 'nodebuffer' (default) or 'arraybuffer' for browser-compatible XLSX export. options (typed as ToXLSXOptions) carries a compressionLevel setting (DEFLATE 0–9), now defaulting to 6, down from 9 — measurably faster export with negligible size impact on typical XLSX payloads.
  • Workbook.columnWidth(columnIndex, sheetName) — analogous to Workbook.rowHeight(). Returns the column width in pixels.
  • Workbook.moveCells and Workbook.rewriteFormulas accept an optional recalcNow (default true); pass false to defer the trailing recalculate().
  • Workbook.editCell() added as the preferred name for writeCellData() (which stays as a @deprecated alias). Its parameter type simplifies to plain CellData, which now includes the optional ft field.
  • Identifier casing standardized on uppercase JSF/XLSX: Model.fromJSF, toJSF, csfToJSF, normalizeJSFForComparison, toXLSX, toXLSXFile, validateXLSXFilePath, plus type XLSXOutputType. The old title-case spellings remain as @deprecated aliases with identical signatures — no breaking change.
  • moveCells now 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 .M merge 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 a ModelError.
  • Iterative calculation now converges correctly when the iterative set contains a spill; previously the calc ran to maxIterations every time and dependents of unchanged spills could be flagged as false-positive circular references.
  • getEntities no longer crashes on workbooks containing sheet-scoped defined names.
  • SORTN silently ignores out-of-bounds sort-column indices instead of returning #VALUE!, matching verified Google Sheets behavior.
  • toJSF preserves values in merged-cell “ghosts” (non-anchor cells); mergeCells keeps 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/rows properties or on the stable.csf -> stable.jsf Table key rename.
  • replaceTableReferences now correctly rewrites bare-name references to tables (not just bracketed structured references).
  • Dynamic dependencies of LAMBDA closures passed to BYROW/BYCOL/MAP/REDUCE/SCAN with INDIRECT/OFFSET/ANCHORARRAY in the body are now tracked, so edits to referenced cells correctly trigger recalc.
  • Lambda parameter resolution during reference analysis no longer crashes on INDIRECT/OFFSET inside directly-called lambdas or nested lambda scopes.
  • toJSF no longer crashes on full-row or full-column spill ranges.
  • rewriteFormulas skips 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 crashed describeWorkbook in some cases.
  • Cells in JSF with error-looking string values ("#VALUE!", "#N/A", …) but no explicit t: 'e' marker are preserved as strings, not coerced to errors.
  • Faster label detection in describeWorkbook via cheaper subordinate-style checks (weighted geomean ~23.5% faster, worst-case workbooks up to 43% faster).
  • CURRENT_SERIALIZATION_VERSION bumped 3 → 5 over the v16 cycle. The 3 → 4 step was a marker for shape changes that had already landed without a version bump (Table csf → jsf key, cellsWithDynamicDependencies, notes/comments/views/tables), giving consumers that cached v3 payloads a proper signal to invalidate. The 4 → 5 step adds the namedStyles field and folds the legacy parallel rowHeights/colWidths maps into the WorkSheet.rows/WorkSheet.columns span arrays. MINIMUM_ACCEPTABLE_SERIALIZATION_VERSION stays at 1 — older cached payloads still load.
  • ModelError.toJSON API documentation cleaned up (the raw inferred-return-type property list is gone); the MDN link gets a readable link text.
  • @jsfkit/types ^1.4.1 → ^2.2.0 (see Breaking changes for the structural shifts).
  • @borgar/xlsx-convert and @grid-is/jsf2xlsx upgraded 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.

First release candidate for the 16.0 major line. Covers the deprecation cleanup, API additions, and fixes landed since v16.0.0-alpha.0.

All of the following remove symbols that were already @deprecated, with their deprecation notes pointing at the replacement shown here.

  • Cell.hasValue() / DefinedName.hasValue() and Cell.isBlank() / DefinedName.isBlank() removed. Use hasValueOrFormula().
  • Workbook.getTables() and Workbook.getTable(name) removed. Use workbook.tables.getAll() and workbook.tables.get(name).
  • WorkSheet.getComments() and WorkSheet.getNotes() removed. Use workbook.comments.get(sheetName) and workbook.notes.get(sheetName).
  • Model.fromData removed. Use Model.fromCsf.
  • Workbook.lazyImportPromise, Model.lazyImportPromise, loadLazy(), and the loadLazy re-export removed. All four were already no-ops; just delete the calls.
  • A batch of @deprecated @internal re-exports removed from the package entry point: formula-evaluation helpers (run, errorForCode, errorTable, …) and dependency graph helpers (cellToVertexId, wbNameToKey, …). Use cell.vertexId / definedName.vertexId instead of cellToVertexId(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.)

  • 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 (new NamedStyles collection), with case-insensitive lookup and original casing preserved on NamedStyle.name.
  • Workbook.editCell() added as the preferred name for writeCellData() (which stays as a @deprecated alias). Its parameter type simplifies to plain CellData, which now includes the optional ft field.
  • Identifier casing standardized on uppercase JSF/XLSX: Model.fromJSF, toJSF, csfToJSF, normalizeJSFForComparison, toXLSX, toXLSXFile, validateXLSXFilePath, plus types XLSXOutputMap and XLSXOutputType. The old title-case spellings remain as @deprecated aliases with identical signatures — no breaking change.
  • moveCells now 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 .M merge 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 a ModelError.
  • CURRENT_SERIALIZATION_VERSION bumped 3 → 4 as a marker for shape changes that had landed without a version bump (Table csf → jsf key, cellsWithDynamicDependencies, notes/comments/views/tables), giving consumers that cached v3 payloads a proper signal to invalidate.
  • CURRENT_SERIALIZATION_VERSION bumped 4 → 5 for the named-styles addition. MINIMUM_ACCEPTABLE_SERIALIZATION_VERSION stays at 1 — older cached payloads still load.
  • ModelError.toJSON API documentation cleaned up (the raw inferred-return-type property list is gone); the MDN link gets a readable link text.

First alpha of the 16.0 major line. Subject to further breaking changes before the stable release.

  • Workbook.views is now a ViewManager (was WorkbookView[] | undefined). Workbook.getViews() and WorkSheet.getViews() are removed; WorkSheet.views is now @internal. Per-sheet view state is now read/written via workbook.views.get(index).getSheetView(sheetName).
  • Workbook.theme is now a ThemeManager (always present), not Theme | undefined.
  • WorkSheet.showGridLines property removed; gridline visibility now lives on WorkSheet.views[].showGridLines (per-view).
  • @jsfkit/types 1.x → 2.0.0: colours are now structured Color objects instead of hex strings, themes are restructured, and GridSize gains an optional size.
  • Views CRUD via Workbook.views, with two new public types ViewManager and WorkbookView.
  • Theme CRUD via Workbook.theme (new ThemeManager): read/write the theme as a whole, the 12-colour scheme, major/minor fonts, and custom colours.
  • Workbook.reorderSheet(name, newIndex) and Workbook.copySheet(name, newName?).
  • Table class is now a public export with new methods (appendRow, insertRow, rename, rowRef) and a new dataRowCount getter. TableManager is also exported.
  • toXlsx accepts an optional outputType argument ('nodebuffer' (default) or 'arraybuffer') for browser-compatible XLSX export. New exports XlsxOutputType and XlsxOutputMap.
  • NotesManager and CommentsManager are now exported so they show up in the generated public docs.
  • Workbook.moveCells and Workbook.rewriteFormulas accept an optional recalcNow (default true); pass false to defer the trailing recalculate().
  • getEntities no longer crashes on workbooks containing sheet-scoped defined names.
  • SORTN silently ignores out-of-bounds sort-column indices instead of returning #VALUE!, matching verified Google Sheets behavior.
  • toJsf preserves values in merged-cell “ghosts” (non-anchor cells); mergeCells keeps 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/rows properties or on the stable.csf -> stable.jsf Table key rename.
  • replaceTableReferences now correctly rewrites bare-name references to tables (not just bracketed structured references).
  • Dynamic dependencies of LAMBDA closures passed to BYROW/BYCOL/MAP/REDUCE/SCAN with INDIRECT/OFFSET/ANCHORARRAY in the body are now tracked, so edits to referenced cells correctly trigger recalc.
  • Lambda parameter resolution during reference analysis no longer crashes on INDIRECT/OFFSET inside directly-called lambdas or nested lambda scopes.
  • toJsf no longer crashes on full-row or full-column spill ranges.
  • rewriteFormulas skips 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 crashed describeWorkbook in some cases.
  • Cells in JSF with error-looking string values ("#VALUE!", "#N/A", …) but no explicit t: 'e' marker are preserved as strings, not coerced to errors.
  • Faster label detection in describeWorkbook via cheaper subordinate-style checks (weighted geomean ~23.5% faster, worst-case workbooks up to 43% faster).
  • @jsfkit/types ^1.4.1 -> ^2.0.0 (see Breaking changes).
  • @grid-is/waspiary -> 1.11.0 for structured-reference bare-name rewrite.
  • Cell notes CRUD via NotesManager (workbook.notes): add, get, getByCell, update, delete
  • AND/OR/XOR/NOT evaluation with strings and other edge-case arguments, matching Excel and Google Sheets behavior
  • TEXTJOIN and JOIN now apply cell display formats (e.g. date formats) in Google mode

These are technically breaking, but very minor and in very recent features, so did not demand a major-version bump.

  • CommentsManager now throws SheetNotFoundError (instead of plain Error) for missing sheets, with a differently worded message.
  • WorkSheet.notes type changed to Note[] (no longer possibly undefined)
  • Update @grid-is/jsf2xlsx to ^1.3.2, updating default column width and fixing extraneous activeTab="0" attributes in workbookView elements.
  • Respect ‘autoNoTables’ from XLSX file, plus deferDataTables option for manual control
    • And more generally, mark any formula cells to be deferred in recalculation
  • Threaded comments CRUD via CommentsManager (workbook.comments)
  • MATCH returned #N/A instead of #REF! in Excel missing-sheet edge case
  • toJsf export 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 toJsf export
  • Much faster worst-case describeWorkbook (label-detection) performance
  • Update quickstart to use Model.fromXlsxFile instead of manual xlsx-convert
  • 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
  • Add changelog to public docs
  • Excel data tables
  • Function SORTN
  • getViews method in WorkSheet and Workbook
  • Google Sheets FLATTEN function
  • Merge/unmerge cells
  • Make HYPERLINK()-derived hyperlinks ephemeral
  • Preserve external workbooks in JSF roundtrip
  • Update comment and note cell refs on move, insert, and delete
  • Wrap fs/promises imports in try-catch to avoid bundler errors
  • Speed up describeWorkbook by pre-filtering parseDate calls
  • Pare down public API, marking stuff @deprecated and/or @internal
  • Use code blocks in Typedoc-generated docs
  • Support JSF cell notes and threaded comments
  • describeWorkbook crash on cross-workbook ref to formula cell
  • Support QUERY with COUNT(DISTINCT value)
  • Missed dynamic dependencies in lambda-calling functions at init time
  • Preserve cells with explicit empty styles through round-trip
  • Strip numberFormatFromFormula from exported styles in toJSF
  • toJsf
  • toXlsx
  • Add fromXlsx methods for direct XLSX loading
  • Native QUERY without pg-mem (calling it “feat” because it supports a lot that the old one did not)
  • Output style indexes on rows and columns
  • Real workbooks override external workbooks with same name
  • Fix invariant error on insert after row is deleted
  • Export ft property in toCSF for array formulas
  • Init recalc was skipped if !recalcVolatiles && !recalcErrors
  • Load XLSX files in Excel mode by default
  • Respect recalcVolatiles option during initial recalculation
  • Preserve text strings that look like errors in JSF loading
  • ERROR.TYPE did not support modern error codes
  • describeWorkbook throws on JSF workbook with externals
  • Upgrade xlsx-convert to 3.6.4 for bugfixes
  • Fix handlerRels to work with absolute paths
  • Fix external refs order
  • The r property on cells is optional
  • Remove Flags module and flags export
  • Public Model.attachWorkbook, and document addWorkbook as just a wrapper
  • Export StyleManager
  • Export CellData type
  • Add support for JSF workbook/worksheet view props
  • Specific Error subclass for invalid reference strings
  • Add missing detail messages on #CALC! errors
  • Google-mode early-date date function discrepancies
  • Matrix.expand makes fully-populated matrices, causing performance disasters
  • Nonresolving name in LOOKUP vector arg borks reference analysis
  • Propagate markDirty to all dependents of spill ranges
  • Blank spill anchor missing in Cells.getCells
  • Don’t propagate recalc from unchanged spill matrix
  • numberFormatFromFormula propagation
  • SUMIFS etc. wrong for Matrix with defaulted regions
  • Incorrect swap in Brent root-finding setup
  • QUERY with now() 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 Table serialization property name mismatch
  • Fix serialization invariant violation
  • Fix errors in toCSF export structures
  • Fix GSDV spill range inference: use offset not absolute row
  • Fix regression in evaluateExpression
  • Preserve CSF style array when loading workbooks
  • Add vertexId to Cell and DefinedName
  • Non-volatile OFFSET and INDIRECT and ANCHORARRAY with dynamic dependencies
  • CSF type should have filename, not name
  • Change NameReference.width and .height to be undefined, not throw
  • Update fx to 5.0
  • Upgrade Waspiary to 1.10.0 to permit R and C names
  • Add missing detail messages on #CALC! errors
  • Export CellData type
  • Specific Error subclass for invalid reference strings
  • Add support for JSF workbook/worksheet view props
  • Preserve CSF style array when loading workbooks
  • GSDV blocker in extended range should not block other columns
  • numberFormatFromFormula propagation
  • SUMIFS etc. wrong for Matrix with defaulted regions
  • Incorrect swap in Brent root-finding setup
  • QUERY with now() 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 Table serialization property name mismatch
  • Fix serialization invariant violation
  • Fix errors in toCSF export structures
  • Blank spill anchor missing in Cells.getCells
  • Don’t propagate recalc from unchanged spill matrix
  • Fix regression in evaluateExpression
  • CSF type should have filename, not name
  • Non-volatile OFFSET and INDIRECT and ANCHORARRAY with dynamic dependencies
  • Public Model.attachWorkbook, and document addWorkbook as just a wrapper
  • Export StyleManager
  • Google-mode early-date date function discrepancies
  • Matrix.expand makes fully-populated matrices, causing performance disasters
  • Non-resolving name in LOOKUP vector arg borks reference analysis
  • Propagate markDirty to all dependents of spill ranges
  • Fix default column-width units discrepancy
  • Add vertexId to Cell and DefinedName
  • Change NameReference.width and .height to be undefined, not throw
  • Update fx to 5.0
  • Read JSF directly
  • 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/HLOOKUP can return non-up-to-date value
  • Direct call to UPLUS fails with internal error
  • Recalculate affected formulas if clearGsdv clears cells
  • Remove reset value from DefinedName and CellInterface
  • Switch to @jsfkit/types for JSF type definitions
  • Install from, and publish to, npmjs.com registry
  • Make FormulaError and Reference fully immutable
  • Store origin cell in FormulaError
  • Add and improve error-value detail messages
  • Add detail messages to #SPILL! errors
  • Make Cell.isBlank() closer to what its name implies
  • Minor bugs in reference intersection and add a test
  • Rename and deprecate Cell methods
  • Deploy public API docs to GitHub Pages site
  • Remove internal stuff from docs
  • Mention case-insensitivity in name parameters
  • Recalculate error-valued cells at init time
  • Remove defined name
  • Wrong spill-operator result for blocked spill
  • QUERY coerces dates to string by local timezone
  • Support structured references in INDIRECT
  • IF result should not be marked dynamic
  • Make apiary bin work again
  • Allow disabling common-subexpression extraction
  • Fix 1904 epoch bug when converting JSF to CSF
  • Export FormulaSyntaxError and EvaluationError
  • Add Model.empty() convenience method
  • Export recalculation option symbols
  • ADDRESS with invalid coordinates yields wrong results or throws exception
  • Use source number format in TO_TEXT
  • Set number format in TO_* functions
  • XLOOKUP third argument wrongly forced up-to-date
  • ROW etc. fail to update when name ref updates
  • False-pos circdep in SUMIF/AVERAGEIF on target range extension
  • Workbook.iterFormulaCells had a bug and a redundant sibling
  • Update JSF support
  • Add a quick start guide
  • Add Starlight-based documentation system
  • Add tutorial for creating a spreadsheet from scratch
  • Document some methods on the Model class
  • Add support for loading and converting JSF format
  • CONVERT failed to handle reference value argument
  • Bump fx from 4.11.2 to 4.12.0 [bot]
  • Bump msgpackr from 1.11.4 to 1.11.5 [bot]
  • Fix defined names causing slow iterative calculation

Version revoked due to incorrect fix.

  • Detect parent labels
  • Label sequence confusion from sparse values at island top/left
  • Bump component-emitter from 1.3.0 to 2.0.0
  • Upgrade pg-mem to 2.6.15
  • Leftmost-column data mistaken for a label sequence
  • Skip over gaps in mixed-type label subsequences
  • Improve island-based label detection