SEQUENCE and RANDARRAY Equivalents in Python

Excel's dynamic-array functions made a few small jobs one-formula affairs: =SEQUENCE(12) for the numbers 1 to 12, =SEQUENCE(4,3,10,5) for a grid counting in fives, =SEQUENCE(12,1,DATE(2026,1,1),...) for a column of dates, =RANDARRAY(5,2,1,100,TRUE) for random whole numbers and =SORTBY(list,RANDARRAY(ROWS(list))) for a shuffled list. In Python these jobs are usually part of something larger — building a calendar table, creating test data, drawing a random sample for an audit — and NumPy and pandas do each of them in a line. The one real difference is randomness: Excel's random functions recalculate every time anything changes, so their results can never be reproduced, while NumPy's can be seeded. This page translates each pattern, checks the deterministic ones against a spreadsheet's results, and writes the outputs to Excel as values. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install numpy pandas openpyxl

The deterministic examples are compared with the same formulas recalculated by LibreOffice (soffice), which supports SEQUENCE; Excel 365 gives the same results. The random examples cannot be compared value for value — that is the point of them — so their properties are checked instead.

Step 1 — SEQUENCE: counting in rows and columns

SEQUENCE(rows, [columns], [start], [step]) fills a grid row by row. NumPy's arange produces the numbers and reshape lays them out:

import numpy as np

def sequence(rows, cols=1, start=1, step=1):
    return start + step * np.arange(rows * cols).reshape(rows, cols)

print(sequence(5).ravel())
print(sequence(4, 3, 10, 5))

SEQUENCE(5) is the column 1 to 5; SEQUENCE(4,3,10,5) is a four-by-three grid starting at 10 and counting in fives, filled across each row before moving down — exactly NumPy's default (row-major) reshape order. Building the values as start + step * index rather than arange(start, stop, step) avoids the floating-point surprises arange can produce with fractional steps, where the last value may or may not be included. For row numbers in a DataFrame, range(1, len(df) + 1) or np.arange(1, len(df) + 1) is the everyday version.

Step 2 — Check against the spreadsheet

Write the same formulas into a workbook, recalculate, and compare:

import subprocess
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.formula import ArrayFormula

