Should I combine timesheets in grid or long format?

Long format: one row per person, date and project. Grids from different weeks can't be totalled without extra formulas, while a long table works directly with pivots, SUMIFS and SQL.

How do I keep track of who submitted each timesheet?

Put the person's name or ID in the file name or in a column on every row. Power Query's Source.Name column carries the file name through to the combined table.

Can I combine timesheets from Google Sheets?

Yes. In Google Sheets, ={Sheet1!A2:F; Sheet2!A2:F} or =VSTACK(Sheet1!A2:F, Sheet2!A2:F), stacks ranges vertically, and IMPORTRANGE brings in other spreadsheets. For many people's files, a script or exporting to CSV is easier to maintain.

What about leave and bank holidays?

Record them as rows with a Type of Leave, rather than leaving the day blank. Payroll counts them as paid hours while project reports can filter them out.

Why are my combined hours far too small?

Hours were probably entered as clock times (7:30) and Excel stores those as fractions of a day. Multiply by 24 to convert to decimal hours before summing.

How to combine weekly timesheets into one spreadsheet

By the Analistable team · Updated 2026-10-10 · 5 min read

Unpivot each timesheet so there is one row per person, date and project, add the person and week from the file name, then append every week with Power Query From Folder. From that single long table, SUMIFS or a pivot gives payroll hours per person per week and billable hours per project from the same numbers.

Part of our guide: How to consolidate data in Excel

The problem with grid-shaped timesheets

Most weekly timesheets are grids: projects down the side, Monday to Sunday across the top, a total in the corner. That layout is easy to fill in, but stacking twenty of them gives you twenty grids, not a table you can total. Before combining, decide on the shape you want at the end:

Target shape: one row per person, day and project
Person	Week start	Date	Project	Hours	Type
Sam Okoro	07/09/2026	07/09/2026	Website rebuild	7.5	Billable
Sam Okoro	07/09/2026	08/09/2026	Internal admin	2	Non-billable

This long format is what payroll, project costing and the timesheet-to-payroll reconciliation all need. It is also the shape the consolidating data in Excel guide recommends for any recurring roll-up.

Step 1: unpivot the day columns

In Power Query, the grid becomes long in one step. Load one timesheet, select the Project column, then Transform → Unpivot Other Columns. The seven day columns turn into two: Attribute (the day name or date) and Value (the hours). Rename them Date and Hours and remove the Total row and column first so they don't get counted twice.

If the day headers are names (Mon, Tue…) rather than dates, you need the week start date to turn them into real dates. Take it from a cell on the sheet or from the file name, then add 0–6 days depending on the day name.

Step 2: append every person and every week

Save each timesheet into one folder with a consistent name, for example 2026-W37 Sam Okoro.xlsx.
Data → Get Data → From File → From Folder, then Combine & Transform Data.
Apply the unpivot in the sample-file transform so every file is reshaped the same way.
Split Source.Name into Week and Person columns.
Filter out rows where Hours is null or zero, then Close & Load.

Each new week, drop the files into the folder and refresh. The Power Query folder walk-through covers the sample-file mechanics in more detail. If timesheets come from a time-tracking app as CSV exports, they are usually long already and only need appending.

Worked example: one week, three people

Combined timesheet rows, week starting 07/09/2026 (summarised by project)
Person	Project	Type	Hours
Sam Okoro	Website rebuild	Billable	30
Sam Okoro	Internal admin	Non-billable	7.5
Lena Fischer	Website rebuild	Billable	18
Lena Fischer	CRM migration	Billable	16
Lena Fischer	Holiday	Leave	7.5
Dev Patel	CRM migration	Billable	22.5
Dev Patel	Internal admin	Non-billable	6

The same rows answer both questions. For payroll, total by person:

Hours by person (payroll view)
Person	Worked	Leave	Total paid hours
Sam Okoro	37.5	0	37.5
Lena Fischer	34	7.5	41.5
Dev Patel	28.5	0	28.5

For projects, total by project: Website rebuild 48.0 hours, CRM migration 38.5 hours, Internal admin 13.5 hours, Holiday 7.5. Both views sum to 107.5 hours, which is the check that nothing was dropped. Lena's 41.5 paid hours stand out against a 37.5-hour week and are worth a question before payroll runs.

Checks before you hand it to payroll

Missing timesheets: compare the list of people in the combined table with the staff list; anyone absent hasn't submitted.
Double submissions: COUNTIFS on Person and Week greater than the number of project lines suggests two files for one week.
Overtime: a pivot of hours by person and week, filtered to more than the contracted hours.
Unknown projects: projects that don't exist in the project list are usually typos; fix them in the source file.

To go further and tie hours to project codes, tickets or invoices, see matching timesheet hours to projects.

The same roll-up in SQL

If the timesheets are already long CSV exports, one query stacks and summarises them. In DuckDB:

SELECT person, project, SUM(hours) AS hours
FROM read_csv_auto('timesheets/2026-W37 *.csv')
GROUP BY person, project
ORDER BY person, hours DESC;

Analistable runs this kind of query for you: load the weekly files (they stay in your browser), and ask "hours by person this week" or "which project had the most billable hours in September". It reads the timesheets but does not change them, so the files remain the record.

Common problems when stacking timesheets

Different week layouts: one team starts the week on Monday, another on Sunday. Convert day names to real dates using each file's own week start, never a single global one.
Decimal versus clock time: 7:30 entered as a time is 0.3125 of a day in Excel, while 7.5 is hours. Multiply time values by 24 before adding them to decimal hours, or the totals are wildly off.
Merged project cells: a project name merged across two rows leaves the second row blank after combining. Use Fill Down in Power Query on the Project column.
Hidden total rows: a Total row inside the grid gets unpivoted as if it were a project. Filter it out by name before appending.
Edits after submission: if someone resubmits a corrected week, keep only the newest file for that person and week, or the hours double.

Most of these are cheaper to fix in the template than in the combined table. A locked template with dates already filled in for the week, a project drop-down and hours as plain numbers removes nearly all of them.

Frequently asked questions

Should I combine timesheets in grid or long format?: Long format: one row per person, date and project. Grids from different weeks can't be totalled without extra formulas, while a long table works directly with pivots, SUMIFS and SQL.
How do I keep track of who submitted each timesheet?: Put the person's name or ID in the file name or in a column on every row. Power Query's Source.Name column carries the file name through to the combined table.
Can I combine timesheets from Google Sheets?: Yes. In Google Sheets, ={Sheet1!A2:F; Sheet2!A2:F} or =VSTACK(Sheet1!A2:F, Sheet2!A2:F), stacks ranges vertically, and IMPORTRANGE brings in other spreadsheets. For many people's files, a script or exporting to CSV is easier to maintain.
What about leave and bank holidays?: Record them as rows with a Type of Leave, rather than leaving the day blank. Payroll counts them as paid hours while project reports can filter them out.
Why are my combined hours far too small?: Hours were probably entered as clock times (7:30) and Excel stores those as fractions of a day. Multiply by 24 to convert to decimal hours before summing.