Travel and vehicles

Splitting a multi-country trip budget by country (UK)

Use a simple pivot table to see what each country cost on a trip, in pounds, from a list of daily expenses. Steps, a worked example and a checklist.

Editorial illustration for Splitting a multi-country trip budget by country (UK)

The fastest way to learn what each country cost is a pivot table built on a plain list of expenses: one row per payment, with a country written on it. Once the list exists, the pivot takes a minute and rebuilds itself whenever you add rows.

Here is the approach with a made-up two-week rail trip through France, Italy and Switzerland, with every amount in pounds.

Start with a clean list, not a clever sheet

Create a table with the headings Date, Country, Category and Amount (£). Resist adding colour and merged cells; pivot tables dislike both. Each row is one payment: a coffee, a hotel night, a museum ticket. If a payment is shared with someone, enter only your share.

How do you build the pivot?

  1. Click anywhere inside the list.
  2. In Excel choose Insert, then PivotTable; in Google Sheets choose Insert, then Pivot table.
  3. Put Country in Rows.
  4. Put Amount (£) in Values and make sure it says Sum, not Count.
  5. Drag Category into Columns if you want lodging, food and transport side by side.

Menu names vary a little between versions, so look for the nearest match. If the numbers show as a count, the Amount column probably holds text rather than numbers.

What does a worked example look like?

CountryLodging (£)Food (£)Transport (£)Total (£)
France42031095825
Italy380290140810
Switzerland5103601301,000

France: 420 plus 310 plus 95 is 825. Italy: 380 plus 290 plus 140 is 810. Switzerland: 510 plus 360 plus 130 is 1,000. The trip comes to 2,635 pounds, and Switzerland alone is 1,000 of it, which is just under two fifths. That single comparison is the reason to bother.

Where should the rail pass go?

A pass that crosses all three borders has no natural country. Either give it its own Country entry called Multi-country and show it as a separate pivot row, or divide it by the number of days spent in each place. The first is honest and quick; the second is neat but needs a note in the sheet. Pick one before you start entering rows.

Can you do this with only a bank statement?

Partly. Card statements show merchant names and dates, and many merchants make the country obvious, but a taxi or a market stall rarely does. Download your statement for the trip dates, copy the rows in, and add the Country column by looking at your own travel dates: everything between the day you crossed into Italy and the day you left is Italy. Cash withdrawals need a note about where the cash was spent, or you will credit the wrong country.

What if the pivot shows two spellings of one country?

It happens often: France and france, or Switzerland and Swiss. The pivot treats each spelling as a separate country and splits your total in two. Fix it at the source with a drop-down list in the Country column, or use Find and Replace once on the whole column before you build the pivot. Also look for trailing spaces, which look invisible but count as a different value.

A final habit worth keeping is to refresh the pivot after any edit. In Excel it does not update by itself, so use the Refresh button; Google Sheets updates on its own.

Before you close the sheet

  • Fill any blank Country cells.
  • Look for duplicate rows, such as a card payment and the receipt you also typed in.
  • Note one thing that surprised you, so the next trip's budget starts with it.
  • Save a copy with the trip name and year in the file title.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports