Working with Array Formulas and R1C1 Formulas in Excel Using Python

Most formulas address a single cell at a time: =A1+A2 refers to two coordinates and lives in a third. An array formula is different — it treats a whole block of cells as input and writes its result into a whole block of output. Calculations like a linear regression with LINEST or a cross-range conditional sum take many steps as plain formulas but one step as an array formula.

R1C1 notation is a second key. "R1C1" means "row 1, column 1", and in a formula the ranges are described not as absolute coordinates but as offsets relative to the current cell — R[-3]C[-2] means "three rows up, two columns left". When a formula is copied across a region, this relative description is what makes every copy land correctly, which is exactly what batch formula generation needs. This article demonstrates both formula types, plus reading them back, using Spire.XLS for Python.

Setting Up the Environment

Install Spire.XLS:

pip install Spire.XLS

Then import the modules:

from spire.xls import *
from spire.xls.common import *

Writing an Array Formula with FormulaArray

An array formula is assigned to a range, not a single cell. The example below fills a 3×3 block of test data, then writes the LINEST regression function into a 2×3 result region:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Fill test data
data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# Write the array formula into A5:C6
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# Calculate and save
workbook.CalculateAllValue()
workbook.SaveToFile("UseArrayFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The result:

Two things to note here:

• The range assigned through Range.FormulaArray should match the shape of the matrix the formula returns. With two independent variables in its four-argument form, LINEST returns a 5-row × 3-column result; the demo narrows this to the 2×3 block A5:C6 for brevity, so Excel displays the first rows of the full matrix.
• Call workbook.CalculateAllValue() after writing. Without it, the results appear only once Excel opens the file and recalculates.

The pattern applies to every function that returns a matrix: select the output block as a whole, write one statement, calculate once.

Writing Array Formulas with R1C1 Offsets

FormulaArray uses the usual A1 coordinates; the matching property FormulaArrayR1C1 uses relative R1C1 references. The next example computes, in C4, the sum of the 3×3 block above it:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# Label, right-aligned, next to the sum
sheet.Range["B4"].Text = "Sum:"
sheet.Range["B4"].Style.HorizontalAlignment = HorizontalAlignType.Right

# R1C1 array formula: from C4, three rows up and two columns left, to the current column
sheet.Range["C4"].FormulaArrayR1C1 = "=SUM(R[-3]C[-2]:R[-1]C)"

workbook.CalculateAllValue()
workbook.SaveToFile("UseArrayR1C1Formula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The result:

R[-3]C[-2] reads as "three rows up, two columns left" from the cell holding the formula — that is A1. R[-1]C reads "one row up, same column" — C3. Negative offsets go up or left; an unsigned form like R1C1 refers to the fixed first row and column. The value of this notation is that the formula is independent of its absolute position: put the same statement in a loop and apply it to every cell of a column, and the relationship between each cell and its data stays intact — exactly the property wanted when generating formulas in bulk.

The same offset idea applies to ordinary formulas: Range.FormulaR1C1 accepts R1C1-style references and works just like Formula, only in a different coordinate language.

Generating Formulas in a Loop

With R1C1 references a loop needs only one formula string. The example writes a per-row sum into column D, to the right of each data row, and records the formula text in column E so the actual references can be checked:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# One relative-reference formula per row
for r in range(1, 4):
    sheet.Range[r, 4].FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    # Record the formula text next to it, as plain text, for verification
    sheet.Range[r, 5].NumberFormat = "@"
    sheet.Range[r, 5].Text = "=SUM(RC[-3]:RC[-1])"

workbook.CalculateAllValue()
workbook.SaveToFile("BatchR1C1.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The result:

Every cell in column D holds the identical text =SUM(RC[-3]:RC[-1]) — for column D, "in this row, from three cells left to one cell left", which resolves to columns A through C. The same string dropped into three rows calculates against each row's own data (12, 15, and 18); with A1 notation, the formula text would have to change from row to row. Column E, formatted as text with NumberFormat = "@", records the formula string itself so the actual references can be checked after opening the file.

Summary Rows with SUBTOTAL

Besides array formulas, SUBTOTAL is the standard function for summarizing a table region. The number from 1 to 11 selects the operation: 1 for average, 2 for count, 4 for maximum, 5 for minimum, 9 for sum. Its distinguishing feature is that it automatically ignores rows hidden by a filter, which makes it suitable for totals placed at the end of a data region:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

data = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].NumberValue = value

# 1 = average, 2 = count, 5 = min
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

workbook.CalculateAllValue()
workbook.SaveToFile("SubTotalFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The result:

Reading Formulas and Their Values

Generated formulas often need verification inside the same script. Range.Formula returns the formula string and FormulaNumberValue returns the calculated value:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("UseArrayR1C1Formula.xlsx")
sheet = workbook.Worksheets[0]

# The formula comes back in A1 style
cell = sheet.Range["C4"]
print("Formula:", cell.Formula)
if cell.HasNumber:
    print("Value:", cell.FormulaNumberValue)

workbook.Dispose()

One behavior worth knowing: the file stores the R1C1 array formula, but the Formula property returns the equivalent in A1 style. The representation you write and the representation you read back can differ, so any automated comparison should account for the conversion rather than compare strings directly.

Practical Tips

• Match the range to the result: the rows and columns of the FormulaArray target should equal those of the returned matrix, or results get truncated or shifted.
• Order the steps: write data first, formulas second, CalculateAllValue() last — calculation then always sees values in the referenced cells.
• Prefer R1C1 for bulk generation: one FormulaR1C1 / FormulaArrayR1C1 string in a loop is less error-prone than assembling A1 coordinate strings cell by cell.
• Watch the representation on read-back: formulas written in R1C1 style are returned in A1 style; avoid raw string comparison in verification code.
• Release resources: call Dispose() on each workbook when done.

Conclusion

This article covered writing array and R1C1 formulas in Excel from Python: FormulaArray places matrix functions such as LINEST into an output block in one statement, FormulaArrayR1C1 expresses cross-range calculations as relative offsets, FormulaR1C1 in a loop generates row-wise formulas from a single string, SUBTOTAL handles summary rows, and Formula together with FormulaNumberValue verifies the results. Arranged in the order "data, formulas, calculation, save", these formulas fit reliably into automatically generated reports.