# Create Array and R1C1 Formulas in Excel with 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](https://www.e-iceblue.com/Introduce/xls-for-python.html).

## Setting Up the Environment

Install Spire.XLS:

```bash
pip install Spire.XLS
```

Then import the modules:

```python
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:

```python
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:

![UseArrayFormulas](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/qutexfakn3j12d2zp7wj.png align="center")

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:

```python
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:

![UseArrayR1C1Formula](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/7wi5cec2zfq9rfrh91ck.png align="center")

`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:

```python
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:

![BatchR1C1](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/xh4zinoki4902m8wcxf9.png align="center")

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:

```python
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:

![SubTotalFormula](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/6v48zmvju8z4zq4he6l2.png align="center")

## 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:

```python
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.