Does your apartment spreadsheet turn into an unreadable wall of numbers by the third week of the month? Adding a dedicated category column is the simplest way to fix that mess. It separates fixed costs like rent from fluctuating bills like power and grocery runs, so you can filter expenses instantly without digging through receipts.
A basic sheet solves almost every roommate dispute before it starts. You do not need a paid app. Free spreadsheets work fine. With a clean layout, automated dropdowns, and basic balance math, Google Sheets handles shared living costs easily.
Core Column Structure
Start by creating a clean table structure in a blank sheet. Name the tab something simple like Expenses. Row 1 holds your column headers. Each subsequent row represents one transaction paid by one person.
| Column | Header | What It Tracks | Sample Entry |
|---|---|---|---|
| A | Date | When the transaction cleared | 2026-02-03 |
| B | Description | Specific item or bill | Electric Bill |
| C | Category | Standardized spending bucket | Utilities |
| D | Total Amount | Full cost paid in USD | 142.50 |
| E | Paid By | Name of the person who paid | Alex |
| F | Split Method | How the expense gets divided | Equal Split |
| G | Alex Net | What Alex is owed or owes | +71.25 |
| H | Jordan Net | What Jordan is owed or owes | -71.25 |
| I | Status | Settlement record | Pending |
Every roommate gets their own net balance column. When someone pays for a shared bill, their column shows a positive credit. Other roommates show a negative debit.
Locking Categories with Dropdowns
Typing category names by hand leads to typos that break sheet filters. Turns out, people spell things differently. One person types Groceries, while another types grocery. Then your monthly totals fail. Setting up data validation dropdowns eliminates that headache.
- Highlight column C starting from cell C2 down to C100.
- Click Data in the top menu bar, then select Data validation.
- Click Add rule in the sidebar.
- Under Criteria, select Dropdown and add your labels: Rent, Utilities, Groceries, Household Supplies, Internet, and Repairs.
- Assign distinct colors to each tag so large bills stand out visually.
- Click Done to apply the rules.
Calculating Category Totals and Balances
Once transactions flow in, you want to see where the money went. You can pull running category totals onto a separate summary tab using a formula.
Google Sheets makes category aggregation painless with the query function. If your raw entries sit in columns C and D, you can generate a neat summary table with a single formula:
=QUERY(C2:D100, "SELECT C, SUM(D) WHERE C IS NOT NULL GROUP BY C LABEL SUM(D) 'Total Paid'")
For roommate balances, keep the math basic. Jordan paid $120 total for internet and owes half. Jordan gets a credit of $60. Alex paid nothing and owes $60, leaving Alex with a negative balance. Now and then someone tosses personal snacks into a shared Costco run, which makes the receipt slightly messy to split evenly, but you can just estimate the personal amount and adjust the row balance directly.
Permissions and Sheet Protection
Giving every roommate editor access is necessary, but unprotected sheets invite accidental deletions. Someone tries to filter on their phone and accidentally wipes a column formula.
Thing is, Google Sheets allows you to lock specific ranges while keeping entry rows open. You can restrict header rows and formula blocks through Google Sheets range protection so nobody can overwrite totals.
- Share the file directly to roommate Google accounts with Editor permissions instead of using a public edit link.
- Highlight row 1 and lock it via Data > Protect sheets and ranges.
- Lock your balance formulas so roommates only input data into columns A through F.
- Set mobile notifications so you get a ping when an entry changes.
A Simple Weekly Settlement Routine
Spreadsheets only work if people keep them updated. Establish a regular routine. Pick Sunday evening to log pending receipts and review balances. Settle debts using Venmo or Zelle.
When a roommate sends their repayment, do not erase previous rows. Add a new row labeled Settlement Repayment, log the amount, and mark the status column as settled. That keeps a clean record.
Frequently Asked Questions
What if roommates pay uneven rent shares?
Calculate their fixed percentage upfront. Record the full rent in column D, note the split percentage in column F, and assign the exact dollar shares into each person's balance column.
How do we handle reimbursements for personal errands?
Mark the split method as Direct Reimbursement. To be honest, that happens all the time when someone picks up a package or covers takeout. Put 100 percent of the cost on their debit balance and credit your balance in full.
Can roommates update this sheet from a smartphone?
Yes. The Google Sheets mobile app supports dropdown selection and cell editing. Bookmark the file on your home screen for quick access.
Set up your columns today, add your category validation rules, and enter the first bill of the month together.