How to Consolidate Weekly Data into One Table in Excel Using Formulas (5 Quick Steps)
Watch the Tutorial
Introduction
Consolidate weekly data in Excel often gets messy — especially when every week is stored in separate columns across an entire year. Manually restructuring this data takes time, and many users immediately turn to Power Query or VBA for automation.
But here’s the good news: You can consolidate weekly data into a single table using formulas only — no coding, no add-ins. In this guide, I’ll show you step by step how to do it with modern Excel functions like SEQUENCE, CHOOSECOLS, and TOCOL
Why Consolidate Weekly Data?
When project or effort data is spread across columns (Week 1, Week 2, Week 3 … Week 52), it becomes hard to analyze. Pivot tables, charts, or dashboards require a tabular format with:
Project Name
Week
Resource
Hours
By restructuring the data, you unlock:
Easier reporting
Cleaner pivot tables
Faster analysis
Quick Steps to Consolidate Weekly Data in Excel
- Find column pattern → Weeks are in columns 2, 5, 8… (difference of 3). Formula:
=SEQUENCE(52,1,2,3)
2. Extract week columns → Use CHOOSECOLS with SEQUENCE.
Formula: =CHOOSECOLS(Data, SEQUENCE(52,1,2,3))
3. Flatten weeks into one column → Use TOCOL.
Formula: =TOCOL(CHOOSECOLS(Data, SEQUENCE(52,1,2,3)))
4. Get Resource/Hours → Adjust start positions.
Formula (Resource): =TOCOL(CHOOSECOLS(Data, SEQUENCE(52,1,3,3)))
Formula (Hours): =TOCOL(CHOOSECOLS(Data, SEQUENCE(52,1,4,3)))
5. Bring Project Name → Single column.
Formula: =TOCOL(CHOOSECOLS(Data,1))
Conclusion
Consolidating weekly effort data in Excel no longer requires VBA or Power Query. With modern functions like SEQUENCE, CHOOSECOLS, and TOCOL, you can build a dynamic and automated solution directly in your workbook.
If you want to master such advanced Excel formulas and take your data analysis skills to the next level, check out my Data Academy .
