Are you still hunting down roommates for electric bill money or wondering who paid for the dish soap? A simple shared spreadsheet solves household finances without monthly app subscriptions or pinging your phone with notifications.

Everyone checks the same running ledger. Anyone can pull it up on a phone or laptop. When people look at identical numbers, arguments stop before they start.

Here is how to set up an expense tracker in Google Sheets from scratch, divide shared costs, and keep your year-end math clean.

Designing the Transaction Ledger

A shared sheet needs a clean structure. Throwing expenses into a blank grid guarantees someone will spell a name wrong or accidentally overwrite a total.

Name your primary sheet tab Transactions. Put these headers across row 1:

Column Header Purpose Example Entry
A Date Date the expense was paid 02/01/2026
B Category High-level bucket for household budgeting Utilities
C Description Specific store, utility provider, or item Electric bill
D Total Cost Total dollar amount charged $140.00
E Paid By Name of the person who fronted the money Alex
F Split Method How the expense is divided Equal
G Alex Share Alex's calculated portion of the bill $70.00
H Jordan Share Jordan's calculated portion of the bill $70.00
I Notes Venmo handle, invoice link, or receipt memo Paid via auto-pay

Thing is, tracking every person's share in an individual column makes calculating summaries much easier later on. Jamming multiple names and split percentages into a single notes cell forces you into awkward manual math every single month. Give each roommate their own column. It takes ten seconds to set up, and it saves hours down the road.

Pin the top row next. Click View, choose Freeze, and pick 1 row. Now your headers remain locked in place as you scroll. After that, select columns D, G, and H, open Format, go to Number, and pick Currency to keep your decimal places tidy.

Adding Drop-Down Menus for Payer and Categories

Spelling mistakes ruin lookup formulas. If Alex enters their name as "Alex " with an extra space, or Jordan types "Jordn", the sheet reads them as completely different people.

Drop-downs eliminate typos entirely. The GeeksforGeeks guide on Google Sheets drop-down lists outlines how data validation locks down column inputs in just a few clicks.

  1. Highlight column E starting from cell E2 down through the rest of the column.
  2. Click Data and choose Data validation.
  3. Select Add rule in the side panel.
  4. Set the Criteria field to Dropdown.
  5. Add each roommate's name as an option, matching the exact spelling you will use on your summary sheet.
  6. Click Done.

Repeat those exact steps for Category in column B. Good default options are Rent, Utilities, Household Supplies, Groceries, and Maintenance. Setting clean categories up front keeps your data orderly and lets you run group spending summaries later without sorting through random, unorganized labels.

Splitting Rent, Utilities, and Grocery Costs Fairly

Real living situations are rarely fifty-fifty across the board. Every apartment has weird quirks.

Rent creates the most tension. One person gets an en-suite bathroom, someone else has a private balcony, and the third room feels like a broom closet. Most housemates handle this by pricing rooms according to square footage or agreeing on fixed ratios, like 55% for the master bedroom and 45% for the smaller room. Agree on those numbers in writing before move-in day.

Utilities are easier. Water, gas, trash, and home internet split evenly down the middle. Everyone uses them every day.

Groceries require strict ground rules. Shared essentials like olive oil, trash bags, dish soap, and salt belong on the shared tab. Personal snacks, specialty drinks, and private takeout do not. The test is straightforward: if an item benefits everyone under the roof, put it on the sheet and log the split. If Jordan grabs personal snacks during a supply run, Jordan should ask for a split receipt or subtract the personal total before entering the bill.

Calculating Balances with Simple Formulas

You do not need custom macros or AppSheet code. Two formulas on a separate tab handle everything.

Add a second tab named Balances. In column A, list roommate names starting at A2. Label cell B1 as Total Paid, C1 as Total Share, and D1 as Net Balance.

To tally what Alex paid upfront all year, drop this formula into cell B2:

=SUMIF(Transactions!$E$2:$E, A2, Transactions!$D$2:$D)

