Skip to content
Tutorials Center
My learning

Topics

Lesson 48 of 93 ยท Dynamic Arrays

SEQUENCE, RANDARRAY and Array Arithmetic

Generate number grids and calendars with SEQUENCE, understand row and column arrays, array arithmetic and MMULT, with JavaScript and NumPy stand-ins.

Until now, one formula meant one value. With dynamic arrays a formula can return a whole grid, and you can do arithmetic on whole ranges at once. That raises two practical questions. How do you create an array when you have no data yet, for example the numbers 1 to 100 or the days of a month? And what happens when you multiply a column of numbers by a row of numbers?

SEQUENCE and RANDARRAY create arrays. The rules of array arithmetic (often called broadcasting) decide what operators do with arrays of different shapes. Excel cannot run on this site, so the formulas here are read-only. Each one is paired with a JavaScript stand-in (and a NumPy version) that you can run. If spilling is new to you, read Dynamic Arrays and Spilling first.

SEQUENCE and RANDARRAY exist in Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and older do not have them.

Make arrays from nothing

SEQUENCE Evenly spaced numbers in a grid.
RANDARRAY A grid of random numbers.
Dates Add a start date to build calendars.

No typing, no fill handle, no helper column.

1 / 2

SEQUENCE: numbers on demand

SEQUENCE(rows, [columns], [start], [step]) returns an array of numbers. Only rows is required. Columns default to 1, the start to 1 and the step to 1. Numbers fill row by row, left to right, then move down.

TEXT
=SEQUENCE(5)             1, 2, 3, 4, 5 down one column
=SEQUENCE(1, 5)          1, 2, 3, 4, 5 across one row
=SEQUENCE(3, 4)          3 rows by 4 columns, numbered 1 to 12 row by row
=SEQUENCE(4, 1, 10, 5)   10, 15, 20, 25
=SEQUENCE(5, 1, 100, -10) 100, 90, 80, 70, 60

Since a date is just a number (see Dates and Times in Excel), a sequence can produce dates too. Format the result as a date and SEQUENCE becomes a timeline generator:

TEXT
=SEQUENCE(7, 1, TODAY())                 the next seven days
=EDATE(DATE(2026,1,1), SEQUENCE(12,1,0)) the first of every month in 2026

The second formula works because EDATE accepts an array of month offsets, so it also returns an array. Most functions that take a single value will do the same when you hand them an array. This is called lifting.

A calendar from one formula

A monthly calendar is a 6-by-7 grid: at most six weeks, seven days each. If A1 holds the first day of the month, then the Sunday on or before it is A1 - WEEKDAY(A1) + 1. Counting up from that Sunday for 42 cells gives every cell of the grid:

TEXT
=SEQUENCE(6, 7, A1 - WEEKDAY(A1) + 1)

Format the result with the custom number format d, so cells show only the day number. You can blank out days of other months by adding IF(MONTH(x) = MONTH(A1), x, "") with LET, which comes in LET, LAMBDA and Reusable Formulas.

JS SEQUENCE and a month calendar as JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The stand-in is our model of the logic. In Excel the cells would hold date serial numbers, and a number format would show only the day.

RANDARRAY: random grids

RANDARRAY([rows], [columns], [min], [max], [whole_number]) fills a grid with random numbers. With no arguments it returns a single decimal between 0 and 1. Set whole_number to TRUE for integers.

TEXT
=RANDARRAY(5, 3)               5 by 3 decimals between 0 and 1
=RANDARRAY(4, 1, 1, 6, TRUE)   four dice rolls
=SORTBY(A2:A11, RANDARRAY(ROWS(A2:A11)))   shuffle a list

RANDARRAY is volatile: it produces new numbers every time the workbook recalculates, even if you only edit another cell. The shuffle formula shows the usual trick: sort the list by a column of random keys. Random numbers and simulations are the subject of Random Numbers and Simple Simulations.

Row arrays and column arrays

An array has a shape: rows by columns. SEQUENCE(3) is a column array (3 by 1). SEQUENCE(1, 3) is a row array (1 by 3). This matters because Excel combines arrays by position. Constants typed in braces follow the same rules: a comma separates columns and a semicolon separates rows.

TEXT
={1, 2, 3}        a row array, 1 by 3
={1; 2; 3}        a column array, 3 by 1
={1, 2; 3, 4}     a 2 by 2 array

