Have you ever tried sorting out three months of crumpled paper receipts and Venmo screenshots just to settle an electric bill? It turns into a headache fast. You do not need a paid app subscription to fix it, either. If you connect an empty Google Sheet to a plain Google Form, you get a clean intake log that prevents people from typing over each other's cell entries. The whole build takes ten minutes.
Whoever buys household supplies snaps a quick photo, types the total, and gets on with their day.
Core Columns for Logging Shared Bills
Open a blank spreadsheet in Google Sheets. Name the first tab "Expenses" and keep row 1 clear for your headers. That gives you the basic structure.
| Column | Header | What Goes Here |
|---|---|---|
| A | Date | Purchase date (formatted YYYY-MM-DD or MM/DD/YYYY) |
| B | Description | Store or item name, like Trader Joe's or the Wi-Fi bill |
| C | Category | Groceries, Utilities, Rent, Household, or Travel |
| D | Amount | Total cost in USD |
| E | Paid By | Whoever fronted the bill |
| F | Split Type | Even split, custom share, or individual purchase |
| G | Receipt Link | Google Drive photo link or digital invoice URL |
| H | Status | Paid, Pending, or Reimbursement |
Freeze that top row before doing anything else. Head to View > Freeze > 1 row. Your column titles will stick to the top as entries roll in over the year. It keeps the sheet readable on phones.
Build a Google Form for Mobile Receipt Entry
Typing numbers directly into cells on a small phone screen is asking for trouble. Someone always drags a finger across the screen, wipes out a formula, or overwrites yesterday's entry. A Google Form completely eliminates that mess.
- Inside your sheet, click Tools > Create a new form.
- Match your fields to the core columns: Date (Date field), Description (Short answer), Category (Dropdown list), Amount (Short answer with number validation enabled), and Paid By (Multiple choice with group member names).
- Add a File Upload question called "Receipt Photo" so roommates can upload paper slips directly from a phone camera.
- Check the Responses tab to verify answers route into a tab named "Form Responses 1".
- Have everyone save the form link directly to their mobile home screen.
Once that is saved, logging a receipt takes twenty seconds. The spreadsheet updates silently in the background.
Summary Formulas for Group Balances
Create another tab called "Summary" and park your calculations there. Never drop summary formulas inside your raw response tab. Google Forms inserts brand new rows for every single submission. Those new rows push cells around and break references.
To group total spending by category, put this formula into cell A2 of the Summary tab:
=QUERY('Form Responses 1'!A:H, "SELECT C, SUM(D) WHERE C IS NOT NULL GROUP BY C LABEL SUM(D) 'Total Amount'")
To calculate what each person fronted, use this query in another cell:
=QUERY('Form Responses 1'!A:H, "SELECT E, SUM(D) WHERE E IS NOT NULL GROUP BY E LABEL SUM(D) 'Total Paid'")
Turns out, QUERY handles almost any grouping your household or travel crew will ever need. When you just need to pull one isolated total, like electric bills covered by Jordan, SUMIFS works neatly:
=SUMIFS('Form Responses 1'!D:D, 'Form Responses 1'!C:C, "Utilities", 'Form Responses 1'!E:E, "Jordan")
You can also flag stale shares automatically by adding an IF statement in an empty column:
=IF(NOT(ISBLANK(H2)), "Paid", IF(TODAY() > A2 + 30, "Overdue", "Pending"))
Old unpaid charges get flagged without you checking every day.
Lock Down Permissions and Protect Formulas
Shared spreadsheets get broken by accident all the time. One careless tap on mobile can trash a working formula. Built-in protection tools prevent that headache.
Following the Sheets Bootcamp protection steps, you can lock specific ranges so only the document owner can change them. Right-click the "Summary" tab, choose "Protect sheet", and restrict it to "Only you". It locks out accidental edits immediately.
You can then use standard Google Sheets permissions guidelines to give housemates Editor access to intake rows while keeping headers and calculations strictly locked.
If someone only wants to see their current balance, share the sheet with their Google account as a Viewer. Viewers see their numbers anytime without risking any formulas.
Logging Reimbursements Without Double-Counting
Thing is, recording what someone bought is completely different from logging payback money. Entering a Venmo reimbursement in the main cost column doubles your group spending on paper.
Take Alex and Sam as an example. Alex spends $100 on groceries, fronting that full amount for the house. Sam sends Alex $50 over Zelle the next morning. Never enter that $50 transfer as another household grocery expense. Settle-ups belong in a separate settlements table, or you calculate them directly on the Summary tab.
Tax records require extra care when business expenses mix with personal house bills. The IRS recordkeeping guidelines note that valid records include cash register receipts, credit card charge slips, and bank statements showing payee, date, and dollar amount. If you deduct part of a shared internet or utility bill for home office work, attach the actual digital bill to the tracker row. Keep that paper trail intact.
Setting Ground Rules for the Group
A tracker only works if people actually enter their receipts.
To be honest, the best time to set ground rules is before that first grocery run happens, when everyone still feels cooperative. Agree on a strict submission cutoff. A 48-hour window works best for roommates or group vacations. Slips shoved into pockets for two weeks turn into unreadable thermal paper.
Pick a regular settle-up schedule, too. Settle on the last Sunday of the month for ongoing apartment rent and utilities. For group trips, close balances within 48 hours of getting home. Check the net difference on the Summary tab, send the transfers, and mark that billing cycle closed.
FAQ
How do I let someone submit receipts without giving them access to our totals?
Share the link to the Google Form only. Do not give them the spreadsheet link. Anyone with the form link can upload receipt photos and submit amounts without ever seeing other entries or opening the summary sheet.
Where do uploaded receipt photos go?
Google Forms saves uploaded files to a dedicated folder in the form owner's Google Drive. The connected sheet automatically displays a direct clickable link to each uploaded photo in that row.
Can this handle uneven splits?
Yes. Add an extra column for custom percentages or create dedicated columns per person. If an expense covers only two out of four roommates, record those specific names under Split Type before balancing numbers on your Summary tab.
Set up the form and sheet before your next supply run. Drop the submission link into your household group chat. Ten minutes of upfront effort saves you an entire evening of receipt digging later.