Use Google Sheets to build a utility bill tracker with a receipt link column that embeds images directly in cells. Start with these columns: Date, Utility Type, Total Amount, Number of People Sharing, Share Per Person (with formula), Paid By, Status, and Receipt Link/Image. This setup helps U.S. roommates, couples, or small groups track recurring bills like electricity or water, store proof via public URLs, and calculate even splits.

Upload receipts to Google Drive for a shareable URL, then use the IMAGE function to display them. Share the sheet with view-only access for most group members, protecting key ranges to prevent accidental changes. Enter bills monthly, review shares, and export for records. This free template handles tracking and reimbursements without apps for groups under 5 people.

Recommended Columns for Utility Bill Tracker

A utility bill tracker in Google Sheets needs columns focused on shared expenses, proof, and accountability. Tailor them to utilities like electric, water, gas, internet, or trash.

Here is a practical set:

Column Purpose Example Entry Notes
A: Date Bill due or payment date 2026-01-15 Use date format for sorting.
B: Utility Type Identifies the bill Electric Dropdown: Electric, Water, Gas, Internet.
C: Total Amount Full bill cost $150.00 Currency format.
D: # People Number sharing the bill 3 For even splits.
E: Share Per Person Individual amount owed =C2/D2 Simple formula (adapt for uneven splits).
F: Paid By Who fronted the money John Name or initial.
G: Status Payment/reimbursement stage Pending / Paid Dropdown for consistency.
H: Receipt Link/Image Proof of payment =IMAGE("url", 1) Embeds image; clickable.

For the Share Per Person column, use this formula: =IF(D2>0, C2/D2, 0). It divides total by people sharing, avoiding division by zero. Enter it in E2 and drag down.

The Receipt Link/Image column stores visual proof. Group members verify payments without chasing emails. Resize the column width and row height (e.g., 200 pixels) for clear viewing.

Add a Totals row at the bottom: =SUM(C:C) for overall utilities spent. This keeps records simple for roommate rent add-ons or family budgets.

Set Up the Receipt Link Column in Google Sheets

Receipts provide proof for shared utilities, reducing disputes over who paid what.

  1. Take a photo or scan the bill using your phone.
  2. Upload to Google Drive: Right-click the file > Get link > Set to "Anyone with the link" (view access).
  3. Copy the direct image URL (ends in .jpg or .png; use format like https://drive.google.com/uc?id=FILEID if needed).
  4. In the Receipt column (H2), enter: =IMAGE("paste-your-url-here", 1).

The =IMAGE("url", 1) syntax fits the image within the cell while maintaining aspect ratio. Mode 1 auto-resizes; test by pressing Enter - the image appears inline.

Right-click the image for options like "Open image in new tab" for full size. Resize the cell: Select row/column > Drag borders.

Test with a sample electric bill photo. If the image breaks, check URL permissions or re-upload. This column works for PDFs too if converted to images.

Add Basic Formulas and Sharing Permissions

Basic formulas automate shares; permissions keep data safe.

In Share Per Person (E2): =IF(D2>0, C2/D2, 0). Drag to fill. For uneven splits (e.g., income-based), add columns for each person's percentage.

For totals: In a summary cell, =SUMIF(G:G, "Paid", C:C) shows paid amounts.

Secure sharing:

  1. Click Share (top-right).
  2. Add emails or generate a link.
  3. Set to "Viewer" (view only, no edits) for most; "Editor" for trusted admins.
  4. Use Protected Ranges: Data > Protect sheets and ranges. Select formula columns (e.g., E:H), set editors, warn or reject changes.

Viewers see receipts and balances but can't alter them. Request edit access via the green button if needed. This prevents accidental deletes during group reviews.

Workflow for Monthly Utility Tracking

Track utilities monthly to align with billing cycles.

  1. Entry on due date: Admin logs Date, Utility Type, Total Amount, # People. Formula auto-calculates shares.
  2. Payment: One person pays (e.g., via bank transfer). Update Paid By and upload receipt URL to IMAGE function.
  3. Reimbursements: Share sheet link in group chat. Others confirm, pay via Venmo/Zelle (educational example), mark Status "Paid".
  4. Monthly review: First of month, scan for "Pending". Discuss outliers (e.g., high summer AC).
  5. Archive: File > Download > PDF for records. Duplicate sheet yearly.

Example: January electric bill $150 for 3 roommates. Each owes $50. Jane pays, adds receipt image. Others reimburse; all mark paid.

Common mistakes:

  • Unprotected sheets: Editors overwrite formulas.
  • Broken URLs: Private Drive links fail; always use public view.
  • Inconsistent naming: "Elec" vs "Electric" breaks dropdowns - standardize.
  • Forgetting row heights: Images clip; auto-fit rows.

Review weekly for active bills, monthly for summaries.

When This Template Works Best (and Limits)

This Google Sheets tracker suits small U.S. groups (roommates, couples, families) with 4-8 recurring utilities. It handles tracking, proof, and reimbursement requests without fees or apps. Export PDFs for disputes or moves.

Tradeoffs:

  • Strengths: Free, customizable, visual receipts, group-visible.
  • Limits: Manual entry (no auto-scans), no payment integration, edit risks if unprotected. For 5+ people or high-volume (e.g., plus groceries), consider apps for scanning/payments as educational options - Sheets still tracks.

Manual works when trust is high and bills are few. Add chores or rules tabs for full household docs.

FAQ

How do I make the receipt image clickable to full size?
Right-click the embedded image > Open image in new tab. Or hyperlink the cell: =HYPERLINK("full-url", IMAGE("url", 1)).

Can I protect only the receipt column from edits?
Yes, via Data > Protect sheets and ranges. Select the column range, set permissions to specific editors only.

What if the IMAGE function doesn't work for my receipt URL?
Ensure public access ("Anyone with link"). Use direct image links (e.g., Drive uc?id=). Test in incognito; fallback to hyperlinked text URL.

How often should we review the tracker sheet?
Weekly for pending bills, monthly for summaries and reimbursements. Set calendar reminders.

Should we switch to an app if we add non-utility bills?
Consider if volume grows (e.g., groceries/events). Sheets scales to dozens of rows; apps add scanning/payments but may fee (qualitative).

Next, copy these columns into a new Google Sheet, test a sample bill, and share the link with your group.