Most of the cost of a trip is a small number multiplied by a bigger one: nights times the price per night, people times the price per ticket. Excel is very good at exactly this, which means you can change one number — five nights instead of seven — and see the true effect immediately.
Set up the cost list
In row 1, type these headings: Item, Cost each, How many, Total.
Four columns cover every kind of cost, from one taxi to fourteen nights of hotel.
Down column A, list the real costs: Flights, Hotel per night, Meals per day, Train tickets, Insurance, Spending money.
Write the unit into the name. 'Hotel per night' reminds you that the cost each is one night, not the whole stay.
Fill in Cost each and How many for each row.
Guesses are fine to begin with. The point of the sheet is that you can correct them in seconds.
Multiply instead of guessing
Click D2, type =, click the Cost each box, type *, click the How many box, then press Enter.
The star is Excel's multiply sign. You are telling it 'this one times that one', not typing an answer.
Click D2 again and drag the small square at its bottom-right corner down the rest of your rows.
That square is the fill handle. It copies the formula and shifts it to each row automatically.
Two rows under the list, use AutoSum (Σ) on the Total column.
That is the honest headline number for the whole trip.
Split it per person
Under the grand total, type People in column A and the number of travellers beside it.
Keeping the input in its own labelled box means you never have to hunt inside a formula to change it.
Under that, type Each pays, then in the box beside it type =, click the grand total, type /, click the People box, and press Enter.
The slash divides. Now everyone's share updates the moment any cost changes.
Compare two options
Right-click the sheet tab at the bottom, choose Move or Copy, tick Create a copy, and click OK.
You now have an identical second sheet — the fastest honest comparison there is.
Double-click each tab and rename them, such as Lisbon and Edinburgh.
Named tabs stop you editing the wrong plan, which is easier to do than it sounds.
Change the prices on the second sheet and flick between the tabs.
Two totals, same layout, no arithmetic. This is the whole reason to build it in Excel.
Check yourself
If you can say yes to each of these, you have finished.
- Each row multiplies a cost by a quantity.
- One grand total covers the whole trip.
- Changing the number of people updates each share.
- I have two named sheets I can compare.