A utility tracker works when one row holds one bill and the math stays visible. You put the utility name, the total, who covered it, each roommate's cut, a link to the bill, and the settlement status in that single line. Turns out that simple layout heads off almost every shared house argument. Why bother keeping it that tight? Paying an invoice and owing the money are two entirely different things.

Say Alex fronts a $200 electric bill shared equally with Sam. Each person owes $100. Alex paid $200, but Alex only owes $100 of it. Sam owes $100 and paid nothing. Alex has a positive net of $100. Sam sits at negative $100.

The sheet tracks that debt. It won't move money for you.

Build the Bills tab around one row per bill

Name your main tab Bills. Give every invoice its own row instead of making separate rows for each roommate. That keeps recurring electric, water, gas, internet, trash, and sewer bills easy to filter down the road.

Column What to enter Why it helps
Date Bill date or due date Gives every entry a consistent time reference.
Utility Type Electric, water, gas, internet, or another label Groups similar bills for totals.
Total Amount Full bill amount Stores the source amount before splitting.
Participant Count Number of people sharing that bill Supports equal-split calculations.
Split Method Equal, Usage, Per Room, Income-Based, or Custom Shows the rule used for the allocation.
Paid By Person who paid the provider Separates payment from responsibility.
Payment Status Paid or Unpaid Keeps unpaid bills out of reimbursement balances.
Person 1 Share % Percentage assigned to the first participant Drives the owed amount formula.
Person 1 Owed Formula result Shows that participant's dollar responsibility.
Person 2 Share % Percentage assigned to the second participant Supports equal or uneven splits.
Person 2 Owed Formula result Shows the second participant's responsibility.
Settlement Status Open, Partial, or Settled Tracks whether reimbursements are finished.
Receipt Link Link to the bill or receipt Lets the group verify the amount.
Notes Meter readings, exceptions, or agreement details Preserves the reason behind unusual splits.
Share Check Formula result Flags percentages that do not total 100 percent.

When three to six people split the bills, insert an extra share and owed pair for each person right before Settlement Status. Put real names in the header row instead of placeholder labels if others will view the sheet. Keep those names identical everywhere.

Choose what the split method means

The split method column describes the allocation rule. It does not record the payment event.

Split method Best fit Record alongside it
Equal Everyone uses the utility in roughly the same way Participant count and agreed names
Usage Meter reads or another agreed measure show different use Readings, dates, or the calculation
Per Room Bedrooms or private spaces justify different shares The room or occupancy rule
Income-Based The household has agreed to contributions tied to income The written agreement, not private income details
Custom Fixed percentages or another unusual arrangement A plain-language explanation in Notes
Reimbursement Someone fronted the payment for the group The actual responsibility shares and payer

Thing is, fronting a bill is a cash-flow issue, not a split method. When Alex pays a $200 bill split 50/50, enter Paid By: Alex, Equal, 50%, and 50%. Don't flip the shares to 100% for Alex and 0% for Sam unless Alex truly owes the entire bill alone.

Usage splits sound fair for summer electricity, but they need verifiable numbers. If roommates cannot easily check the meter math, equal splits create far fewer arguments.

Set up the Google Sheets formulas

Open a blank file in Google Sheets and create the Bills tab. Excel works the same way, though menus differ slightly.

  1. Put the headers across row 1. Add extra share and owed pairs before the settlement columns if you have more than two people.
  2. Freeze the first row with View > Freeze > 1 row and turn on filtering with Data > Create a filter.
  3. Format dates as dates, amounts as currency, and shares as percentages.
  4. Add dropdowns for Split Method, Payment Status, and Settlement Status. Stick to one spelling, like Equal instead of mixing Equal, Even, and 50/50.
  5. Enter a real utility bill right away to test your setup.

Drop these formulas into row 2 for a two-person household:

Cell Formula Result
I2 =IF(H2="",0,C2*H2) Person 1's dollar responsibility
K2 =IF(J2="",0,C2*J2) Person 2's dollar responsibility
O2 =IF(SUM(H2,J2)=100%,"OK","CHECK") Confirms the shares total 100 percent

Format H2 and J2 as percentages. Type 50%, not 50. Drag the formulas down once row 2 works.

A completed row looks like this:

Date Utility Type Total Count Method Paid By Status Person 1 Share Person 1 Owed Person 2 Share Person 2 Owed Settlement
2026-01-15 Electric $200.00 2 Usage Alex Paid 60% $120.00 40% $80.00 Open

If your household divides every utility equally, you can skip percentage columns entirely. With the total in C2 and headcount in D2, use =IF(D2>0,C2/D2,0) in a Share Per Person column. That formula outputs dollars directly. Keep the percentage layout whenever anyone pays an uneven share.

For three or more people, add every person's share into the check formula. Shares of 50%, 30%, and 20% return OK. A typo totaling 99% returns CHECK.

Keep receipts easy to verify

Paste the web link to your bill PDF into Receipt Link. The sheet does not need image files cluttering every row.

If you store receipts in Google Drive, use Get link and restrict access to the people in your house. Public links create privacy leaks. Open the link once in a private browser window to confirm your roommates can see it without requesting access.

