Excel: Generate Every Number Between Two Values
Question details
The user needs to expand multiple beginning-and-ending number ranges into individual numbers automatically.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Telephone or other numeric ranges are stored with starting values in one column and ending values in another, and the user wants one generated number per row.
- Observed behavior
- The numbers must otherwise be entered manually instead of being spilled automatically from the supplied ranges.
Place each range's beginning number in column B and ending number in column C, with one range per row. Confirm that every ending value is greater than or equal to its beginning value and that the output area is empty.
Use a dynamic array formula to expand all number ranges
Use this method in Microsoft 365, Excel for the web, or a compatible WPS Spreadsheet version when several beginning-and-ending ranges must become one continuous list.
The formula reads each beginning and ending pair from B2:C3, creates a sequence for every pair, combines the sequences, and spills the results into the cells below the formula. Change B2:C3 to match your source range.
For telephone numbers with leading zeros, format the output cells as Text or apply the required custom number format after generating the sequence.
In Excel or WPS Spreadsheet, click the first empty cell where the expanded list should begin, such as E2.
Enter =DROP(REDUCE("",SEQUENCE(ROWS(B2:C3)),LAMBDA(x,y,VSTACK(x,SEQUENCE(INDEX(B2:C3,y,2)-INDEX(B2:C3,y,1)+1,1,INDEX(B2:C3,y,1),1)))),1) and press Enter.
Replace B2:C3 with the actual cells containing the beginning and ending numbers, keeping the beginning column first and the ending column second.
Check the cells below the formula to confirm that every value from each range appears once and that no existing content blocks the spill area.
Generate one range with the SEQUENCE function
Use this shorter option when you only need to expand one beginning-and-ending pair at a time.
Expand Number Ranges Automatically in WPS Spreadsheet
WPS Spreadsheet can generate each number between supplied beginning and ending values with dynamic array formulas. Enter the range-expansion formula in an empty cell, update the source range, and let the results spill automatically without manual entry.
- 1. Open the spreadsheet: Open your XLSX or spreadsheet file in WPS Spreadsheet and confirm that the beginning and ending values are in adjacent columns.
- 2. Choose the output cell: Click an empty cell outside the source data, such as E2, where the generated list can spill downward.
- 3. Enter the expansion formula: Type =DROP(REDUCE("",SEQUENCE(ROWS(B2:C3)),LAMBDA(x,y,VSTACK(x,SEQUENCE(INDEX(B2:C3,y,2)-INDEX(B2:C3,y,1)+1,1,INDEX(B2:C3,y,1),1)))),1), then press Enter.
- 4. Update and verify the range: Change B2:C3 to the actual beginning-and-ending range, then confirm that the generated values fill the output area without spill conflicts.
Frequently Asked Questions
Which cells should the formula reference?
Use a two-column range in which the first column contains beginning values and the second contains ending values. For example, B2:C3 means beginnings are in B2:B3 and endings are in C2:C3.
Can this formula generate telephone numbers with leading zeros?
The formula generates numeric values, so leading zeros may not display automatically. Format the output cells as Text or use a custom number format with the required digit count.
What happens if an ending value is smaller than its beginning value?
The sequence length becomes invalid for that row and may produce an error. Correct the range so the ending value is equal to or greater than the beginning value.
Why do I get a spill or blocked-cell error?
The cells below or beside the formula may contain existing data. Clear the intended output area and ensure the formula is not inside a restricted table or merged-cell region.
Can I expand just one beginning-and-ending pair?
Yes. If the beginning value is in B2 and the ending value is in C2, enter =SEQUENCE(C2-B2+1,1,B2,1) in an empty cell to generate that single range.
