9.5 SEQUENCE and RANDARRAY
=SEQUENCE(rows, [columns], [start], [step])– a list of numbers.=RANDARRAY([rows], [columns], [min], [max], [integer])– random numbers (volatile).
| Need | Formula |
|---|---|
| 1 to 10 | =SEQUENCE(10) |
| All dates of Ganeshotsav 2026 period | =SEQUENCE(12, 1, DATE(2026,9,14), 1) (format dd-mm-yyyy) |
| First day of each month, FY 2026-27 | =EDATE(DATE(2026,4,1), SEQUENCE(12,1,0)) |
| Row numbers for a filtered list | =SEQUENCE(ROWS(FILTER(tblMini, tblMini[City]="Pune"))) |
| 10 random delivery times 5–25 (practice data) | =RANDARRAY(10, 1, 5, 25, TRUE) |
About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on sequence and randarray.
Worked example – practice data generator. Ruhi creates 100 fictional orders for a test: Qty =RANDARRAY(100,1,1,5,TRUE), City =INDEX({"Pune";"Nashik";"Nagpur";"Kolhapur";"Solapur";"Sambhaji Nagar"}, RANDARRAY(100,1,1,6,TRUE)). She then Copy › Paste Values so the numbers stop changing.
Ravindra Bagale's Tip
RANDARRAY changes on every recalculation – many students create practice data and the next day find all the numbers have changed. Right after creating random data, do Paste Values. When creating a date list with SEQUENCE, don't forget to apply a date format to the cells.
Ravindra Bagale's Tip – मराठी
RANDARRAY प्रत्येक recalculation ला बदलतो – बरेच students practice data बनवतात आणि दुसऱ्या दिवशी सगळे आकडे बदललेले दिसतात. Random data बनवल्यावर लगेच Paste Values करा. SEQUENCE ने date list बनवताना cells ला date format लावायला विसरू नका.
Ravindra Bagale's Tip – हिंदी
RANDARRAY हर recalculation पर बदल जाता है – बहुत से students practice data बनाते हैं और अगले दिन सारे आँकड़े बदले हुए मिलते हैं. Random data बनाते ही Paste Values करो. SEQUENCE से date list बनाते समय cells पर date format लगाना मत भूलना.
Practice task
Create a calendar of all dates in November 2026 with SEQUENCE and mark weekends. Generate 50 random fictional orders and freeze them with Paste Values.
Loading practice tasks...
- 1 to 10 (spill) (Easy): Make numbers 1 to 10.
- Row numbers for members (spill) (Easy): Make 1 to 4.
- Start at 101 (spill) (Easy): Make 5 numbers starting at 101.
- Steps of 5 (spill) (Easy): Make 6 numbers: 0, 5, 10 ...
- A grid (spill) (Medium): Make 3 rows and 4 columns of numbers.
- Member codes (spill) (Medium): Make "GYM-1" to "GYM-4".
- Fee for 1 to 12 months (spill) (Medium): Fee in D2 × months 1 to 12.
- Next 7 days (spill) (Medium): 7 dates starting from C2.
- Monthly bill dates (spill) (Hard): Make 6 monthly dates starting from C3 (C3, then 1 month later, up to 5 months later).
- Total of 1 to 100 (Hard): Add the numbers 1 to 100.
- Year fee for Premium (Hard): Make 12 copies of D3 with SEQUENCE (step 0) and add them.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