To show a clean label in a separate display column, use =IF(M2="","",HYPERLINK(M2,"Open receipt")) when the link sits in M2. Write any weird quirks, like that freezing month where the gas heater ran constantly or an extra trash charge from moving boxes, straight into Notes - because you think you will remember why a random winter bill jumped by eighty bucks six months from now, but you won't.

Add totals and a settlement view

A bill log shows what happened. A balance tab shows what needs to happen next.

Get an overall total with =SUM(C2:C200). To total paid electric bills only, use =SUMIFS(C2:C200,B2:B200,"Electric",G2:G200,"Paid"). Make the ranges longer if your sheet grows past 200 rows.

Create a second tab named Balances using these columns:

Column Purpose
Person Participant name
Total Paid Paid bills where that person appears in Paid By
Total Owed That person's owed column for paid bills
Net Before Settlement Total paid minus total owed
Sent Reimbursements that person has sent
Received Reimbursements that person has received
Net After Settlement Remaining credit or amount due

For Alex in row 2, assuming Alex is Person 1, use:

  • B2: =SUMIFS(Bills!$C$2:$C$200,Bills!$F$2:$F$200,$A2,Bills!$G$2:$G$200,"Paid")
  • C2: =SUMIFS(Bills!$I$2:$I$200,Bills!$G$2:$G$200,"Paid")
  • D2: =B2-C2

For Person 2, switch the owed range in C2 to Bills!$K$2:$K$200. Add another owed column reference for each extra person.

A positive net means the group owes that person money. A negative net means that person owes the group. The plus or minus sign does more work than a vague balance note.

Track reimbursements separately

Create a third tab named Settlements with columns for Date, From, To, Amount, and Note. Add an entry only when cash actually changes hands.

Calculate net after settlement in G2 with:

=D2+SUMIF(Settlements!$B$2:$B$200,$A2,Settlements!$D$2:$D$200)-SUMIF(Settlements!$C$2:$C$200,$A2,Settlements!$D$2:$D$200)

The formula adds money that person sent and subtracts money they received. After Sam sends Alex $100, both balances return to zero.

Use Partial when a bill has split reimbursements and only some are done. Save Settled for when payments are fully logged.

Share the file without losing the math

Skip the open link setting. Invite each person by email instead. Give editing rights to the people logging bills, and set everyone else to view or comment.

Lock your formulas before sharing. In Google Sheets, highlight the headers and formulas, click Data > Protect sheets and ranges, and restrict them while leaving data entry cells open. Excel uses Review > Protect Sheet.

Live editing helps, but two people editing one cell causes confusion. Give each bill its own line. Put corrections in Notes rather than quietly changing an old number.

To be honest, most apartments only need a quick review once a month. When utility statements arrive, check unpaid rows, make sure receipt links open, and export a backup for your records.

Common mistakes and simple fixes

Mistake Fix
Typing 50 into a percentage cell Enter 50% and format the cell as Percent.
Using different labels for the same method Use a dropdown with a short, fixed list.
Letting shares total 99% or 101% Add a Share Check column and correct the row before settling.
Setting the payer to 100% for every reimbursement Record the agreed responsibility, then use Paid By and Settlements for repayment.
Including unpaid bills in current balances Mark them Unpaid and filter them out of paid-bill formulas.
Manually overwriting owed formulas Keep formulas in owed columns and change the share inputs instead.
Sharing a receipt link that others cannot open Test access with one intended viewer.
Replacing an old bill instead of adding a new row Preserve the history and add a correction note.

When a spreadsheet is enough

Spreadsheets suit small households with recurring bills, clear split rules, and a preference for visible records. They also make sense if you need custom percentage splits or a paper trail.

Need Spreadsheet fit Consider another tool when
Recurring household bills Strong fit Manual entry becomes a regular burden
Custom usage or room-based rules Flexible The group needs automated calculations from other systems
Receipt records Add links and notes Scanning and categorizing many receipts is the main task
Reimbursements Use a Settlements tab Payment requests and reminders matter more than the ledger
A small group that avoids new accounts Often a good fit Everyone wants a dedicated mobile workflow

Keep tracking separate from paying. A payment app moves the money, while the spreadsheet preserves the receipts, split rules, and math. No app fixes an unclear agreement.

FAQ

Should the person who paid get a 100% share?

Only if that person is responsible for the full charge. For a shared bill fronted by one person, enter the agreed shares and log the payer in Paid By.

How do I split a utility bill by usage?

Agree on the measurement first. Convert each person's usage into percentages that add up to 100 percent, put the readings in Notes, and link the invoice.

What if the bill has not been paid yet?

Enter the split shares, set Payment Status to Unpaid, and leave Paid By blank. The balance formulas ignore unpaid rows.

Can this replace a tax record?

It organizes personal utility records, but it does not make an expense tax-deductible. Keep original receipts and speak with a qualified tax professional about your situation.

Set up the Bills and Settlements tabs, log your most recent utility statement, and confirm the totals match the receipt before inviting your roommates.