How to Split Shared Expenses in Excel Between Friends

To split expenses in Excel, you need three numbers per person: what they paid, what their fair share is, and the difference. A simple log of who paid what, plus SUMIF and one subtraction, gives you all three, and tells you exactly who owes whom at the end of a trip, a shared flat or a group gift. This guide builds the sheet step by step, then handles uneven splits and odd cents.

The names and amounts below are example figures for a weekend trip.

Step 1: Log every expense#

Make one row for each expense, with who paid it, what it was and the amount:

Type each name exactly the same way every time. "Ben" and "Ben " (with a trailing space) look identical but count as different people. A drop-down list for the Paid by column prevents that. Format the log as a table (Windows: Ctrl+T; Mac: ⌘T) so the totals grow as you add rows.

Step 2: Total what each person paid with SUMIF#

SUMIF adds the amounts in one column where another column matches a name. Put the names in a summary area, E2:E4, and in F2 type:

=SUMIF($A$2:$A$8,E2,$C$2:$C$8)

Fill it down to F4 (Windows: Ctrl+D; Mac: ⌘D). Ana paid 484.60, Ben 129.80 and Cai 192.00. SUMIF reads as: check A2:A8, find the name in E2, and add the matching amounts from C2:C8. The SUMIF function guide has the full syntax, and the article on SUMIF with examples shows more criteria.

The $ signs matter. They lock the log ranges so they stay on rows 2 to 8 as you fill down, while E2 moves to E3 and E4. Without them, the ranges would slide down and miss rows. The article on the dollar sign in Excel formulas explains this in detail.

Step 3: Work out the fair share#

For an even split, the fair share is the grand total divided by the number of people. In G2:

=SUM($C$2:$C$8)/COUNTA($E$2:$E$4)

The total is 806.40 and there are 3 people, so each share is 268.80. COUNTA counts the names, so adding a fourth friend to the summary updates the share by itself. Fill the formula down, or put it in a single cell and point to it with an absolute reference.

Step 4: Calculate each balance#

The balance is what each person paid minus their fair share. In H2, type =F2-G2 and fill down:

A positive balance means the group owes that person money. A negative balance means that person owes money to the group. Check that the balances add up to zero: 215.80 − 139.00 − 76.80 = 0. If they don't, a name is misspelled or an amount is missing from a SUMIF.

You can do the whole thing for one person in a single formula, as in the exercise: =SUMIF(A2:A8,"Ben",C2:C8)-SUM(C2:C8)/3 returns -139.

Step 5: Settle up: who pays whom?#

People with negative balances pay people with positive balances. Here it is simple: only Ana is owed money, so Ben pays Ana 139.00 and Cai pays Ana 76.80. That's two transfers and everyone is even.

With more people, start with the person who owes the most and have them pay the person owed the most. Repeat until every balance is zero. To show a plain-English line next to each balance, use IF:

=IF(H2<0,E2&" owes "&TEXT(-H2,"0.00"),E2&" is owed "&TEXT(H2,"0.00"))

For Ben, this shows "Ben owes 139.00".

How to split some items unevenly#

When not everyone shares every item, mark who joined each one and split that row only among them. Copy the log to a new sheet and add a column per person, D:F for Ana, Ben and Cai, with an x for each person who shared the item. Say Ana skipped the museum, so row 7 has x only for Ben and Cai.

Each person's share of one row is the amount divided by the number of x's, but only if they have an x. For Ana, in a share column next to the marks:

=IF(D2="x",$C2/COUNTIF($D2:$F2,"x"),0)

The $C2 and $D2:$F2 lock the columns but not the rows, so you can copy the formula across for Ben and Cai and down every row. Total each share column, then subtract from what each person paid as before. With the museum split only between Ben and Cai, the shares become Ana 254.80, Ben 275.80 and Cai 275.80, which still add up to 806.40.

You can practise relative and absolute references to get comfortable with mixed references like $C2.

Odd cents and other details#

Some totals don't divide evenly. A 100.00 bill split three ways is 33.333…, and three shares of 33.33 add up to 99.99. Round each share with =ROUND(total/3,2) and let one person cover the extra cent.

A few more tips:

• Different currencies. Add a rate column and convert every amount to one currency before you total.
• Running costs. For a shared flat, add a Month column and use SUMIFS to total by person and month. The expense tracker guide and bill tracker guide show how to log recurring costs.
• Personal budgets. Once you know what you owe, add it to your own monthly budget.

Practise it#

This sheet runs on two skills: conditional totals and locked references. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then practise absolute references with the dollar sign so your formulas fill down correctly. Then try the exercise on this page to work out what Ben owes.