It scans your Paid By column, locates every entry matching cell A2, and sums the values in column D.

To see what Alex consumed across all shared purchases, enter this formula in cell C2:

=SUM(Transactions!$G$2:$G)

This adds up Alex's personal share column. For Jordan in row 3, change the target to column H.

Now calculate net standing in cell D2:

=B2 - C2

Total money paid upfront minus total share consumed equals net balance. Simple as that. A positive number means the house owes that roommate cash. A negative number means that roommate owes money back. Summing column D will always equal zero.

To pull an automatic breakdown of household costs by category, place this QUERY formula in an empty cell on your Balances tab:

=QUERY(Transactions!A1:D, "SELECT B, SUM(D) WHERE B IS NOT NULL GROUP BY B LABEL SUM(D) 'Total Spending'", 1)

Whenever a housemate adds an internet bill or a pack of sponges, your category spending updates instantly.

How to Record Repayments Without Doubling Debts

Turns out, logging repayments trips up more roommates than broken formulas ever do.

People often try to delete old rows or rewrite numbers when someone sends money via Venmo or Zelle. Never do that. Deleting rows wipes your payment history, which means no one can verify the original expense when questions come up three months later.

Always keep an audit trail. If Jordan sends Alex $70 to settle an electric bill, enter a brand-new row on the ledger. Put the payment date in column A, pick Settlement under Category, write "Jordan repaid Alex for electric" in the Description, log $70 under Total Cost, and choose Jordan under Paid By.

Now fill in the share columns. Because this payment was a direct transfer between two people rather than a shared group bill, put $70 in Alex's share column and $0 in Jordan's share column.

It feels backward on first glance. Jordan paid cash, yet Alex's share shows seventy dollars, which makes it look like Alex bought an item. But look at the math: it raises Alex's consumed balance by seventy while crediting Jordan for paying seventy out of pocket. That moves their running balance exactly seventy dollars closer to zero without changing a single past record.

Protecting Formulas and Managing Sharing Permissions

Shared sheets break when people type on small phones during grocery runs. A wayward thumb tap can wipe out your summary formulas.

Lock down your calculated ranges before sharing the spreadsheet. The Google Docs collaboration support guide explains how access levels work across desktop and mobile, and the GeeksforGeeks guide on Google Sheets permissions shows how to restrict specific ranges.

  1. Open the Balances tab where your summary formulas sit.
  2. Click Data in the top menu bar and select Protect sheets and ranges.
  3. Click Add a sheet or range, select Sheet, and pick Balances.
  4. Click Set permissions and choose Only you.
  5. Head over to the Transactions tab and repeat those steps for your calculated share columns so roommates can only edit the date, category, description, cost, and payer.

When sharing the file, invite roommates by adding their personal Google email addresses directly. Do not set link access to "Anyone with the link can edit." A public edit link exposes your personal names and bills to anyone who gets hold of the URL.

Ground Rules That Keep the Ledger Working

To be honest, shared spreadsheets almost never break because of technical bugs. They fall apart because someone leaves a stack of receipts on a desk for six weeks.

A few basic household agreements prevent that friction:

  • Log purchases within 48 hours. Add shared expenses to the sheet within two days while the receipt is right there on your phone.
  • Pick a fixed settlement day each month. The first Sunday after rent clears works well for most apartments. Everyone checks the Balances tab, settles any remaining negative balance, and enters a settlement row.
  • Drop physical receipts into a single house chat. Snap a photo of paper receipts and post them to your group thread with the purchase date. That way, anyone can verify numbers if something looks off.

A Google Sheet is private, free, and gives two to four roommates complete control over their shared ledger.

What to Do Next

Open a new Google Sheet right now and set up the column headers on your Transactions tab. Add your last two utility bills along with your most recent household supply run.

Verify that your Balances tab nets out to zero. Once the math checks out, invite your housemates before the start of the next billing cycle.