The SEQUENCE function in Excel generates a row/column/matrix of sequential or interval-based numbers or dates.
Do we need another way to generate sequences? While Excel already offers methods for generating sequences, the SEQUENCE function brings several advantages:
Simplicity: SEQUENCE reduces the need for complex formulas or manual steps.
Flexibility: It can create multi-dimensional arrays, making it easier to manage large datasets.
Efficiency: The function can be dynamically linked to other formulas, updating automatically as your data changes
The syntax for SEQUENCE is straightforward:
= SEQUENCE(rows, [columns], [start], [step])
Rows: The number of rows to fill with numbers.
Columns: The number of columns to fill with numbers (optional; defaults to 1).
Start: The starting number of the sequence (optional; defaults to 1).
Step: The increment for each subsequent number (optional; defaults to 1).
So how can the SEQUENCE function help you? Here’s a couple of examples:
Example 1: Waffle Charts
A Waffle Chart is a popular way to present a percentage. Not heard of a Waffle Chart? It’s basically a square grid, usually consisting of 100 smaller squares arranged in a 10-by-10 layout. The squares are colored according to the percentage you are aiming to visualize. In the screenshot below we want to show that the USA accounts for 34% of the allocated project budget.
The matrix of numbers on the right is the “before” version of the data. It was generated with a single formula: =SEQUENCE(10,10,100,-1)
Example 2: Loan Calculator
The screenshot below was taken from an Excel-based loan calculator.
Cell D3 contains the formula =SEQUENCE(B5). By simply changing the value in B5 (the number of payments), Excel re-generates the sequential list of numbers down column D
Cell E3 contains the formula =SEQUENCE(B5,1,B6,B6). This single formula calculates the total amount repaid after each payment. The SEQUENCE function is a versatile and powerful addition to Excel, streamlining the creation of sequential data and improving productivity. Whether you’re managing dates, generating datasets, or constructing complex arrays, SEQUENCE simplifies your workflow, making Excel even more robust and user-friendly.
Want to Learn More?
Watch the replay of our live event with Mike Thomas (TheExcelTrainer): Excel – Master the Latest & Greatest Functions. We go in-depth on VSTACK, SEQUENCE, and other new innovative functions added since 2020, exclusively available to Microsoft 365 and Excel 2021 users.
Replays are available exclusively for MPUG members. Not a member yet? Join today!
Elevate your project management skills and propel your career forward with an MPUG Membership. Gain access to 500+ hours of PMI-accredited training, live events, and a vibrant online community. Watch a free lesson and see how MPUG can teach you to Master Projects for Unlimited Growth. JOIN NOW
