If your company doesn't have an expense system, or you run a small team, sooner or later someone has to make the expense report form. It sounds simple until you start deciding which columns to include and how the totals should work. This guide builds a one-page expense report template from scratch.
1. What every expense report needs
Whoever approves the report should be able to see who spent what, when and why without digging through receipts. Split the sheet into a header, the line items and a totals section.
- Header: employee name, department, trip or period start and end dates, business purpose, date submitted
- Line items: one row per receipt
- Totals: totals by category, grand total, amount to reimburse
- Sign-off: employee, reviewer and approver, with dates
Keep the header in rows 1 to 4 and start the line items table in row 6, and the formulas below will work as written.
2. Line item columns
| Column | Name | Example | Why |
|---|---|---|---|
| A | No. | 1 | Matching rows to receipt photos |
| B | Date | 9/3/2026 | Checking it's within the trip |
| C | Category | Meals | Category totals |
| D | Merchant | Union Station Cafe | The store on the receipt |
| E | Description | Lunch with client | Why the money was spent |
| F | Paid with | Personal card | Working out reimbursement |
| G | Amount | 18.50 | Totals |
| H | Receipt | Card slip | What proof is attached |
Stick to five or six categories, such as Airfare, Lodging, Ground transport, Meals, Supplies and Other. Too many choices and every employee picks differently.
3. Filling line items from receipts
When there's a stack of receipts, don't type them one by one. Add the photos to the receipt tool to get the date, store and total in a table, then copy those three columns from the downloaded file into columns B (Date), D (Merchant) and G (Amount). Keeping the same numbering as the tool makes it easy to match rows to photos later.
Category, Description, Paid with and Receipt need human judgment, so fill those in after pasting, with the photos open beside the sheet.
4. Totals and reimbursement formulas
With line items in rows 6 to 40:
- Grand total:
=SUM(G6:G40) - Meals total:
=SUMIF($C$6:$C$40, "Meals", $G$6:$G$40) - Paid with a personal card:
=SUMIF($F$6:$F$40, "Personal card", $G$6:$G$40) - Amount to reimburse, adding personal card and cash:
=SUMIF($F$6:$F$40, "Personal card", $G$6:$G$40) + SUMIF($F$6:$F$40, "Cash", $G$6:$G$40)
Instead of typing category names inside formulas, list the categories in cells next to the totals and point the formulas at those cells. Renaming a category then won't break anything.
5. Three checks that prevent mistakes
Pick categories and payment methods from a list. Select the column, go to Data > Data Validation > List and enter the options. This stops "Meals" and "Meal" being totaled separately.
Highlight dates outside the trip. With the start date in C3 and end date in E3, select B6:B40 and add a conditional formatting rule using =OR($B6<$C$3, $B6>$E$3).
Catch receipts entered twice. In column I, =COUNTIFS($B$6:$B$40, B6, $G$6:$G$40, G6) counts rows with the same date and amount. Anything above 1 deserves a second look.
6. Checklist before you submit
- Does every row have a category, description and payment method?
- Does the grand total match the receipts?
- Do highlighted out-of-range dates have an explanation?
- Are receipt photos or originals ready in the same order as the rows?
- Are any meals or hotel nights above your company's limits?
Summary
A good expense report template comes down to one receipt per row, people only fill in what needs judgment, and lists and formulas catch the mistakes. Build it once, paste in receipt data each time, and an expense report becomes a ten-minute job.