wb = Workbook()
ws = wb.active
ws["A1"] = ArrayFormula("A1:C4", "=_xlfn.SEQUENCE(4,3,10,5)")
ws["E1"] = ArrayFormula("E1:E6", "=_xlfn.SEQUENCE(6,1,0.1,0.1)")
ws["G1"] = "=SEQUENCE(4,3,10,5)"                        # no prefix: what goes wrong
wb.save("seq.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "seq.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/seq.xlsx", data_only=True).active
grid = np.array([[calc.cell(r, c).value for c in range(1, 4)] for r in range(1, 5)])
tenths = np.array([calc.cell(r, 5).value for r in range(1, 7)])
print("grid matches:", np.array_equal(grid, sequence(4, 3, 10, 5)))
print("tenths match:", np.allclose(tenths, sequence(6, 1, 0.1, 0.1).ravel()), tenths)
print("without the _xlfn prefix:", calc["G1"].value)

Both results match NumPy's — the decimals within floating-point precision, which is why allclose rather than exact equality is the right comparison for fractional steps. Two details made the formulas work at all. In the file format, functions added to Excel after 2010 are stored with an _xlfn. prefix; written without it, as in G1, the spreadsheet does not recognise the name and shows #NAME?. And because openpyxl does not write the metadata that marks a formula as a spilling dynamic array, the formula is written as an ArrayFormula over the exact range it should fill — A1:C4 for a four-by-three result. Without that, a plain formula fills only its first cell. Writing dynamic-array formulas properly is covered in Write Dynamic Array Formulas with Python.

Step 3 — Date sequences

A column of dates — every month start, every day, every working day — is what SEQUENCE is most often used for in reports, often combined with EDATE or WORKDAY. pandas generates them directly with date_range:

import pandas as pd

month_starts = pd.date_range("2026-01-01", periods=12, freq="MS")
month_ends = pd.date_range("2026-01-31", periods=12, freq="ME")
workdays = pd.bdate_range("2026-09-21", periods=10)
weekly_mondays = pd.date_range("2026-09-07", periods=6, freq="W-MON")
print(month_starts[:3].date, month_ends[:3].date)
print("10 business days from 21 Sep:", workdays.strftime("%a %d").tolist())
print("Mondays:", weekly_mondays.strftime("%d %b").tolist())

freq="MS" gives month starts and "ME" month ends — the latter replacing the EOMONTH(SEQUENCE(...)) construction, with February's end and leap years handled automatically. bdate_range skips weekends like WORKDAY; pass freq="C" with a holidays= list to skip public holidays as well. "W-MON" anchors weekly dates to Mondays. These series are the backbone of a date table — see Build a Date Table for Excel Reports with pandas.

Step 4 — RANDARRAY and RANDBETWEEN, reproducibly

RANDARRAY(rows, cols, min, max, whole_number) and RANDBETWEEN(low, high) produce random numbers. NumPy's Generator does the same, with one decisive advantage — a seed:

rng = np.random.default_rng(seed=2026)

decimals = rng.uniform(0, 1, size=(3, 2))              # =RANDARRAY(3,2)
whole = rng.integers(1, 100, size=(5, 2), endpoint=True) # =RANDARRAY(5,2,1,100,TRUE)
one = rng.integers(1, 6, endpoint=True)                 # =RANDBETWEEN(1,6)
print(decimals.round(3))
print(whole)
again = np.random.default_rng(seed=2026).uniform(0, 1, size=(3, 2))
print("same seed, same numbers:", np.array_equal(decimals, again))
print("in range:", whole.min() >= 1, whole.max() <= 100)

uniform(0, 1) matches RANDARRAY's default of decimals between 0 and 1; integers(1, 100, endpoint=True) matches whole numbers from 1 to 100 inclusive — without endpoint=True, NumPy's upper bound is exclusive, a classic off-by-one when translating RANDBETWEEN. Creating a generator with the same seed reproduces the same numbers exactly, so test data, simulations and audit samples can be regenerated and checked. In Excel the same formulas produce new numbers every time the sheet recalculates — pressing Delete on an empty cell is enough — so anyone who uses them for a sample must copy and paste them as values immediately, and can never show how the sample was drawn.

Step 5 — Shuffle and sample without repeats

=SORTBY(list, RANDARRAY(ROWS(list))) shuffles a list; =TAKE(SORTBY(...), 5) picks five items without repeats. NumPy and pandas have both built in:

invoices = pd.DataFrame({"Invoice": [f"INV-{1000 + i}" for i in range(40)],
                         "Amount": np.random.default_rng(1).gamma(3, 400, 40).round(2)})
shuffled = invoices.sample(frac=1, random_state=7).reset_index(drop=True)
audit = invoices.sample(n=5, random_state=7)
print(audit["Invoice"].tolist())
print("no repeats:", audit["Invoice"].is_unique, "| reproducible:",
      audit["Invoice"].tolist() == invoices.sample(n=5, random_state=7)["Invoice"].tolist())

with pd.ExcelWriter("sequences.xlsx") as xw:
    pd.DataFrame(sequence(4, 3, 10, 5), columns=["A", "B", "C"]).to_excel(xw, sheet_name="Grid", index=False)
    pd.DataFrame({"Month start": month_starts.date}).to_excel(xw, sheet_name="Months", index=False)
    audit.assign(Seed=7).to_excel(xw, sheet_name="Audit sample", index=False)
print("written as values: Grid, Months, Audit sample")

sample(frac=1) shuffles all rows; sample(n=5) draws five without replacement, so no invoice is picked twice; random_state makes both reproducible. For an audit, record the seed with the sample — as in the Audit sample sheet — so anyone can redraw it and confirm it was random rather than chosen. Writing the results with to_excel stores plain values, which is what a report or an audit file should contain; a sheet full of live RANDARRAY formulas would show a different sample every time it was opened. Larger sampling questions are covered in Take a Random Sample of Rows from a Large Excel File.

Values or formulas?

Most of the time a Python script should write the values — the grid of numbers, the list of dates, the drawn sample — because the script already knows them and values work in every version of Excel and every other spreadsheet. Writing a SEQUENCE formula is worth it only when the workbook should keep recalculating after delivery: a template where the user types a start date and the column of month-ends updates itself, say. In that case use the _xlfn. prefix and an array formula as in Step 2, and test the file in the spreadsheet versions your readers use. RANDARRAY and RANDBETWEEN formulas are almost never what a delivered file should contain; generate the numbers in Python with a seed and write them as values, so the workbook says the same thing every time it is opened.

Common pitfalls

• arange with fractional steps. The end point is unreliable; compute start + step * index.
• Column-major thinking. SEQUENCE and reshape both fill row by row.
• Exclusive upper bounds. Use endpoint=True to match RANDBETWEEN's inclusive range.
• Unseeded random numbers for samples. Seed the generator and record the seed.
• Live random formulas in delivered files. Write values, not volatile formulas.

Conclusion

Excel's sequence and random functions map onto short NumPy and pandas calls: start + step * np.arange(n).reshape(rows, cols) for SEQUENCE, pd.date_range and bdate_range for date sequences, rng.uniform and rng.integers(..., endpoint=True) for RANDARRAY and RANDBETWEEN, and DataFrame.sample for shuffles and samples without repeats. Check deterministic translations against a spreadsheet once, seed every random generator so results can be reproduced and audited, and write the outputs to Excel as values.

Frequently asked questions

What is the Python equivalent of Excel's SEQUENCE? Use NumPy: start + step * np.arange(rows * cols).reshape(rows, cols). It fills the grid row by row, as SEQUENCE does.

How do I create a sequence of dates like SEQUENCE with dates? Use pandas date_range, for example date_range("2026-01-01", periods=12, freq="MS") for month starts or bdate_range for business days.

What replaces RANDBETWEEN in Python? numpy's default_rng().integers(low, high, endpoint=True), which includes both ends of the range like RANDBETWEEN.

How do I take a random sample without repeats like SORTBY with RANDARRAY? Use DataFrame.sample(n=5, random_state=seed), which draws rows without replacement and is reproducible with the same seed.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• UNIQUE, SORT and FILTER Equivalents in pandas — other dynamic-array functions.
• VSTACK, HSTACK, TAKE and CHOOSECOLS Equivalents in pandas — reshaping arrays.
• Generate Realistic Test Data for Excel Reports with Faker — seeded random data at scale.