When a roommate leaves before the electricity billing cycle ends, build the sheet around two separate numbers: each person's share and any money that person still owes or should receive. Use one row per roommate per bill, calculate days or usage with an agreed rule, then record repayment in a separate settlement log.
Turns out, the formula is the easy part. A day-based split is a practical estimate when individual meter data isn't available, not proof of exact consumption. If a lease or written roommate agreement sets a different rule, follow that rule and record it.
Pick the allocation rule first
Your Google Sheets template should state how the group divides electricity before anyone calculates a balance. Different methods can produce different answers from the same bill.
| Rule | How it works | Good fit | Watch for |
|---|---|---|---|
| Equal per person | Divide the bill equally among people responsible for the full period | Stable occupancy | It ignores partial stays and different usage |
| Equal per day while present | Divide each day's cost among the people present that day | A roommate moves in or out mid-cycle | It assumes each day's usage is shared evenly |
| Person-days | Allocate the total bill by each person's days divided by total occupied person-days | A simple row-based tracker | It is not identical to equal-per-day splitting |
| Usage based | Use meter readings, kWh data, or an agreed estimate | Roommates have noticeably different private usage | It needs reliable data and a clear agreement |
| Room-size or income weighting | Apply agreed percentages instead of occupancy days | The household already uses that policy | Neither factor measures electricity use directly |
| Reimbursement after proof | Keep the original allocation, then record a transfer backed by the bill and payment record | One roommate paid the utility for everyone | A reimbursement is a settlement, not a new bill split |
Equal-per-day and person-day methods aren't identical. With equal-per-day splitting, each day's cost is divided among the people present. With person-days, the entire bill follows the ratio of occupied days.
Pick one method before the result appears. That prevents the formula from becoming an argument.
Build the tracker around two tabs
For most households, two tabs are enough. Keep the bill calculation in Shares and the money movement in Settlements.
| Tab | One row means | Main purpose |
|---|---|---|
| Shares | One roommate's share of one bill | Calculates responsibility and balance |
| Settlements | One transfer between two people | Records who reimbursed whom |
In the Shares tab, repeat the bill details on each roommate row. That makes filters, formulas, and sorting easier to understand.
| Column | What it records |
|---|---|
| A: Bill ID | A consistent label such as ELEC-04 |
| B: Period start | First day covered by the utility bill |
| C: Period end | Last day covered by the utility bill |
| D: Total bill | Full electricity amount |
| E: Roommate | Person responsible for the row |
| F: Move-in | First day of responsibility |
| G: Responsibility end | Last day included in that person's share |
| H: Days in cycle | Number of days in the billing period |
| I: Days responsible | Overlap between the billing period and the person's dates |
| J: Allocation method | Person-days, equal-per-day, usage, or percentage |
| K: Weight or percentage | Optional custom share, entered as a decimal |
| L: Share owed | Amount assigned to that roommate |
| M: Paid to utility | Amount that person paid directly toward the bill |
| N: Net balance | Payment minus assigned share |
| O: Receipt | Link or file name for the bill |
| P: Check | Optional percentage-total check |
Use Responsibility end rather than automatically treating the truck-moving date as the final day owed. If the group counts the move-out date, enter it. If responsibility ends the day before, enter that date instead.
The Settlements tab needs fewer fields:
| Column | What it records |
|---|---|
| Date | Date the transfer was requested or completed |
| Bill ID | Related electricity bill |
| From | Person sending money |
| To | Person receiving money |
| Amount | Agreed reimbursement |
| Status | Pending, paid, or disputed |
| Note | Reason, payment reference, or follow-up |
Don't overwrite the original share after someone pays. The settlement row is the audit trail.
Add formulas that show the math
These formulas assume the first data row is row 2 and that each roommate has a separate row for the same Bill ID.
In H2, calculate the inclusive billing period:
=C2-B2+1
In I2, calculate the number of days that overlap the billing period:
=MAX(0,MIN(C2,IF(G2="",C2,G2))-MAX(B2,F2)+1)
This formula treats both the start date and responsibility end date as included. Copy it down the column.
For the person-day method, enter this in L2:
=IFERROR(D2*I2/SUMIF($A:$A,A2,$I:$I),0)
The formula divides each person's days by the total days listed for that Bill ID, then applies that ratio to the bill.
For a custom percentage split, enter values such as 0.25 or 0.5 in column K and use this in L2:
=D2*K2
All percentage rows for the same bill should total 1. In the optional check column, use:
=SUMIF($A:$A,A2,$K:$K)
Format currency columns as currency and column K as a percentage when appropriate. Leave K blank for the person-day formula.
Finally, calculate each person's position in N2:
=M2-L2
A positive balance means the person paid more than their share and is due money. A negative balance means the person still owes money. The sheet records the position; it doesn't collect the payment.
Set up the template before the next bill arrives
- Open Google Sheets and create a blank spreadsheet. Name it for the household and the utility.
- Add the
SharesandSettlementstabs. Freeze the header row so column names remain visible while you scroll. - Add the columns above, then format the date fields consistently. Google Sheets can interpret dates differently when sheet locales differ, so use one date format and consider a date-validation rule. The Google Sheets date validation walkthrough shows the relevant settings.
- Enter one row for every roommate connected to each bill. Repeat the Bill ID, period dates, and total bill exactly.
- Agree on the allocation method. Write the choice in column J and add a short note if the method is unusual.
- Add the receipt link or file name. Keep the original statement somewhere the group can access it.
- Copy the formulas down, check the total assigned amount, and review the result before requesting reimbursement.
The bill's payment date belongs in a separate field or note. It shouldn't replace the billing period dates used for proration.
Worked example with a roommate leaving halfway through
Suppose a 30-day electricity bill is $180. Alex is responsible through day 15. Bea and Cam are responsible for all 30 days.
Under the person-day method, the denominator is 75 person-days: 15 for Alex, 30 for Bea, and 30 for Cam.
| Roommate | Days responsible | Share owed | Paid to utility | Net balance |
|---|---|---|---|---|
| Alex | 15 | $36 | $180 | +$144 |
| Bea | 30 | $72 | $0 | -$72 |
| Cam | 30 | $72 | $0 | -$72 |
Alex paid the entire bill, so the settlement log would show Bea sending Alex $72 and Cam sending Alex $72.
Thing is, equal-per-day splitting would produce a different result. The first 15 days would be divided among three people, and the last 15 days among two. Under that rule, Alex would owe $30, while Bea and Cam would each owe $75.
Neither result is automatically correct. The group needs a stated rule.
Record the reimbursement separately
Some expense tools label a one-time transfer as Reimbursement and show the person covering the transaction at 100% while the other person shows 0%. That can work for a settlement record. It shouldn't replace the original electricity allocation.
Use this workflow:
- Confirm the bill period, responsibility dates, and allocation method.
- Check who paid the utility and compare each payment with each assigned share.
- Add a settlement row from the person with a negative balance to the person with a positive balance.
- Mark the transfer as pending until both people agree it was completed.
- Leave the original share and payment fields unchanged.
A clear request might say: "Electric bill ELEC-04: your share is $72. Please reimburse Alex $72. The receipt and calculation are in the Shares tab."
If several people paid the utility, calculate each person's net position first. Don't send the full bill amount to the person who happened to submit the statement.
Share the file without losing control
Only trusted roommates need Editor access. Everyone else can review the record without changing formulas.
| Access level | Sensible use |
|---|---|
| Editor | Roommates who enter bills, payments, or dates |
| Commenter | Someone reviewing the calculation and leaving questions |
| Viewer | A landlord, parent, or departing roommate who only needs the final record |
For a private household sheet, keep general access set to Restricted and add specific email addresses. Google Sheets permission controls can vary by account, but the Google Sheets sharing permissions guide covers the main access choices.
Avoid putting bank account details, passwords, or unrelated personal information in the file. The tracker needs enough detail to explain the split, not every detail of the household's finances.
If a formula gets overwritten, use version history to inspect or restore an earlier version. This Google Sheets version history walkthrough describes the file menu and restore process.
Fix common tracking mistakes
| Problem | Better approach |
|---|---|
| Counting the move-out date inconsistently | Write the date rule in a note and use it for every roommate |
| Using the bill's arrival date as the proration date | Use the period start and end printed on the statement |
| Treating a day estimate as exact electricity usage | Label the method as an estimate unless actual usage data supports it |
Putting a reimbursement into Paid to utility |
Keep utility payments in M and transfers in Settlements |
Entering 25 instead of 0.25 for a percentage |
Format the cell as a percentage and enter the decimal share |
| Forgetting the receipt | Add a viewable receipt link or note where the statement is stored |
| Letting anyone with the link edit | Use Restricted access and named collaborators |
The bill may arrive after move-out. The final meter read may be missing. Someone may remember the agreement differently. Put the assumption in the note anyway. A plain note now can save a long, boring argument later.
A few edge cases
What if the bill arrives after the roommate leaves?
Enter the actual billing period and the responsibility dates. Use the received or paid date only for recordkeeping. If the group estimated the split, mark it as estimated and update the row when better information becomes available.
Can we separate fixed charges from electricity usage?
Yes, if the statement identifies them and the group agrees on separate rules. A fixed service charge might use a per-person split, while the usage portion might use days or meter readings. If the bill does not separate the amounts, document the chosen estimate.
What if one roommate used much more electricity?
Use a usage-based allocation only when the group can support it with meter data or a written estimate. A regular household split is easier to audit, but it may not feel fair when private usage is unusually different.
Does the tracker make someone pay?
No. It documents the bill, calculation, and agreed reimbursement. The actual handoff happens through whatever payment method the group chooses, and the confirmation belongs in the settlement record.
A spreadsheet works well when the group needs a transparent record and occasional reimbursements. If the household needs recurring reminders or a dedicated payment workflow, compare those functions separately from the calculation itself.
Create the two tabs before the next electricity bill arrives. Add the last statement, enter each responsibility end date, and ask the roommates to approve the allocation rule in writing.