Excel Hack: Create Collated & Uncollated Lists to Build Tables (EASY Formula!)

Struggling to organize data in Excel? This collated/uncollated list trick will save you HOURS!

A collated list repeats numbers in cycles (like 1,2,3,1,2,3…), while an uncollated list groups identical numbers together (like 1,1,1,2,2,2…). Collated sequences are great for rotating tasks or patterns, while uncollated lists help organize data into batches—and with these formulas, you can generate both automatically!

Here are the formulas featured in my video.

Collated List
0 to 4
=MOD(SEQUENCE(20,1,0),5)+1

Collated List
1 to 5
=MOD(SEQUENCE(20,1,0),5)

Uncollated List
=ROUNDUP(SEQUENCE(20)/5,0)

#excel #tips #shorts #fyp