Need to repeat a sequence — assign shifts in rotation, alternate colors, cycle through a roster? MOD wraps a counter back to the start so an index loops 1, 2, 3, 1, 2, 3… forever.
The example
Rotate through 3 names endlessly.
| A | B | |
|---|---|---|
| 1 | Row | Slot (of 3) |
| 2 | 1 | 1 |
| 3 | 4 | 1 |
| 4 | 5 | 2 |
The formula
The formula:
How it works
How it works:
MOD(counter, N)returns the remainder, which cycles 0, 1, …, N−1, then resets.- Add 1 to get a 1-based slot (1…N) for use with INDEX.
- Pair with INDEX to pull the actual item:
=INDEX(list, MOD(ROW()-2, N)+1). - Use it for round-robin assignment, alternating bands, repeating schedules, or zebra-striping by group.
Alternating two states is the simplest case: =MOD(ROW(), 2) returns 0, 1, 0, 1… — perfect for even/odd logic or two-color banding. For three or more, raise the divisor to match the cycle length.
Try it: interactive demo
Cycle length and position.
Variations
Pull the item
With INDEX:
Alternate two
Even/odd:
Group of N rows
Block number:
Pitfalls & errors
Mind the offset. Subtract the header rows so the first data row starts the cycle at the right slot.
+1 for 1-based. MOD is zero-based; add 1 before feeding INDEX.
N must be positive. A zero or negative divisor errors or gives odd results.
Practice workbook
Frequently asked questions
How do I cycle through a list repeatedly in Excel?
How do I alternate between two values per row?
How do I assign items in round-robin?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class