The short version: A working expense report template in Excel needs seven columns done: date, category, description, amount, currency, receipt confirmation, and approval status, built as an Excel Table with SUMIF subtotals and dropdown validation, not a plain grid you type into by hand. The template only fixes the maths. The policy sitting around it is what gets people reimbursed on time, and most businesses skip that part entirely.
Why most homemade expense templates fall apart within a month
I built my first expense template in 2011, back when my consultancy had two people on payroll and one client who wanted VAT-itemised receipts for every single coffee. I made it beautiful. Colour-coded categories, a company logo in the header, drop shadows on the cells. It lasted three weeks before my one employee started emailing me PDFs instead because “the spreadsheet was confusing.”
That taught me something I still tell clients now: the spreadsheet is not the problem you’re solving. Late reimbursements happen because a manager sits on the approval, or because nobody agreed what counts as a legitimate expense in the first place. A gorgeous template with broken formulas and no rules attached to it still ends in the same place, someone chasing a manager on Slack three weeks after a business trip, waiting for £340 that’s already sitting on their personal credit card statement.
Build the template, yes. But also decide, in writing, what needs a receipt, what the spending limit is per category, and who signs off within how many days. Without that, the world’s best spreadsheet just becomes a very tidy record of how slowly your business pays people back.
The columns you need (and the ones that are decoration)
Strip it back to what finance, HMRC, and the person gets used for reconciling accounts:
- Date of expense
- Employee name and department (if you’re beyond a handful of people)
- Category (Travel, Meals, Accommodation, Mileage, Office Supplies, Software, Other)
- Description or business purpose (the client name, the meeting, the reason)
- Merchant or vendor
- Payment method (personal card, company card, cash)
- Amount and currency
- VAT or tax amount if you’re reclaiming it
- Receipt attached (Yes/No)
- Approved by and date approved
That’s ten columns. I’ve seen templates with twenty-six, including a “mood at time of purchase” field someone thought was useful for expense fraud tracking. It wasn’t. Every extra column is one more thing someone abandons filling in, which means you get partial data, which is worse than no data because it looks complete.
Step by step: building the template
1. Set up the header block first
Above your table, put company name, employee name, department, reporting period (say, “1 to 31 January 2026”), and manager name. Merge cells across the top for this, keep it separate from the data table below. This is the bit finance glances at first when the report lands on their desk.
2. Turn your data range into a proper Excel Table
Type your ten column headers in a row, select that row plus a few blank rows below, then go to Insert and choose Table, or just press Ctrl+T. This matters more than people think. A real Table auto-expands as rows are added, keeps your formulas following the data, and lets you apply banded row colours in one click through Table Design. A plain range doesn’t do any of that, and it’s the single biggest reason homemade templates break when someone adds row 47.
3. Add dropdown menus so people can’t type nonsense
Select the Category column, go to Data, then Data Validation, choose List, and type your categories separated by commas: Travel,Meals,Accommodation,Mileage,Office Supplies,Software,Other. Do the same for Payment Method and for the Receipt Attached column, using Yes,No as the list. This is a two-minute job that stops you ending up with “taxi,” “Taxi,” and “TAXI FARE” as three different categories in your pivot table later.
4. Build the formulas that matter
At the bottom of your table, add a Total row (Table Design has a Total Row toggle, or just type SUM manually). For category subtotals, use a formula like this in a separate summary area:
=SUMIF(Table1[Category],”Travel”,Table1[Amount])
That pulls every row tagged “Travel” and adds up the amount column against it. Do one of these per category and you’ve got a spend breakdown without touching a pivot table. If you want VAT reclaimed calculated automatically, add:
=SUM(Table1[VAT Amount])
next to your total, so finance can see the reclaimable figure without doing it by hand.
5. Flag the problems before finance has to find them
Select the Amount column, go to Home, then Conditional Formatting, then New Rule, choose “Format cells that contain,” set it to greater than a number like 100, and format it red. Do a second rule flagging any row where Receipt Attached equals “No” and the amount is over, say, £25. This means the approver sees the risky rows in colour instead of reading every line of a 40-row report. It took me an embarrassingly long time in my own business to realise this saved more approval time than anything else in the sheet.
6. Lock the formula cells, leave the entry cells open
Select the whole sheet, open Format Cells, go to Protection, and untick “Locked” for the columns people fill in (date, category, description, amount). Leave everything else locked. Then go to Review, Protect Sheet, and tick the box. Now nobody can accidentally delete your SUMIF formula while filling in row 22 at 11pm before a deadline, which happens more often than you’d expect.
Mileage, per diems, and the bit that quietly breaks most templates
Mileage is where I see the most mess. HMRC’s rate has sat at 45p per mile for the first 10,000 business miles since 2011, dropping to 25p after that, for a personal car. Put a small reference table on a second tab with those rates, then use a formula in your mileage row like:
=IF(MileageThisYear<=10000,Miles*0.45,Miles*0.25)
so nobody has to remember the threshold themselves. If you’re reimbursing in the US, the IRS standard mileage rate moves almost every year, so don’t hardcode last year’s number into a formula you’ll reuse in 2027. Put the rate on the reference tab, not buried in a formula, so updating it takes ten seconds instead of a rebuild.
Multi-currency is the other thing that quietly wrecks these templates. If someone submits a hotel bill in euros, add a Currency column and a separate GBP Equivalent column using a fixed conversion rate you update monthly on your reference tab, rather than trying to build a live currency formula into Excel. Live rates in Excel through Stocks data types exist, but they’re fiddly and most small businesses don’t need that complexity for four international trips a year.
A worked example with real numbers
Say an employee travels to Manchester for a client pitch. Their report has three lines: a taxi at £24.50 with a receipt, a hotel at £142 with a receipt, and mileage of 68 miles at 45p a mile, which is £30.60, no receipt needed for mileage since it’s calculated, not claimed against a bill. Total: £197.10. Your Total row shows that instantly, your Travel subtotal (SUMIF) shows £197.10 too since it’s all one category, and your conditional formatting flags the hotel line in red because it’s over your £100 threshold, prompting the approver to check the itemised bill rather than rubber-stamping it. That’s the whole system working in one small example: entry, categorisation, calculation, flag, approval.
When Excel stops being the right tool for this job
I’ll say the thing most posts on this topic won’t. Excel is fine, a solid choice, for a business with under about 15 to 20 people submitting expenses, or low monthly volume even with more staff. Past that, you start hitting real friction: version control chaos when six people edit the same file, no audit trail for who changed a figure after approval, and no automatic sync into your accounting software. At that point tools like Expensify, Pleo, or the expense module inside Xero start paying for themselves in the hours they save your finance person, who’s currently manually re-entering every Excel report into the ledger.
Don’t rebuild the Excel wheel forever out of habit. I’ve seen businesses with 60 staff still running expenses through a shared spreadsheet because nobody wanted to pay £5 a user a month for a proper tool, while their bookkeeper burned four hours a week just chasing missing receipts and reformatting broken files. The maths on that trade doesn’t hold up past a certain headcount.
Where AI helps with this now
The useful shift in the last couple of years is receipt scanning. Phone cameras plus AI tools can now read a photographed receipt and pull the date, merchant, and amount straight into a spreadsheet or app, cutting the manual typing almost entirely. I’ve covered this in how to use AI in everyday life, and it’s one of the areas where small businesses are seeing real time saved rather than hype, which I’ve also written about in how small businesses use AI in 2026. If you’re still typing every receipt line by hand into your Excel template, that’s the single fastest thing to change this year.
Getting the totals into your actual books
Your expense report shouldn’t live in isolation. The category totals from your SUMIF formulas should feed straight into your monthly expense line when you’re building out your accounts, the same way I’ve walked through in how you build an income statement using a spreadsheet. If your expense template and your income statement are two completely disconnected files, you’ll end up reconciling them by hand every month end, which is exactly the kind of duplicate admin a decent template is supposed to remove.
Who should be running this process
In a business under ten people, the founder usually approves expenses themselves, which is fine until it’s the bottleneck. Past that, this is a perfect task to hand to a bookkeeper or a virtual assistant rather than doing it yourself at 9pm. I’ve written honestly about what that role can and can’t cover in employee virtual assistants, and expense processing, chasing receipts, checking against policy, entering approved totals into your books, sits right in the middle of what a good VA handles well. It’s repetitive, rules-based, and it doesn’t need you.
Frequently asked questions
Do I need a separate tab for policy, or can it go on the same sheet?
Put your expense policy summary (spending limits, receipt rules, approval timeframe) on its own tab in the same workbook. Keeping it in the same file means nobody claims they never saw it, and it sits one click away from the report itself.
What’s the fastest way to stop people submitting expenses without receipts?
Build a conditional formatting rule that flags any row over a set amount, say £25, where the Receipt Attached column says “No,” and make your approval policy explicit that flagged rows without a receipt get rejected, not queried. The formatting does the spotting. The policy does the enforcing.
Should mileage and cash expenses go in the same table as card purchases?
Yes, keep everything in one table with a Category column rather than separate sheets for mileage versus purchases
