Google Sheets can handle a small shared utility ledger for roommates, couples, families, or other informal groups. Put one bill on each row, calculate an equal share, and keep the receipt URL beside an optional image preview.
The setup is simple. The sharing choice needs more care. Utility receipts can expose service addresses or account details, so use access settings that fit the information you store.
Choose the columns before entering bills
Decide what each row means before you enter the first bill. A consistent row makes sorting, totals, and handoffs much easier.
| Column | What it stores | Example or rule |
|---|---|---|
| A: Date | Bill due date or billing date | Pick one convention and use it consistently. |
| B: Utility Type | The bill category | Electric, water, gas, internet, or trash |
| C: Total Amount | The full bill amount | Enter a number and apply currency formatting. |
| D: People Sharing | The number of people included | Use 3 for a three-person split. |
| E: Share Per Person | The equal share calculation | =IF(D2>0,C2/D2,0) |
| F: Paid By | The person who covered the bill | Name or initials |
| G: Status | The bill or reimbursement stage | Pending, Paid, or Settled |
| H: Receipt URL | The link to the stored receipt | Drive link or an image-compatible URL |
| I: Receipt Preview | An optional image inside the sheet | =IMAGE(H2,1) when H2 contains a usable image URL |
| J: Notes | Context that does not fit elsewhere | Billing period, custom split, or exception |
Use numeric values in column C. Apply currency formatting from the Sheets menu instead of typing labels into the amount.
Create dropdowns for Utility Type and Status. For example, define Pending as not yet paid, Paid as paid by the person in column F, and Settled as fully reimbursed. That distinction prevents a paid utility bill from looking like every roommate has already repaid the person who fronted it.
If you want a compact tracker, you can leave out column I. Keeping both H and I is usually clearer: H remains a dependable clickable record, while I gives the group a quick visual check.
Add the receipt link and image preview
A link and a preview solve different problems. The link opens the original record, while the preview helps someone identify the bill without leaving the sheet.
- Take a clear photo or scan of the bill or payment receipt. Remove information the group does not need when practical.
- Upload the file to Google Drive.
- Choose the access setting deliberately. Named access is better for private household records. Google describes
Anyone with the linkas access for anyone who has the link, without requiring them to sign in; see Google Drive's sharing help. - Paste the receipt address into H2.
- To test an image preview directly, enter
=IMAGE("paste-your-url-here", 1)in I2. - Increase the column width and row height until the preview is readable.
Mode 1 fits the image inside the cell while keeping its proportions. If H2 already contains the image URL, use =IF(H2="","",IMAGE(H2,1)) in I2 so empty rows stay blank.
Turns out, a Drive address that opens in a browser is not always a direct image address. If a compatible image URL is available, you can test a Drive-style format such as https://drive.google.com/uc?id=FILE_ID, but check the result in your own sheet. A standard Drive viewer link may open normally and still fail inside IMAGE.
When the preview fails, keep the Drive address in H and use it as a plain link. Do not weaken receipt permissions just to create a thumbnail. For a PDF, keep the PDF link or convert the needed page to an image before using IMAGE.
A separate clickable label is another option. Put =HYPERLINK(H2,"Open receipt") in an extra column, or test =HYPERLINK(H2,IMAGE(H2,1)) if your Sheets setup accepts the image as the hyperlink label. Keeping the plain URL visible is easier to troubleshoot.
Calculate equal shares and record exceptions
Put =IF(D2>0,C2/D2,0) in E2, then drag it down the column. The formula returns zero when the people count is zero, which avoids a division error.
Format C and E as currency. Keep D as a number.
For a blank-friendly version, use =IF(OR(C2="",D2=""),"",IF(D2>0,C2/D2,0)). It leaves a new row empty until you enter the amount and headcount.
A January electric bill of $150 shared by three people produces a $50 share. Jane can go in F2, and the row can move from Pending to Paid after she pays.
Useful summary formulas include:
=SUM(C2:C)for total utility charges=SUMIF(G2:G,"Paid",C2:C)for bills marked paid=COUNTIF(G2:G,"Pending")for the number of open rows
An equal split is a rule, not a fact about fairness. Usage-based shares, nights stayed, room size, income-based contributions, and custom amounts may fit your agreement better. Add a Split Method column and record the reason in Notes, or replace the equal-share formula for that row with an agreed custom amount.
Do not change a formula silently. Write down the exception so the next person reviewing the sheet can follow it.
Use a Payments tab for partial reimbursements
One status cell works for a simple arrangement where one person pays and everyone settles together. Thing is, it gets muddy when people reimburse on different dates or send different amounts.
Add a second tab named Payments when that detail matters:
| Column | Record |
|---|---|
| A: Payment Date | When the reimbursement was sent |
| B: Bill ID | A matching identifier from the main sheet |
| C: From | The person sending money |
| D: To | The person receiving it |
| E: Amount | The amount recorded |
| F: Note | Method, confirmation note, or context |
Add an optional Bill ID column K on the main sheet. Use a simple label such as Jan-Electric. In an optional Reimbursed Amount column L, use =SUMIF(Payments!B:B,K2,Payments!E:E) to total recorded reimbursements for that bill.
Mark a row Settled only when the recorded payments match the amount the group agreed was owed. The spreadsheet records what people enter; it does not verify that a bank transfer or payment-app transfer actually completed.
Share the sheet without giving everyone editing power
Sheet access and receipt access are separate decisions. Someone may need to review balances without being allowed to overwrite formulas.
Share the sheet with specific email addresses when receipts contain private information. Give Viewer access to people who only need to review the record. Reserve Editor access for the person or people who maintain rows, formulas, and receipt links.
Protect the calculation cells through Data > Protect sheets and ranges. Formula columns E and I, plus summary cells, are good candidates. Leave H editable if participants need to add receipt links, or limit it to the people who upload files.
Protection reduces accidental edits. It does not make a receipt private, hide a visible URL, or verify a reimbursement. Review the Drive permissions on the receipt files themselves.
Follow a monthly utility workflow
Keep updates tied to the billing cycle rather than memory.
- Add a row with the date, utility type, total amount, and number of people sharing.
- Check the split rule before anyone pays. Change the method when the household agreement calls for a custom allocation.
- Record the payer in F and change the status to
Paidafter the utility bill is covered. - Upload the receipt, paste its address into H, and test the preview in I.
- Send a short message such as:
January electric is $150. Three shares are $50. Jane paid; please record your reimbursement when it is sent. - Add partial reimbursements to the Payments tab, if you use one. Move the main row to
Settledonly after the outstanding amount is accounted for. - Review open rows weekly and review totals at the end of each month. Use
File > Download > PDFfor a record, then duplicate the sheet when you start a new annual file.
A sample row should be enough to test the entire process. Use one small bill first, not the household's whole history.
Fix common tracker problems
| Problem | Likely cause | Practical fix |
|---|---|---|
| The image cell shows an error | The URL is private, points to a viewer page, or is not an image | Test access, try an image-compatible URL, or keep a plain receipt link |
| The share shows zero | D2 is blank or zero | Enter the headcount and check the formula |
| Totals look wrong | An amount was stored as text | Re-enter the amount as a number and apply currency formatting |
| Everyone appears settled too early | One status cell is hiding partial payments | Add the Payments tab and use a Bill ID |
| A formula was overwritten | Formula cells were left open to editors | Protect the formula ranges |
| A receipt reveals information unnecessarily | The file was shared too broadly | Use named access or upload a redacted copy |
Know where this template stops
This layout works well for a small group tracking recurring electric, water, gas, internet, or trash bills. It is especially manageable when one person maintains the sheet and the group follows a written split rule.
The work remains manual. Google Sheets will not automatically scan every receipt or confirm that a reimbursement moved. Large groups, many custom allocations, and frequent partial payments may need a more specialized recordkeeping workflow.
To be honest, an app is not automatically better. Compare any alternative by the functions you actually need: receipt capture, split calculation, payment requests, exports, and a clear settlement history. A sheet may still be the simplest record even when the actual payment happens somewhere else.
Common questions
Can I use a normal Google Drive sharing link with IMAGE?
Not always. A viewer link can work in a browser but fail as an image source. Keep the link in H, test an accessible image URL in I, and use the plain link when the preview is unreliable.
Can I make the receipt preview clickable?
Keep H as the receipt URL and use =HYPERLINK(H2,"Open receipt") in another cell. You can also test =HYPERLINK(H2,IMAGE(H2,1)), but the separate link is easier to inspect.
Can I protect the receipt column?
Yes, but only protect H if regular contributors do not need to add or replace links. Formula previews in I should usually be protected.
Do I need a Payments tab?
No. Use the main Status column for a simple one-payer arrangement. Add the tab when reimbursements are partial, delayed, or coming from several people.
Create the headers, enter one sample $150 electric bill for three people, and test both the share formula and receipt permissions before inviting the group.