DEV Community

Financely Templates
Financely Templates

Posted on

Split Shared Bills With Roommates in Google Sheets (Who Owes Whom, Automatically)

#googlesheets #productivity #tutorial #beginners

Rent, utilities, groceries, the Wi-Fi bill someone forgot to mention. Splitting shared costs gets messy fast, and a group chat full of "I'll Venmo you later" isn't a system. This Google Sheets tracker logs every shared expense, works out what each person's fair share is, and tells you exactly who owes whom. It takes about ten minutes to set up.

Step 1: An expense log

Make a tab called Expenses with these headers in row 1:

A: Date B: What C: Paid by D: Amount
10/01/2026 Rent Sam 1800
10/03/2026 Electric Alex 96
10/05/2026 Groceries Jordan 142

Every time someone pays for something shared, add a row. Format A as a date and D as currency.

To stop typos from breaking the math, select column C, then go to Data > Data validation, choose Dropdown (from a range), and point it at the list of names you'll make in Step 2.

Step 2: A people tab

Make a tab called People with these headers:

A: Name B: Paid C: Fair share D: Balance
Sam
Alex
Jordan

Step 3: How much each person paid

In People!B2:

=SUMIFS(Expenses!D:D, Expenses!C:C, A2)
Enter fullscreen mode Exit fullscreen mode

Drag it down for every person. This adds up every expense where that person is listed in "Paid by".

Step 4: Each person's fair share

If you split everything evenly, the fair share is the total spent divided by the number of people. In People!C2:

=SUM(Expenses!D:D) / COUNTA($A$2:$A$10)
Enter fullscreen mode Exit fullscreen mode

Drag it down. If you add a fourth roommate to column A, the share updates on its own.

If your split isn't even (say one person has the bigger room), add a column E called Share % with each person's percentage (they should add up to 100%) and use this instead:

=SUM(Expenses!D:D) * E2
Enter fullscreen mode Exit fullscreen mode

Step 5: The balance

In People!D2:

=B2 - C2
Enter fullscreen mode Exit fullscreen mode

A positive balance means the group owes that person money. A negative balance means that person owes the group. The balances always add up to zero, which is a quick way to check the sheet is right:

=SUM(D2:D10)
Enter fullscreen mode Exit fullscreen mode

With the example numbers above, the total is $2,038, so each fair share is about $679.33. Sam paid $1,800 and is owed about $1,120.67. Alex owes about $583.33 and Jordan owes about $537.33.

Step 6: Make it readable

Select D2:D10 and go to Format > Conditional formatting:

  • "Greater than 0" gets a green fill (is owed money).
  • "Less than 0" gets a red fill (owes money).

Then add a plain-English line in People!F2:

=IF(D2<0, A2 & " owes $" & TEXT(-D2, "0.00"), IF(D2>0, A2 & " is owed $" & TEXT(D2, "0.00"), A2 & " is even"))
Enter fullscreen mode Exit fullscreen mode

Drag it down and you have a summary anyone in the house can read without opening a calculator.

Step 7: Settle up and start fresh

When people pay each other back, log it as an expense too: the person paying back goes in "Paid by" with the amount, and the person receiving it gets the same amount as a negative row. Once everyone is at zero, duplicate the Expenses tab for the next month so you keep a history.

Done

You now have a shared-expense sheet that tracks who paid for what, works out everyone's share (even or uneven), and shows who owes whom in plain English.

If you'd rather start from a finished sheet, I make templates as Financely Templates (disclosure: these are my products):

Top comments (0)