Tracking shared expenses in a spreadsheet, and exporting to Excel

Plenty of groups track shared expenses in Google Sheets or Excel, and for a lot of them it works fine. The trouble starts when you use the wrong layout. “Amount” and “Paid by” columns are enough to list expenses, but they can’t tell you who owes whom once one bill has two payers or a dinner was for only some of the group.

Here is a layout that does, where it stops working, and why you should export the ledger to Excel at the end of a trip or a lease, whatever you used to keep it.

When a spreadsheet is enough

A shared spreadsheet is a perfectly good tool when:

• the group is small (two to four people);
• everyone pays in one currency;
• most expenses are split equally between everyone;
• one person is happy to own the sheet and fix it when someone breaks a formula.

Two flatmates sharing groceries, a couple tracking joint costs, a weekend away with friends: a sheet does the job.

A spreadsheet layout that handles real expenses

The trick is one “paid” column and one “share” column per person. A single “Paid by” column can’t express two payers, and a single “Split” column can’t express “only Ben and Cleo”. Per-person columns can express both.

If you’d rather not build it, the free spreadsheet template has the formulas ready. It keeps one payer per row, so a bill two people paid goes in as two rows.

Example: Ana, Ben and Cleo rent a cabin for the weekend.

Three rules keep it honest:

1. Each row’s paid columns add up to the amount. The cabin went on two cards, €200 and €100, so both are on the row. Add a check column, =C2-SUM(D2:F2), that should always be zero.
2. Each row’s share columns add up to the amount too. Same check, =C2-SUM(G2:I2). This catches the cent that rounding loses.
3. Type the shares as numbers, not =C3/3. €84.50 doesn’t divide evenly by three: in cents it’s 8,450 = 2,817 + 2,817 + 2,816. Decide who takes the extra cent (here Ana and Ben pay €28.17, Cleo €28.16) and write the numbers in. A formula that rounds each cell on its own will leave the sheet a cent off, and someone will notice.

The balance for each person is their total paid minus their total share:

The balances add to zero, which is your final check. Ben and Cleo both pay Ana, and that’s it:

Where the spreadsheet breaks

The layout above is correct. The problem is keeping it correct for weeks, with several people editing it from their phones.

• Several currencies. You need a currency column, a rate column and a converted-amount column, and everyone has to agree which rate goes in: today’s, or the one on the card statement. Mixing the two is how a €3 discrepancy appears that nobody can find.
• More people. Every new member adds two columns. At eight people the sheet is 20 columns wide and unreadable on a phone, which is where expenses actually get entered.
• Someone joins or leaves partway. Columns for people who weren’t there for half the rows, and blank cells that might mean “not included” or might mean “forgot”.
• Who’s-owed formulas. Balances are easy. Turning balances into the fewest transfers isn’t a formula, it’s a small algorithm, and most sheets give up and have everyone pay everyone. See settling up in the fewest transfers for how it works.
• Edits after the fact. Someone changes Saturday’s dinner from €96 to €69. There’s no record of who changed it or why, unless you’ve turned on version history and someone thinks to check.
• Offline. A shared sheet on a mountain with no signal is a note in someone’s phone that gets typed in later, or doesn’t.

None of these is fatal on its own. Together, they’re why the sheet tends to be abandoned in week two of a long trip.

Why you should export at the end

Whatever you use during the trip or the lease, export a complete copy when it ends. A month later, someone will ask about a number. A deposit gets disputed. A flatmate needs the figures for their own records. An app gets deleted. “I think I paid for that” has an answer only if the record still exists.

A good export has, for every expense:

• date, description, amount and currency, and the converted amount if it was in another currency;
• who paid, and how much each payer put in;
• who it was for, and each person’s share;

and at the end, each person’s balance and the payments that settled them.

That’s the same information as the sheet above, which is the point. If you’ve kept the ledger in an app, the export gives you the spreadsheet without having to maintain one. In kisir, the export to CSV or Excel is free, and it contains every expense, who paid, who it was for, and the settlement.

Google Sheets or an app?

Use a sheet when the group is small, the currency is one, and someone enjoys keeping it tidy. Use an app when expenses are entered on phones by several people, in more than one currency, over more than a few days, and export to Excel at the end so you still have the spreadsheet.