Split expenses spreadsheet template (with formulas)
Quick answer
A split expenses spreadsheet is a ledger with one row per purchase and one share column per person, plus a settle-up block. Each person's net is total paid, found with SUMIF, minus total share, and every net together adds to zero. Equal, percent and shares splits each need one short formula, given below.
Key points
- Each person's net is total paid (SUMIF on the Paid by column) minus total share. The nets of everyone add up to 0.00.
- Let the last person's share cell take the remainder, so every row adds up to its amount even when a cent will not divide.
- Percent is amount times percent. Shares is amount times a person's shares, divided by the sum of everyone's shares.
- In the six-row example, Omar is owed $22.25 and two payments ($14.00 and $8.25) clear every balance.
On this page
How to use this template
The sheet has two parts: a ledger with one row per purchase, and a settle-up block that turns it into who pays whom. The formulas fit this layout and work the same way in Excel and Google Sheets.
- Columns A to E are the ledger: date, what, paid by, amount and split rule.
- Columns F to H hold one share column per person, and column I is a row check.
- Row 1 holds each person’s shares over F to H, if you use them. Row 2 holds the headers, and purchases start in row 3.
- Columns K to O are the settle-up block.
Add a row for each purchase, with one spelling for each name because SUMIF compares text. Fill the share cells with the formula for the rule, from the formulas table. In I3, =ROUND(D3-SUM(F3:H3),2) must show 0.00.
The template
Ledger (columns A to E)
Printed, the # column is the row number in both tables, and the date and what share one cell.
| # | Date and what | Paid by | Amount | Split rule |
|---|---|---|---|---|
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 |
Shares (columns F to I, same rows)
| # | Person 1 | Person 2 | Person 3 | Row check |
|---|---|---|---|---|
| Shares (optional) | ||||
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 |
Settle-up (columns K to O)
| Person | Total paid | Total share | Net (paid minus share) | Pays or receives |
|---|---|---|---|---|
| All together | Must be 0.00 |
Formulas to copy
Each rule fills the three share cells of a row. Row 3 is shown, with the three people in F, G and H.
| Rule | Formula | Example |
|---|---|---|
| Equal, all 3 | F3 and G3: =ROUND($D3/3,2). H3: =$D3-F3-G3 |
$64.00 gives 21.33, 21.33, 21.34 |
| Equal, only 2 | F3: 0. G3: =ROUND($D3/2,2). H3: =$D3-F3-G3 |
$18.50 gives 0.00, 9.25, 9.25 |
| Percent | F3: =ROUND($D3*0.5,2). G3: =ROUND($D3*0.3,2). H3: =$D3-F3-G3 |
$60.00 at 50, 30, 20 gives 30.00, 18.00, 12.00 |
| Shares | F3: =ROUND($D3*F$1/SUM($F$1:$H$1),2), copied to G3. H3: =$D3-F3-G3 |
$96.00 at 2, 1, 1 gives 48.00, 24.00, 24.00 |
| Exact amounts | Type each amount and let the row check show what is left | $35.00 as 10, 15, 10 gives 0.00 |
For the settle-up block, with the first person’s name in K3:
- Total paid in L3:
=SUMIF($C$3:$C$200,K3,$D$3:$D$200). SUMIF takes the range to look in, the value to match and the range to add up. The match can be a cell reference such as K3, so the formula copies down for every person. - Total share in M3:
=SUM(F3:F200). Use column G for the second person and H for the third. - Net in N3:
=ROUND(L3-M3,2). Positive means the person is owed money, and negative means they owe it. - Check cell under the nets:
=ROUND(SUM(N3:N5),2). It must read 0.00.
SUMIF takes one condition. For a second one, such as a currency, use SUMIFS.
A worked example with six rows
Say Mia, Noor and Omar keep one sheet. Purchase 5 splits by shares, with 2, 1 and 1 typed in F1 to H1.
| # | What (paid by) | Amount | Split rule | Shares for Mia, Noor, Omar |
|---|---|---|---|---|
| 1 | Groceries (Mia) | $64.00 | Equal, all 3 | 21.33, 21.33, 21.34 |
| 2 | Internet (Noor) | $60.00 | Percent, 50, 30, 20 | 30.00, 18.00, 12.00 |
| 3 | Cleaning supplies (Omar) | $18.50 | Equal, Noor and Omar | 0.00, 9.25, 9.25 |
| 4 | Pizza (Mia) | $47.00 | Equal, all 3 | 15.67, 15.67, 15.66 |
| 5 | Electric (Omar) | $96.00 | Shares, 2, 1, 1 | 48.00, 24.00, 24.00 |
| 6 | Cake (Noor) | $35.00 | Exact amounts | 10.00, 15.00, 10.00 |
| Person | Total paid | Total share | Net |
|---|---|---|---|
| Mia | $111.00 | $125.00 | Owes $14.00 |
| Noor | $95.00 | $103.25 | Owes $8.25 |
| Omar | $114.50 | $92.25 | Owed $22.25 |
Total paid and total share are both $320.50, and the nets add up to 0.00, because the $14.00 and $8.25 owed equal the $22.25 owed to Omar. Mia pays Omar $14.00 and Noor pays Omar $8.25, so two payments clear it.
The month-end close
- Fix any Row check cell that is not 0.00 before doing anything else.
- Confirm that Total paid and Total share match, then read the check cell. Anything other than 0.00 means a mistyped amount or share.
- Have the person who owes the most pay the person who is owed the most, using the smaller of the two amounts. Repeat until every balance is gone.
- Add each payment as a row, with the sender in Paid by and the full amount in the receiver’s share cell. Every net then reads 0.00.
- Start next month with any unpaid balance as its first row.
What to agree on
- Who gets the spare cent. The last column takes the remainder, so that person pays a cent more or less. Rotate the column order each month, or accept it.
- Who edits the sheet. One person types the rows and everyone else sends amounts the same day, so no purchase is entered twice.
When a spreadsheet stops being the easy option
A sheet is fast while most rows are split the same way. EvenOut has no data export, so nothing downloads from it into this sheet. It does the arithmetic: five ways to split (equally, exact amounts, percent, shares or by item) chosen for each expense, cent-accurate shares and a “Simplify debts” setting that reduces the number of payments in a group.
How to split expenses walks through the five methods, and the formulas for splitting bills explains the arithmetic behind each. How to figure out who owes who explains the net-balance method, and the who owes who calculator checks a sheet where every row is split equally among everyone.
Frequently asked questions
Why does the last person take the remainder?
Rounding every share to the cent can leave a row a cent over or under. $47.00 split three ways rounds to $15.67 for each person, which adds up to $47.01. When the last cell is the amount minus the other shares, it becomes $15.66 and the row adds up exactly.
What if some purchases are in another currency?
Add a Currency column and keep one settle-up block for each currency, because dollars and euros added together give a number that means nothing. SUMIFS can match the payer and the currency in one formula. If the group wants one currency, agree on a single rate and apply it once, to the final nets.
Can the sheet work out who pays whom by itself?
The formulas here stop at each person's net. Matching people who owe to people who are owed takes a few extra steps, so the example does it by hand. With three or four people it takes a minute. For a sheet where every row is split equally among everyone, the who owes who calculator does the same matching.
Sources
Checked October 6, 2026.
Keep reading
- Calculator Who owes who calculator Enter what each person paid to see every balance and the short list of payments that settles the group. Odd cents are shared out so it adds up.
- Guide The formulas for splitting bills, method by method Eight bill splitting formulas with variables, a worked example and a check for each: equal, percent, shares, exact, by item, income, balances and payments.
- Guide How to figure out who owes who after shared expenses Add up what each person paid, divide by the people sharing, and subtract. Four friends and five expenses settle in 3 payments instead of 6 pairwise debts.
- Guide How to split expenses: five methods, one worked example One $480 cabin bill for four people, split five ways: equally, by percent, by shares, by exact amounts and by item. See the sums and pick the method.
- Template Roommate expense tracker template for a spreadsheet or paper A roommate expense tracker with a ledger, a net-balance formula and an 8-row worked example that settles four people in 3 payments instead of 23.