Excel: How to Use the SEQUENCE Function

Photo of a person using the SEQUENCE function in Excel.

The SEQUENCE function in Excel generates a row/column/matrix of sequential or interval-based numbers or dates.

A screen shot of a row/column/matrix of sequential or interval-based numbers or dates generated by the  Excel SEQUENCE function.

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.

A screen shot of a waffle chart showing 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.

Screen shot 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!

mpug logo

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

Written by TheExcelTrainer
Mike Thomas has worked in the IT training business since 1989. He is a subject matter expert in a range of technologies, his primary focus and passion being Microsoft Office (especially Excel) and Power BI. In 2012 Mike founded The Excel Trainer where he has produced nearly 200 written and video-based Excel tutorials. He has recorded several Excel training courses for Pluralsight and in his career delivered thousands of courses and webinars on a wide variety of technology-related topics. Mike is a Fellow of The Learning and Performance Institute and has worked with and for a large number of global and UK-based companies and organizations across a diverse range of sectors. In addition to training, he also designs and develops Microsoft Office-based solutions that automate key business tasks and processes.
Share This Post
Have your say!

Leave a Reply