Function Problems

How to Automatically Generate Guest Numbers in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to automatically generate a sequential list of guest entries (e.g., G.1 through G.100) dynamically based on an input value.

Product: Spreadsheets
Device & OS: not provided
Scenario: Creating automated, scalable guest lists and table numbers for event planning or data management.
Observed behavior: The spreadsheet should dynamically populate the sequence of rows and update instantly whenever the base quantity input changes.

Before you start

Ensure your spreadsheet application supports dynamic array functions like SEQUENCE and VSTACK, as these are required for the formula to spill automatically into adjacent cells.

Solution 1Recommended

Use VSTACK and SEQUENCE Functions

Utilize a combination of dynamic array formulas to instantly generate and arrange a series of numbered guest entries based on a single reference cell.

The SEQUENCE function is designed to generate consecutive numbers in an array, while VSTACK allows you to stack these arrays vertically. By combining them and using an ampersand (&) to add a text prefix, you can dynamically create complex list layouts that respond to a single input value.

Set up your input cell

Select a cell, such as A2, and type your desired total number of guests (for example, 100).

Input the dynamic array formula

Click the cell where you want your generated list to begin. Type the formula: =VSTACK("G."&SEQUENCE(1,A2,1,1),SEQUENCE(1,A2,3,2))

Press Enter to spill the results

Hit Enter on your keyboard. The formula will automatically spill the generated entries, such as G.1, G.2, etc., across the necessary cells. If you change the value in cell A2, the list will adjust automatically.

Avoiding Errors: If you receive a #SPILL! error, it means there is existing data blocking the formula from expanding. Clear the cells in the formula's path to resolve the issue.

Efficient Spreadsheet Management

Easily Generate Dynamic Sequences in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like SEQUENCE and VSTACK, allowing you to manage guest lists, automate repetitive data entry, and handle complex array formulas seamlessly without any hassle.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet file.
2. Enter your target count: Type the total number of guests into cell A2.
3. Apply the SEQUENCE formula: Enter the formula =VSTACK("G."&SEQUENCE(1,A2,1,1),SEQUENCE(1,A2,3,2)) into the desired starting cell to generate your list instantly.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports modern dynamic array functions for automated list generationLightweight application with a fast, familiar user interfaceFree to use for everyday data management and event planning

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SEQUENCE formula returning a #NAME? error?

The #NAME? error occurs if your spreadsheet software version is too old to support dynamic array functions. Ensure you are using an updated version of your spreadsheet software, like the latest WPS Office, which natively supports SEQUENCE.

How can I change the prefix from 'G.' to 'Guest'?

You can easily customize the prefix by modifying the text enclosed in quotes within the formula. Change "G." to "Guest " so the formula looks like: ="Guest "&SEQUENCE(1,A2,1,1).

What does the VSTACK function do in this formula?

VSTACK stands for Vertical Stack. It takes multiple data arrays—in this case, your generated guest names and table assignments—and stacks them vertically into a single, cohesive output range.

How do I fix a #SPILL! error when using array formulas?

A #SPILL! error indicates that the formula does not have enough empty adjacent cells to display all its results. Simply delete or move the data that is obstructing the output path, and the formula will populate correctly.

Excel & Data Problems