Ten Ways to use the Excel SEQUENCE Function

Overview

The SEQUENCE function is one of the newer dynamic array functions (DAFs) and it’s extremely versatile – whether by itself, when combined with other functions, and when built into custom functions via LAMBDA.  It returns an array of sequential numbers that can update dynamically based on the references supplied in the arguments.

In this article, we’ll look at ten different examples of how you can use it in your Excel files to automate calculations – saving you time and reducing the risk of errors.  Make sure to download the example file below to leverage these examples and use as a reference!

The syntax for the SEQUENCE function is as follows:

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

 The arguments within the SEQUENCE function are:

• rows (required) – the number of rows to return.  You must at least supply a comma for the row argument.  If left blank, it will only return one row.
• columns (optional) – the number of columns to return.  If omitted or left blank, only one column is returned.
• start (optional) – the starting value.  If omitted or left blank, it defaults to 1.  It can be a positive or negative whole number or decimal.
• step (optional) – the increment between each value.  If omitted or left blank, it defaults to 1.  It can be a positive or negative whole number or decimal.

Option 1: Dynamic number list (rows) The most basic way to use SEQUENCE is to return a specified number of rows in a spilling dynamic formula.  To make the number of rows dynamic, include a reference to a function like COUNTA, as seen in the example below.  In this example, when you add a value to a new row in column B, the spill range dynamically updates in column A to include the new row! Option 2: Number list (columns) SEQUENCE can also be helpful in a basic way to label column headers – just specify the number of columns you want to use as either a fixed value or a reference. Option 3: Date headers (columns) A great way to use SEQUENCE with columns is to use it to automate displaying dates. When you combine it with the EOMONTH function, you can return columns for the end of however many months you’d like, spilling across columns (like below), or rows, or both. Option 4: Date header labels (columns) Instead of specific dates, maybe you just want to show the month with a specific date format. Just combine SEQUENCE with some date functions and the powerful TEXT function with your preferred date format, like in the example below. Option 5: Annual date list (rows) One other common date example is returning a list of anniversary dates for a specified number of years. Just combine SEQUENCE with the DATE function, and the date component functions (YEAR, MONTH, and DAY). Option 6: List of workdays Do you need to calculate a list of workdays (ignoring weekend days and holidays)? Use SEQUENCE within the WORKDAY.INTL function. Just supply it with your list of holidays and tell it how many weekdays you want to display. Option 7: Counting backwards Perhaps you want to count backwards? No problem! Just make sure to supply a negative step value and optionally a negative starting value. Option 8: Counting backwards (by 5s) You may want a different step value and starting point when counting backwards. That’s easy too – just make sure to adjust the starting value and/or step value. Option 9: Counting by decimals (2D) You don’t have to count by whole numbers – you can use decimals if you need to. Likewise, you can spill that array of values across rows and columns! Just specify the number of rows and columns (or reference values that contain them), and use a decimal for that step value. Option 10: List of roman numerals ROMAN is probably not a function you use much, but if you need several values in a roman numeral format, SEQUENCE can automate it!

Summary

Like many other DAFs, the SEQUENCE function is super helpful and helps you automate several calculations, from a simple checklist to a complex custom formula.  Even if you use it in the most basic way, you’ll grow to love it and use it often.

What’s your most creative way you’ve used the SEQUENCE function?  Any other examples worth sharing?  Let us know in the comments below!