Function Problems

How to Create Sequential Groups of Four-Digit Numbers in Excel

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to generate 169 rows of data containing groups of ten consecutive four-digit numbers, starting at 0101 and increasing sequentially.

Product: Excel
Device & OS: not provided
Scenario: Creating a large, structured sequence of numbers formatted as four digits (with leading zeros) across a specific grid size (10 columns, 169 rows).
Observed behavior: Generating the sequential array and properly formatting it so that numbers below 1000 retain their leading zero (e.g., 0101, 0102).

Before you start

Ensure your version of Excel or your spreadsheet software supports modern dynamic array functions such as SEQUENCE and WRAPROWS.

Solution 1Recommended

Use SEQUENCE and WRAPROWS with Custom Formatting

Use dynamic array functions to generate the numbers mathematically, and apply a custom cell format to display the leading zero.

This method keeps the underlying data as pure numeric values while changing how they look visually. This is ideal if you need to perform calculations on these numbers later.

Enter the formula

Select cell A1 and enter the formula: =WRAPROWS(SEQUENCE(1690,,101),10). This creates a total of 1,690 numbers starting at 101, wrapped into rows of 10.

Press Enter to spill the array

Hit Enter on your keyboard. The formula will automatically spill across the range A1:J169.

Select the data range

Highlight the entire populated array from cell A1 down to J169.

Apply custom number formatting

Right-click the selection and choose 'Format Cells'. Navigate to the 'Custom' category, type '0000' in the Type field, and click OK. All numbers will now display as four digits.

Numeric Values Intact: Even though 0101 displays with a leading zero, Excel still reads it as the number 101.

Solution 2

Use the TEXT Function for Direct String Output

Wrap your dynamic array formula inside the TEXT function to instantly generate the sequence as formatted text strings.

Free Microsoft Office alternative

Generate Complex Number Sequences Easily in WPS Spreadsheet

WPS Office perfectly supports advanced dynamic array functions like SEQUENCE and WRAPROWS. You can quickly generate, format, and organize massive numeric datasets without paying for expensive software.

1. Open a new spreadsheet: Launch WPS Spreadsheet and create a new blank workbook.
2. Enter the dynamic formula: Click on cell A1 and type =TEXT(WRAPROWS(SEQUENCE(1690,,101),10),"0000").
3. Populate the grid: Press Enter. The numbers will instantly spill across the cells in the exact 169x10 grid with four-digit formatting.

Native support for advanced dynamic array formulas100% compatibility with Microsoft Excel files (.xlsx)Built-in custom formatting for leading zerosLightweight, fast, and completely free to use

Frequently Asked Questions

Why are leading zeros disappearing when I type them?

Excel and most spreadsheet programs automatically treat entries starting with zero as numeric values and remove the leading zero. To keep it, you must either format the cell as Text before typing, type an apostrophe (') before the number, or apply a custom number format like '0000'.

Can I make the sequence flow down columns instead of across rows?

Yes. If you want the numbers to count downwards in columns before moving to the next column, use the WRAPCOLS function instead of WRAPROWS.

What if my software doesn't support the SEQUENCE function?

If you are using an older version of Excel that lacks dynamic arrays, you will need to use a combination of ROW(), COLUMN(), and basic arithmetic to calculate the increments, then copy that formula manually across your desired grid size.

Excel & Data Problems