Harnessing the Power of the SEQUENCE Function in Excel

The SEQUENCE function in Excel generates a list or grid of sequential numbers from a single formula. Instead of typing 1, 2, 3 and dragging the fill handle, you write one formula, press Enter, and Excel fills as many rows and columns as you asked for. Change the formula and the whole list updates.

SEQUENCE is available in Excel for Microsoft 365 and current versions of Excel for Windows, Mac, the web, and mobile. It is one of the dynamic array functions, which means its results "spill" from the cell where you type the formula into the cells around it.

This guide explains how SEQUENCE works, walks through examples for simple lists, custom steps, grids, and dates, covers more advanced uses, and includes a free SEQUENCE formula builder that writes the formula for you and previews the result.

Understanding the SEQUENCE Function

SEQUENCE returns an array of numbers that increase, or decrease, by a fixed amount. You control how many rows and columns the array fills, what number it starts at, and how big each step is. The syntax is:

=SEQUENCE(rows,[columns],[start],[step])

Only the first argument is required. Any optional argument you leave out defaults to 1, according to Microsoft's SEQUENCE function reference.

For example, =SEQUENCE(10) asks for ten rows, one column, starting at 1 and stepping by 1. The result is a single column containing the numbers 1 through 10.

How Spilling Works

When you enter a SEQUENCE formula, the result spills into as many cells as it needs. Only the top-left cell holds the formula. When you select any cell in the results, a blue border outlines the whole spill range, and the formula bar shows the formula in gray because it lives in the first cell.

Spilling needs empty space. If any cell in the spill range already contains something, Excel shows a #SPILL! error instead of the results. Clear the blocking cells and the list appears.

