Travel expense tracking in Google Sheets with receipt scanning

Travel is the hardest expense category to track well, and the reason is structural rather than behavioural.

Every other category is one transaction at a time. Travel is a bundle: a single trip produces a dozen or more transactions across several vendors, often in more than one currency, some paid personally and some on a company card, some attributable to a client and some not, generated over several days during which the person involved is tired and in an unfamiliar place.

A sheet that handles ordinary expenses will not hold that without modification. Here is a structure that does.

Two tabs, not one

The common mistake is a single flat list of travel expenses. It cannot answer the question people actually ask, which is "what did that trip cost?"

Tab 1 — Trips. One row per trip.

Tab 2 — Expenses. One row per transaction, each carrying a Trip ID.

The Trip ID is what makes everything else work. With it, a trip total is one SUMIFS; without it, it is a date-range guess that catches the wrong taxi.

Assign the Trip ID before booking

This is the highest-leverage habit in the system and it costs nothing.

The identifier has to exist before the first transaction, because the first transaction — usually a flight booked three weeks ahead — happens long before anyone is thinking about expense tracking. Trips that get their ID assigned on return are always missing their booking costs.

Create the Trips row when the trip is approved. Everything else attaches to it.

Currency: record two numbers, always

For any transaction outside your home currency, two figures exist and they do not match:

• The local amount, printed on the receipt
• The home amount, which is what the card issuer actually charged after conversion and any fee

Record both. The local amount ties to the receipt image, which is what an auditor or approver compares against. The home amount ties to the bank statement, which is what reconciliation needs and what the traveller is actually owed.

Keeping only one creates a specific, recurring argument. Reimburse the local amount converted at today's rate and the traveller is out of pocket by the spread. Reimburse the statement amount and the figure does not match the receipt, so the approver queries it.

Write the policy down — most teams reimburse the actual charged amount — and have both columns so either can be produced.

The practical method: fill the local amount at capture, and fill the home amount when the statement arrives. A formula with a fixed monthly rate is a reasonable interim for planning, but the statement figure is what gets paid.

The "paid by" column decides who is owed

This column does more work than its size suggests. Three values cover almost everything:

• Personal card — the traveller is out of pocket and must be reimbursed
• Company card — already paid by the business; recording it is for the books, not for repayment
• Prepaid/invoiced — flights and hotels booked centrally, often weeks earlier

Without this distinction, a trip total is not a reimbursement amount, and someone ends up manually separating the two at the point of payment. With it, the amount owed to a traveller is:

=SUMIFS(Expenses!AmountHome, Expenses!TripID, $A2, Expenses!PaidBy, "Personal card")

That formula is the reimbursement summary. It is also the thing that prevents the most common travel error: paying a traveller back for a hotel the company already paid for directly.

Split costs between people

One person pays for a dinner attended by three colleagues. One person takes the taxi that four people share.

Two viable approaches, and the wrong one is trying to split at the transaction level.

Approach A — whole cost to the payer, attendees recorded. The full amount sits on one row, with the attendee list in the attendees column. Simple, accurate for reimbursement, and the attendee record satisfies the substantiation most jurisdictions expect for meals. The downside is that per-person cost analysis requires reading the attendee column.

Approach B — split rows. The payer's row is divided into per-person rows, each tagged to its own traveller. More accurate for cost analysis, noticeably more work, and only worth it if you genuinely report per-head travel costs.

Most teams should use A. The attendee column is not optional either way — for shared meals it is usually the field that decides whether a claim is substantiated.

Per diems, if you use them

Some organisations reimburse a fixed daily amount for meals and incidentals rather than actual receipts. If you do, keep per diems structurally separate from receipted expenses: a per diem row with no receipt is correct and should not appear in any "missing receipt" check.

Add a value to the category column — "Per diem" — and exclude it from the receipt-completeness formula. Otherwise your control reports flag every per diem as an exception, and controls that cry wolf get ignored.

Note that the two approaches generally do not mix for the same cost on the same day. Claiming a per diem and a dinner receipt for the same evening is the kind of overlap that gets picked up.

The late submitter

Every team has one, and the structure should assume them rather than wish them away.

Two mechanisms help. The first is a status column on the Trips tab, which makes a trip in Draft status three weeks after return visible without anyone remembering to check. A filter on Status = Draft AND Return < TODAY()-14 is the entire chasing process.

The second is making capture independent of submission. If receipts have been scanned as they occurred, a late submission is a late click, not a reconstruction. The damage from late submission is not the delay — it is that the evidence has degraded in the interim, and that damage is avoided at capture rather than at submission.

Three checks before paying anything

1. Every expense row has a Trip ID. Orphan rows are costs that will never be rebilled or analysed.
2. Every non-per-diem row has a receipt link. =COUNTIFS(ReceiptURL,"",Category,"<>Per diem") should be zero.
3. Rebillable totals match what was invoiced. A trip marked rebillable with £1,840 of costs and £1,200 on the client invoice is a £640 question worth asking before the invoice is sent, not after.

That third check is where the system pays for itself for client-facing teams. Unbilled recoverable travel is one of the quietest forms of lost revenue, because nobody notices an absence.

Keeping capture realistic on the road

All of the above depends on receipts existing. On day three of a trip, in a different timezone, after a long day, the probability of anyone typing a transaction into a spreadsheet is low.

The realistic bar is a photograph taken at the moment of payment. Scan2Sheet reads the vendor, date, and amount off the image and lands the row in the shared sheet with the picture attached, which leaves the traveller to pick the Trip ID from a dropdown — a two-second action that is plausible when standing outside a restaurant, which is more than can be said for data entry.

The fields that still need a human are attendees and purpose, and those are precisely the ones that cannot be recovered later. Capture them in the same motion.

Setting it up

Build the Trips tab first, create one trip, and run a single journey through the whole system before rolling it out to anyone else. The friction points show up immediately — usually the currency columns and the paid-by values — and they are much easier to adjust before four people have adopted the template than after.