TRANSPOSE flips an array, turning a row into a column and the other way around.

Array arithmetic and broadcasting

When you apply an operator to two arrays, Excel matches them cell by cell. The rules:

  1. Same shape: the operation happens position by position.
  2. A single value and an array: the single value is used against every cell. =A2:A10 * 2 doubles every cell.
  3. A column and a row: the column is stretched across the columns and the row is stretched down the rows, which produces a full grid. This is the classic multiplication table: =SEQUENCE(9) * SEQUENCE(1, 9).
  4. Anything else that does not line up: the missing positions become #N/A.
TEXT
=SEQUENCE(9) * SEQUENCE(1, 9)      a 9 by 9 multiplication table
=A2:A6 * B2:B6                     same shape: row-by-row products
=A2:A6 + {10, 20}                  5 by 1 plus 1 by 2 gives a 5 by 2 grid
=SEQUENCE(3) + SEQUENCE(2)         3 by 1 plus 2 by 1: the third row is #N/A

The idea is the same one that SUMPRODUCT uses internally, and it is why a formula such as =SUM((A2:A10 > 5) * B2:B10) works: the comparison produces an array of TRUE and FALSE, multiplication turns them into 1 and 0, and SUM adds the result.

JS Broadcasting rules as JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

MMULT in one minute

MMULT(array1, array2) is matrix multiplication, which is not the same as *. The number of columns of the first array must equal the number of rows of the second, and each result cell is the sum of products of one row and one column. It is the engine behind a lot of financial and statistical models, and it answers questions such as "total cost per order" when quantities form a grid and prices form a column.

TEXT
=MMULT(B2:D5, F2:F4)   4 by 3 times 3 by 1 gives 4 by 1: one total per row

If the sizes do not fit, the result is #VALUE!. For one total per row, SUMPRODUCT with MMULT-style layouts and BYROW (see LET, LAMBDA and Reusable Formulas) are often easier to read.

The same ideas in NumPy

NumPy, the Python array library, uses exactly this broadcasting model, which is why it is a good place to experiment. This runs in real Python on the site.

Python Broadcasting and matrix product in NumPy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the difference in behavior at the mismatch: NumPy raises an error, Excel fills the missing cells with #N/A. The stretching rule for sizes of 1 is identical.

Common mistakes

  • Spill blocked. SEQUENCE(10) needs ten free cells below the formula. Anything in the way gives #SPILL!.
  • Hard-coded sizes. Write SEQUENCE(ROWS(A2:A100)), or better a spill reference such as ROWS(A2#), instead of a typed 99.
  • Forgetting volatility. RANDARRAY changes constantly. Copy and paste as values to freeze a random sample.
  • Mixing up rows and columns. SEQUENCE(1, 5) and SEQUENCE(5, 1) look alike but combine differently with other arrays.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =SEQUENCE(2, 3) return?

    The first argument is rows and the second is columns. Numbers fill each row from left to right before moving down.
  2. 2. What does =SEQUENCE(3) * SEQUENCE(1, 4) produce?

    A 3 by 1 array and a 1 by 4 array are each stretched to fill 3 by 4, so every row value is multiplied by every column value.
  3. 3. Why does a RANDARRAY result keep changing?

    RANDARRAY is volatile. To keep a sample, copy it and paste as values.
  4. 4. In an array constant, what separates rows?

    Commas separate columns and semicolons separate rows, so {1;2;3} is a column and {1,2,3} is a row. (This follows the US-English list separator; some regional settings differ.)
  5. 5. What is the result of adding a 3 by 1 array to a 2 by 1 array?

    Sizes other than 1 must match. Positions with no partner in the shorter array become #N/A.

Summary

  • SEQUENCE(rows, columns, start, step) creates a grid of evenly spaced numbers, filled row by row, and with a date as start it creates timelines and calendars.
  • RANDARRAY creates random grids and is volatile.
  • Arrays have a shape; {1,2,3} is a row and {1;2;3} is a column.
  • Operators work position by position; a single value or a size of 1 is stretched, and anything else that does not line up becomes #N/A.
  • MMULT is true matrix multiplication and needs columns of the first to equal rows of the second.
  • NumPy follows the same stretching rules and is handy for testing ideas.

Next: LET, LAMBDA and Reusable Formulas.