How to combine monthly expense claims into one spreadsheet

Why one ledger beats twenty claim forms

Most teams collect expenses as one workbook per employee per month. That suits the claimant, but approvers and finance need the opposite view: every line in one table, so they can see who spent what, on which category, and whether anything breaks policy. Opening twenty files to copy totals into a summary loses the line detail that makes checking possible.

Stacking the claims into one long table keeps every receipt line and adds two columns that the individual forms leave implicit: Employee and Claim month. Everything else — totals, approvals, policy checks, the card reconciliation — runs off that single table. This is the per-person version of the general approach in the consolidating data in Excel guide.

Step 1: fix the claim template

Stacking only works cleanly when every file has the same header row. If you control the form, lock it down before the next cycle:

• One header row, starting in A1, with no merged cells or title block above it.
• Columns: Date, Category, Description, Net, VAT, Gross, Payment method (personal or company card), Receipt ref.
• Category as a drop-down (Data Validation list) so you get "Travel" rather than "travel", "Trains" and "Rail fare".
• Employee name or ID in a fixed cell, or better, as a column on every row.
• Format the lines as an Excel table (Ctrl+T) so new rows are included automatically.

Old forms with a header block (name, month, manager) above the lines can still be combined: in Power Query, remove the top rows and promote the next row to headers, then pull the name from the file name instead.

Step 2: stack the files

Put the month's claim files in one folder, named consistently, for example 2026-09 Priya Shah.xlsx. Then use whichever method suits your Excel version:

1. Power Query (Excel 2016 and later, Windows; recent Mac builds also support it): Data → Get Data → From File → From Folder, pick the folder, then Combine & Transform. Power Query adds a Source.Name column holding the file name, which you can split into claim month and employee.
2. VSTACK (Microsoft 365): if each claim is a sheet in one workbook, =VSTACK('Priya'!A2:H40, 'Tom'!A2:H40) stacks them. You will need a separate formula or a helper column to label each block with the employee.
3. Copy and paste: fine for three or four claims, but add the Employee column as you paste or the lines lose their owner.

The Power Query folder method is the one to use for a recurring monthly job: next month you drop new files into the folder and click Refresh.

Worked example: September claims

Three claims stacked into one ledger, with the employee taken from the file name:

A pivot with Employee in rows and Gross as the value gives the totals to approve: Priya £259.00, Tom £102.50, Aisha £93.00, £454.50 in all. Filtering Paid by = Personal gives the reimbursement due (£262.50); the £192.00 on company cards is not repaid but still needs a receipt.

Policy checks on the combined table

Once every line sits in one table, policy rules become filters or formulas rather than a manual read of each form. Common checks:

• Over-limit meals: add a column =AND([@Category]="Meals", [@Gross]>40) and filter to TRUE. In the example, Tom's £60.00 and Aisha's £45.00 lines would be flagged against a £40 limit.
• Duplicates: a COUNTIFS on Employee, Date and Gross greater than 1 catches the same receipt claimed twice, including across months if you keep the history in the ledger.
• Missing receipts: filter Receipt ref to blanks.
• Weekend or out-of-period dates: compare Date to the claim month taken from the file name.

Company-card lines then need matching to the card statement; that is a separate join, covered step by step in reconciling expense claims with card statements.

Doing it in SQL or without building the query

If the claims arrive as CSV exports from an expenses app, the same stack is one statement in any SQL engine. In DuckDB, reading a glob of files and keeping the file name looks like this:

SELECT
  regexp_extract(filename, '(\d{4}-\d{2}) ([^/]+)\.csv', 2) AS employee,
  category,
  SUM(gross) AS gross
FROM read_csv_auto('claims/2026-09 *.csv', filename = true)
GROUP BY ALL
ORDER BY employee, category;

Analistable does this kind of stack in the browser: you drop the claim files in, they are loaded into DuckDB on your machine rather than uploaded, and you can ask "total personal spend by employee for September" or "meals over £40" in plain English. It reads your files and answers questions; it does not edit or write back to the claim forms.

Keeping it tidy month to month

• Keep one folder per month, or one folder with the month in every file name, and never edit the combined output by hand — fix the source claim and refresh.
• Store approved ledgers as values (copy, paste values) at month end so later template changes do not rewrite history.
• If a new category appears, add it to the drop-down list rather than letting free text creep back in.
• Hide or remove personal details you don't need (home addresses, bank details) before sharing the combined ledger beyond finance.

Frequently asked questions

How do I show which employee each line came from?

Add an Employee column to the template, or derive it from the file name. Power Query's From Folder keeps a Source.Name column with the file name, which you can split on the space or delimiter you used when naming the files.

Some claim forms have a header block above the lines. Can I still combine them?

Yes. In Power Query's sample-file transform, remove the top rows down to the real header and use Use First Row as Headers. Every file is then processed the same way, as long as the header block is the same height on each form.

Should the combined ledger include company-card spend?

Yes, but keep a Paid by column. Company-card lines are not reimbursed, yet they still need receipts, policy checks and matching to the card statement, so they belong in the same table.

Can I combine claims from different months in one table?

Yes, and it is useful: keep a Claim month column and the duplicate check can catch a receipt that was claimed in August and again in September.

Related guides

• How to reconcile expense claims with card statements
• How to combine files from a folder with Power Query
• How to combine project status trackers into one spreadsheet
• How to combine property rent rolls into one portfolio rent roll
• How to merge rows in Excel without losing data