Function Problems

How to Create a Repeating Number Sequence From 1 to 5 in Excel

Huma Ashraf Ch Oct 10, 2026 874 views

Question details

The user needs an Excel formula to automatically generate a cyclic sequence of numbers (1, 2, 3, 4, 5) down a column without manually typing starting values.

Excel Formula for a Repeating Number Sequence From 1 to 5

Product: Excel
Device & OS: not provided
Scenario: Creating a repeating numbered list or repeating sequence cycle for data organization and formatting.
Observed behavior: The goal is to calculate a 5-item cycle based on the row number where the end of the cycle correctly returns 5 instead of 0.

Before you start

Determine how many rows you need the sequence to cover, and verify if your version of Excel supports dynamic array functions like SEQUENCE before choosing a method.

Solution 1Recommended

Use MOD and ROWS Functions (Compatible with all Excel versions)

This is the most reliable method to create a repeating sequence in any version of Excel by calculating cycles based on relative row numbers.

This formula uses the ROWS function to count the current row and the MOD function to return a remainder based on a 5-item cycle.

Select the starting cell

Click on the first cell in your worksheet where you want the number sequence to begin.

Enter the formula

Type the formula =MOD(ROWS($1:1)-1,5)+1 into the formula bar.

Apply and fill down

Press Enter. Then, click and hold the small green square (fill handle) at the bottom-right corner of the cell and drag it down to fill the formula into the remaining rows.

Use MOD and ROWS Functions (Compatible with all Excel versions)

How it works: The ROWS function generates a sequential number as you drag down. The MOD function divides that number by 5 and returns the remainder. Subtracting and adding 1 ensures the sequence outputs 5 instead of 0 at the end of each cycle.

Solution 2

Use the SEQUENCE Function (Microsoft 365 and Newer)

Use this modern dynamic array formula to instantly spill the entire sequence into a specific number of rows without manual dragging.

Solution 3

Use the LET Function for a Location-Independent Formula

This advanced approach assigns a variable name to the calculation result, making the formula easier to read and avoiding the standard subtraction/addition offset trick.

Free Microsoft Office alternative

Easily Create Repeating Sequences with WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced mathematical functions, including MOD, ROWS, and dynamic array calculations. You can effortlessly manage complex data sequences without paying for expensive software subscriptions.

1. Open your workbook: Launch WPS Spreadsheet and open the document where you need the numbered sequence.
2. Select the starting cell: Click on the exact cell where the cyclic sequence should begin.
3. Input the repeating formula: Type =MOD(ROWS($1:1)-1,5)+1 into the formula bar and press Enter.
4. Drag to fill: Click the small square at the bottom right of the active cell and drag it downward to populate the rest of your sequence.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Natively supports repeating sequence formulas and array functionsLightweight, fast-loading software with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the sequence to repeat from 1 to 10 instead of 1 to 5?

In the standard formula =MOD(ROWS($1:1)-1,5)+1, simply change the divisor '5' to '10'. The updated formula will be =MOD(ROWS($1:1)-1,10)+1.

Why does my standard MOD formula output 0 at the end of the sequence?

The normal MOD function returns a remainder of 0 when a number is perfectly divisible by the divisor (e.g., 5 divided by 5). To force it to return the maximum number instead of 0, you must offset the calculation by subtracting 1 before the MOD function and adding 1 after it.

Can I make the sequence repeat across columns instead of rows?

Yes. You just need to replace the ROWS function with the COLUMNS function. For example, use =MOD(COLUMNS($A:A)-1,5)+1 in the first cell, then drag the fill handle to the right to spread the repeating sequence across the columns.

Excel & Data Problems