Expense consolidation template
By the Analistable team · Updated · 2 min read
The workbook has a Claims sheet for every employee's expense lines, a Limits sheet with per-line limits by category, and a Summary. Paste claim lines with employee and month, set your limits, and formulas compute gross amounts, flag lines over policy and total spend, line counts and breaches by category.
Part of our guide: How to consolidate data in Excel
What the template does
Expense claims usually arrive as one form per employee. Approvers need the opposite: every line in one table so they can total by category and spot policy breaches. This template is that table, plus a limits lookup and a category summary. It follows the process in combining monthly expense claims and the general method in the consolidating data guide.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Claims | Employee, Claim month, Date, Category, Net, VAT | Gross, Limit, Check |
| Limits | Category, Per-line limit (gross) | — |
| Summary | Category names | Gross total, Lines, Over-limit lines |
How to fill it in
- Copy each employee's claim lines into Claims, adding their name and the claim month on every row. Keep one row per receipt.
- Set a per-line gross limit for each category in Limits. Categories must be spelled the same as in Claims.
- Fill the Gross, Limit and Check formulas down to the last line.
- Filter Check for Over limit and No limit set; the second means a category is missing from Limits.
How the formulas work
The limit is looked up with INDEX/MATCH, which works in every Excel version and Google Sheets:
=IFERROR(INDEX(Limits!$B$2:$B$20,MATCH(D2,Limits!$A$2:$A$20,0)),0)
=IF(H2=0,"No limit set",IF(G2>H2,"Over limit","OK"))The Summary counts breaches per category with a two-condition COUNTIFS:
=COUNTIFS(Claims!$D$2:$D$100,A2,Claims!$I$2:$I$100,"Over limit")Worked example
Nine September lines from three employees. Tom's 84.00 meal and Ana's 252.00 hotel night exceed their limits of 60 and 200. Priya's 192.00 hotel night is close to the cap but within it, so it shows OK. Every other line passes, and the Total row confirms all nine lines were counted once.
| Category | Gross total | Lines | Over limit |
|---|---|---|---|
| Travel | 306 | 3 | 0 |
| Meals | 183.6 | 3 | 1 |
| Hotel | 444 | 2 | 1 |
| Software | 36 | 1 | 0 |
| Total | 969.6 | 9 | 2 |
Limits and when to move on
- Limits are per line. Daily or per-trip caps need a SUMIFS by employee and date before comparing.
- Copying lines in by hand is fine for a small team. For dozens of claim files a month, stack them with Power Query from a folder.
- To check claims against the corporate card statement, see reconciling expense claims with card statements, or load both files into Analistable and ask which card lines have no claim.
Frequently asked questions
- Why is the limit based on gross, not net?
- Most policies cap what the employee actually spent, which includes VAT. If yours caps net, change the Check formula to compare E2 with H2 instead of G2.
- Can I summarise by employee as well?
- Yes. Add employee names in a new column on Summary and use =SUMIFS(Claims!$G$2:$G$100,Claims!$A$2:$A$100,F2) next to each name.
- Does it work in Google Sheets?
- Yes. INDEX, MATCH, COUNTIFS, SUMIFS and IFERROR all work identically after you open the file in Google Sheets.