How to split bills between roommates in Excel

Set up one shared log

Three roommates, one month of bills. Alex paid the rent, Sam did the big grocery run and the electricity, Jordan covered internet and a few small things. Nobody wants to argue about it in the group chat, so everything goes in one table: date, item, who paid and the amount.

Keep one row per payment and type names exactly the same way every time. SUMIFS ignores capitals, so "alex" still counts as Alex, but "Sam " with a trailing space doesn't match Sam and quietly returns 0. A drop-down list of names (Data > Data Validation > List) stops that. Ctrl+T turns the log into a table so new rows are included in every formula.

Columns E to G are for bills not everyone shares (the last section). Leave them as 1 for now.

1. Put the headers Date, Item, Paid by and Amount in A1:D1.
2. Add one row per bill as it's paid, not at the end of the month.
3. In I2:I4 list the people. Next you'll add Paid, Fair share and Balance columns for them.

What each person paid

SUMIFS adds the amounts where the Paid by column matches the person. Alex paid 1896.15, Sam 230.77 and Jordan 104.49, for a month total of 2231.41.

Lock the log ranges with $ so the formula fills down the person list. If you track several months, add a date condition: =SUMIFS($D$2:$D$8,$C$2:$C$8,I2,$A$2:$A$8,">="&DATE(2026,10,1),$A$2:$A$8,"<="&DATE(2026,10,31)).

Equal split: who owes whom

With an equal split, everyone's fair share is the total divided by 3: 743.80. The balance is paid minus fair share. In Excel 365 you can do all three people in one formula by giving SUMIFS the whole name list.

Alex gets back 1152.35. Sam owes 513.03 and Jordan owes 639.31. Settling up is then simple: when only one person is owed, everyone else pays that person what they owe. So Sam sends Alex 513.03 and Jordan sends Alex 639.31. With three people, at most two payments ever settle the month.

To print it as sentences: =I2:I4&IF(n<0," pays "&TEXT(-n,"0.00")," gets back "&TEXT(n,"0.00")) inside a LET that names the balances n gives "Alex gets back 1152.35", "Sam pays 513.03" and "Jordan pays 639.31".

Make the cents add up

Look closely at those rounded balances: 1152.35 - 513.03 - 639.31 is 0.01, not 0. The month total of 2231.41 doesn't divide evenly by 3. Each share is really 743.8033..., and 743.80 times 3 is 2231.40, a cent short.

The fix is to round the shares and let one person take the leftover cent. Give the first two people =ROUND(total/3,2) and the last person the total minus the other shares. Jordan's fair share becomes 743.81, his balance -639.32, and the balances add up to exactly 0. Rotate who takes the odd cent each month if anyone cares.

The same thing happens with percentage splits: 30% of 2231.41 is 669.42 after rounding, and 892.56 + 669.42 + 669.42 is 2231.40. Use the same "last person gets the remainder" rule.

Unequal splits: shares and bills only some people use

If Alex has the bigger room and you've agreed on 40/30/30, put those shares in J2:J4 and multiply: fair share = total * share. The shares are input cells you agree on together, and =SUM(J2:J4) should be exactly 100%. With 40/30/30 the balances are 1003.59, -438.65 and -564.93.

Some bills aren't for everyone. Sam doesn't use the streaming service, so his cell in F6 is 0. Each person's fair share is then each bill divided by the number of people sharing it: SUMPRODUCT does that across all rows. Alex and Jordan each pay 746.47 and Sam 738.47, which adds up to 2231.41 exactly. The balances become 1149.68, -507.70 and -641.98.

Want this ready-made for your own household? Paste your log into Cellwise Fix (cellwise.app/excel-formula-helper/) and ask for "balance per person"; it writes the formula for your columns and checks it on your data.