You can refer to the entire spill range by adding a hash sign after the first cell. If your SEQUENCE formula sits in A2, then =SUM(A2#) adds up every number it produced, no matter how many there are.

1. Generating Simple Sequences

For a quick demonstration, see the short video on generating simple sequences.

Example 1: A Basic Column of Numbers

To create a column of numbers from 1 to 15, click an empty cell, type =SEQUENCE(15), and press Enter. Excel fills fifteen rows down from that cell with the numbers 1 through 15.

This is the fastest way to number the rows in a list. Because the numbers come from a formula, you can change 15 to 50 and the list grows instantly.

Example 2: A Horizontal Row of Numbers

To create a row of numbers from 1 to 10 instead, set rows to 1 and columns to 10 with =SEQUENCE(1,10). The numbers spill across ten columns to the right. This is handy for numbering columns, such as week numbers across the top of a schedule.

2. Customizing Sequences

Example 1: A Custom Start and Step

To generate ten numbers that start at 5 and increase by 3, use =SEQUENCE(10,1,5,3). Rows is 10, columns is 1, start is 5, and step is 3.

The step can also be negative to count down, or a decimal for fractional increments. For instance, =SEQUENCE(5,1,100,-10) returns 100, 90, 80, 70, and 60, and =SEQUENCE(5,1,0,0.25) returns 0, 0.25, 0.5, 0.75, and 1.

Example 2: A Multi-Column Grid

To create a grid with 3 rows and 4 columns, use =SEQUENCE(3,4). Excel fills across each row before moving down to the next one, so the first row holds 1 to 4, the second holds 5 to 8, and the third holds 9 to 12.

If you want the numbers to run down each column first instead, wrap the formula in TRANSPOSE and swap the dimensions: =TRANSPOSE(SEQUENCE(4,3)) returns a 3 by 4 grid where the first column reads 1, 2, 3.

3. Using SEQUENCE With Dates

Excel stores dates as serial numbers, where each day is one more than the day before. That means SEQUENCE can generate dates as easily as numbers. Give it a start date and a step measured in days, then format the results as dates.

The examples below assume your start date is in cell A2. Keeping the start date in a cell, rather than typing it inside the formula, makes the list easy to reuse. You can also make it automatic: =EOMONTH(TODAY(),-1)+1 always returns the first day of the current month.

Example 1: Monthly Dates

A common mistake is to step by 30 days to get monthly dates. Months range from 28 to 31 days, so a 30-day step drifts away from the first of the month after a few rows. Instead, build each date from its year and month:

=DATE(YEAR(A2),MONTH(A2)+SEQUENCE(12,1,0),1)

Here SEQUENCE produces 0 through 11, which are added to the starting month. DATE handles the rollover into the next year automatically, and every result lands exactly on the first of a month. If you want the same day each month instead, such as the 15th, =EDATE(A2,SEQUENCE(12,1,0)) moves forward one whole month at a time from the start date.

Example 2: Weekly Dates

Weekly dates are simpler because every week is exactly seven days. =SEQUENCE(10,1,A2,7) returns ten dates, each seven days after the one before. If A2 holds a Monday, every result is a Monday, which makes this ideal for the week-starting column of a schedule or timesheet.

If the results show as five-digit numbers rather than dates, select the spill range and apply a date format from the Number group on the Home tab. The underlying values are correct; they simply need a date format to display as dates.

4. Advanced Use Cases

Example 1: Creating a Number Grid

=SEQUENCE(5,5) creates a 5 by 5 grid of the numbers 1 through 25. Grids like this are useful for seat or bin numbering, bingo-style cards, and test data. Combine them with other functions for more: =SEQUENCE(10)*SEQUENCE(1,10) multiplies a column by a row and produces a full ten by ten multiplication table from one formula.

Example 2: A Dynamic Range

SEQUENCE can read its arguments from other cells, so the size of the list can change on its own. =SEQUENCE(A1) returns 1 through whatever number is in A1. If A1 contains 7, you get 1 through 7; change it to 12 and the list grows to 12.

A practical version numbers a list automatically as it grows. If names are in B2:B100, =SEQUENCE(COUNTA(B2:B100)) counts the filled cells and produces exactly that many row numbers.

Example 3: Creating a Calendar

To list every day in a month, combine SEQUENCE with EOMONTH, which finds the last day of the month. With the first of the month in A2, =SEQUENCE(DAY(EOMONTH(A2,0)),1,A2) returns every date in that month, whether it has 28, 29, 30, or 31 days. The DAY part works out how many rows are needed, so the formula never overshoots into the next month.

For a traditional seven-column calendar layout, =SEQUENCE(6,7,A2-WEEKDAY(A2)+1) returns six weeks of dates that start on the Sunday on or before the first of the month. Format them to show only the day number and you have the body of a monthly calendar.

Example 4: Pairing SEQUENCE With Other Functions

SEQUENCE also works as a building block inside other formulas. =INDEX(B2:B20,SEQUENCE(5)) returns the first five items in a list. =LARGE(C2:C50,SEQUENCE(3)) returns the top three values in a range. And =SORTBY(SEQUENCE(10),RANDARRAY(10)) shuffles the numbers 1 through 10 into a random order, which is useful for drawing names or assigning random groups.

Quick Reference

The table below collects the most common SEQUENCE patterns in one place, so you can copy the one you need.

Free SEQUENCE Formula Builder

Use the builder below to create a SEQUENCE formula without memorizing the argument order. Choose numbers, daily dates, weekly dates, or the first of each month, then set the size, start, and step. The builder writes the formula and previews exactly what Excel will spill.

Copy to Excel pastes your settings into cells B2 through B5 and a live formula in B7 that reads them, so changing a setting in Excel updates the list. Print gives you a copy of the formula and preview for reference.

Common SEQUENCE Errors and Fixes

#SPILL! error. Something is in the way of the spill range. Clear the cells below or to the right of the formula.

#NAME? error. Your version of Excel does not support SEQUENCE. It requires Excel for Microsoft 365 or a recent standalone version. In older versions, use ROW with the fill handle, such as =ROW()-1 copied down.

Dates show as numbers. Apply a date format to the spill range. The values are correct serial dates.

Numbers do not update when rows are added. Base the rows argument on a count, such as COUNTA, rather than a fixed number.

Monthly dates drift. Replace a 30-day step with the DATE and MONTH approach or EDATE, shown in the dates section above.

Final Thoughts

The SEQUENCE function is a versatile and powerful tool in Excel, simplifying the creation of lists, grids, and date sequences. By understanding its four arguments and how spilling works, you can use SEQUENCE to streamline data entry, generate dynamic ranges that grow on their own, and build schedules and calendars from a single formula.

Whether you are managing schedules, performing calculations, or designing complex data models, SEQUENCE can make your work in Excel more efficient and effective. When your spreadsheets start turning into project plans and schedules for a whole team, Updoot is business management software that brings tasks, scheduling, and time tracking into one place for $5 per user per month.

Sources

Microsoft Support: SEQUENCE function

Microsoft Support: EDATE function