XLSX

Formulas

Normal, shared, and array formulas

Normal Formulas

Set a formula on a cell with an optional cached result:

{
  "worksheets": [
    {
      "name": "Sheet1",
      "children": [
        {
          "cells": [
            { "value": "Apples" },
            { "value": 10 },
            { "value": 2.5 },
            { "formula": { "formula": "B2*C2" }, "value": 25 }
          ]
        },
        {
          "cells": [
            { "value": "Bananas" },
            { "value": 5 },
            { "value": 3.0 },
            { "formula": { "formula": "B3*C3" }, "value": 15 }
          ]
        },
        {
          "cells": [
            { "value": "Total" },
            { "formula": { "formula": "SUM(B2:B3)" } },
            { "formula": { "formula": "SUM(C2:C3)" } },
            { "formula": { "formula": "SUM(D2:D3)" } }
          ]
        }
      ]
    }
  ]
}

import { Workbook, Packer } from "@office-open/xlsx";

const wb = new Workbook({
  worksheets: [
    {
      name: "Sheet1",
      children: [
        {
          cells: [
            { value: "Apples" },
            { value: 10 },
            { value: 2.5 },
            { formula: { formula: "B2*C2" }, value: 25 },
          ],
        },
        {
          cells: [
            { value: "Bananas" },
            { value: 5 },
            { value: 3.0 },
            { formula: { formula: "B3*C3" }, value: 15 },
          ],
        },
        {
          cells: [
            { value: "Total" },
            { formula: { formula: "SUM(B2:B3)" } },
            { formula: { formula: "SUM(C2:C3)" } },
            { formula: { formula: "SUM(D2:D3)" } },
          ],
        },
      ],
    },
  ],
});

Omit value and Excel will recalculate on open.

Shared Formulas

When multiple cells use the same formula structure, shared formulas reduce repetition:

{
  "worksheets": [
    {
      "name": "Sheet1",
      "children": [
        { "cells": [{ "value": "X" }, { "value": "Y" }, { "value": "X+Y" }] },
        {
          "rowNumber": 2,
          "cells": [
            { "value": 1 },
            { "value": 2 },
            {
              "reference": "C2",
              "formula": {
                "formula": "A2+B2",
                "type": "shared",
                "reference": "C2:C4",
                "sharedIndex": 0
              },
              "value": 3
            }
          ]
        },
        {
          "rowNumber": 3,
          "cells": [
            { "value": 4 },
            { "value": 5 },
            {
              "reference": "C3",
              "formula": { "formula": "", "type": "shared", "sharedIndex": 0 },
              "value": 9
            }
          ]
        },
        {
          "rowNumber": 4,
          "cells": [
            { "value": 6 },
            { "value": 7 },
            {
              "reference": "C4",
              "formula": { "formula": "", "type": "shared", "sharedIndex": 0 },
              "value": 13
            }
          ]
        }
      ]
    }
  ]
}

{
  rowNumber: 2,
  cells: [
    { value: 1 },
    { value: 2 },
    {
      reference: "C2",
      formula: { formula: "A2+B2", type: "shared", reference: "C2:C4", sharedIndex: 0 },
      value: 3,
    },
  ],
},
// Follower cells: no formula text or reference needed, just the same sharedIndex
{
  rowNumber: 3,
  cells: [
    { value: 4 },
    { value: 5 },
    { reference: "C3", formula: { formula: "", type: "shared", sharedIndex: 0 }, value: 9 },
  ],
},
{
  rowNumber: 4,
  cells: [
    { value: 6 },
    { value: 7 },
    { reference: "C4", formula: { formula: "", type: "shared", sharedIndex: 0 }, value: 13 },
  ],
},

Key points for shared formulas:

Definition cell: contains formula text, reference (full range), and sharedIndex
Follower cells: formula is an empty string, same sharedIndex, no reference

Array Formulas

Array formulas return an array of values spanning multiple cells:

{
  "worksheets": [
    {
      "name": "Sheet1",
      "children": [
        { "cells": [{ "value": "X" }, { "value": "Y" }] },
        { "cells": [{ "value": 1 }, { "value": 10 }] },
        { "cells": [{ "value": 2 }, { "value": 20 }] },
        {
          "rowNumber": 5,
          "cells": [
            {
              "reference": "A5",
              "formula": { "formula": "A2:A4*B2:B4", "type": "array", "reference": "A5:B6" },
              "value": 10
            },
            {
              "reference": "B5",
              "formula": { "formula": "", "type": "array", "reference": "A5:B6" },
              "value": 100
            }
          ]
        },
        {
          "rowNumber": 6,
          "cells": [
            {
              "reference": "A6",
              "formula": { "formula": "", "type": "array", "reference": "A5:B6" },
              "value": 20
            },
            {
              "reference": "B6",
              "formula": { "formula": "", "type": "array", "reference": "A5:B6" },
              "value": 200
            }
          ]
        }
      ]
    }
  ]
}

// Definition cell: formula text + reference
{
  rowNumber: 5,
  cells: [
    { reference: "A5", formula: { formula: "A2:A4*B2:B4", type: "array", reference: "A5:B6" }, value: 10 },
    { reference: "B5", formula: { formula: "", type: "array", reference: "A5:B6" }, value: 100 },
  ],
},
// Follower cells: each cell still needs reference
{
  rowNumber: 6,
  cells: [
    { reference: "A6", formula: { formula: "", type: "array", reference: "A5:B6" }, value: 20 },
    { reference: "B6", formula: { formula: "", type: "array", reference: "A5:B6" }, value: 200 },
  ],
},

Key points for array formulas:

Definition cell: contains formula text and reference (covering all result cells)
Follower cells: formula is an empty string, but still needs the same reference
Every cell in the range must exist, or Excel will report a repair error

FormulaOptions Reference

Option	Type	Description
`formula`	`string`	Formula expression, e.g. `"SUM(A1:A10)"`
`type`	`string`	Formula type (default: `"normal"`, see table below)
`reference`	`string`	Formula range, e.g. `"C1:C10"`
`sharedIndex`	`number`	Shared formula group index (required for shared)

Formula types:

Type	Description
`normal`	Normal formula
`shared`	Shared formula
`array`	Array formula

Edit this pageorReport an issue

Cells & Data

Cell data types, row structure, and basic data entry

Styles

Fonts, fills, borders, alignment, and number formats