The SEQUENCE() function in Excel is a powerful tool for generating lists of values in a defined sequence. This function is particularly useful for business professionals who need to create a series of data without manually entering each number. Understanding and utilizing SEQUENCE() can save time and reduce errors, making it a superior alternative to the fill handle for creating sequences.
To follow along with this demonstration, please download the exercise file below:
SEQUENCE() syntax
The SEQUENCE() function syntax is as follows:
=SEQUENCE(rows, [columns], [start], [step])
Here’s a breakdown of each parameter:
rows: The number of rows in the sequence.columns: (Optional) The number of columns in the sequence. If omitted, defaults to 1.start: (Optional) The starting number of the sequence. If omitted, defaults to 1.step: (Optional) The increment for each subsequent number in the sequence. If omitted, defaults to 1.
Next, let’s explore a few quick examples of the SEQUENCE() function in action.
Example 1: Inventory Management
For our first example, let’s begin with a straightforward task on the inventory worksheet of the demo file: creating new stock keeping units (SKUs) for our inventory. We need to list 100 new SKUs in a column, starting from SKU 101.
In this case, we’ll request a sequence of 100 rows, use the default parameter of 1 column, and step increments of 1, starting at 101. This approach might seem unusual if you’ve never done it before in Excel, but in such instances, we will leave arguments we don’t need to modify as blank.
Go ahead and experiment by changing the various arguments of the function to see what you end up with. This might be the best way to learn any Excel function, including SEQUENCE().
Example 1: Monthly Budget Planning
Next for something a little more complex, head to the budget worksheet of the demo workbook.
Imagine you are preparing a monthly budget and need to list the months of the year along with a sequential number for each month. Instead of manually typing each month number using the fill handle, you can use SEQUENCE() to generate this automatically.
=SEQUENCE(12)
This formula generates a sequence from 1 to 12, representing each month. Keep in mind that because we left the other arguments blank, they defaulted to 1, meaning the sequence will generate a single column of numbers starting at 1 and extending down 12 cells.
While this might not seem very exciting—after all, we’ve just ended up with 12 numbers with no clear indication of what months they represent—it’s still more dynamic than if you had manually entered the numbers. For instance, if you needed to forecast for 18 periods, you could simply replace 12 with 18.
But let’s take it a step further. To indicate the month-end date for each row, I can use a combination of the EDATE() function with SEQUENCE():
=EDATE(A2 - 1, SEQUENCE(A5))
This formula now facilitates fully dynamic sequence generation, serving as an excellent starting point for developing various monthly budgets.
The most unusual aspect of this formula is likely the subtraction of 1 from A2. If you’re unfamiliar with the EDATE() function, its second argument specifies the number of months to add or subtract from a given start date. Since our SEQUENCE() starts at 1, it would normally add one month to the date figures. Therefore, subtracting 1 acts as a counterbalance, ensuring we get the month-end date for the last day of each previous month. Feel free to experiment further on your own if this concept is unclear!
Example 3: Sum of top N items
For our final example in this post, let’s explore how SEQUENCE() can assist in summing the top N items from a sales list. This technique is invaluable for understanding what percentage of an assortment’s performance is derived from a select group of top items. Check it out on the sales worksheet of the demo workbook.
Perhaps you’ve previously used the LARGE() function to identify the Nth largest value in a dataset. With SEQUENCE(), instead of isolating just the Nth largest values, we can capture the first through Nth values. In this example, I’ve set N to 3, which will retrieve the first, second, and third largest values.
I’ve also designed this to be user-defined, allowing the user to adjust N as needed. Consider adding data validation or a dropdown menu to provide users with guidance and sanity checks. With this setup, it’s easy to see how the top three items contribute nearly half of all sales:
=SUM(LARGE(sales[Sales],SEQUENCE(D3)))
Conclusion
Using SEQUENCE() in these scenarios not only saves time but also enhances data integrity. The function’s ability to dynamically adjust to changes in your dataset makes it a more robust solution compared to using the fill handle. By leveraging SEQUENCE(), you can streamline your workflows, reduce the risk of errors, and ensure that your data remains consistent and reliable.
For more insights into dynamic array functions, check out my book, Modern Data Analytics in Excel:
Leave a Reply